Spreadsheet programs shine when used to calculate formulas and functions. Formulas are the mathematical equations you compose yourself. Functions are the pre-built formulas that Microsoft Excel or Google Sheets include for you to use. Examples of functions are calculations of a data set's average or standard deviation.
In this unit, we review basic functions and how to write formulas. Then, we discuss special functions and formulas used in statistics and business.
Completing this unit should take you approximately 4 hours.
You must use proper syntax (the arrangement of symbols, words, and phrases) when writing formulas in Excel and other spreadsheet programs. Defining the calculation you are performing and the data you will input is essential. A range is a set of numbers used in a formula or function. The range is usually, but not always, a group of contiguous numbers that follow each other in a spreadsheet.
Remember to use the cell reference rather than the actual number in the cell. For example, use the formula (=A3+B3) to add two cells rather than the numbers in the cell (=12+15). This allows Excel to automatically update the totals according to the associated formulas and functions when you change a number in a cell.
Read this overview of creating formulas in Excel. Pay close attention to the figures showing how to enter formulas in a budget spreadsheet.
The second section explains relative referencing. Excel will adjust the cell reference used in a formula when you copy and paste the formula into a new location. For example, if you are summing numbers in column A, you may have a cell with the formula =SUM(A1:A10). Copying the formula cell into the next column (column B) will automatically change to =Sum(B1:B10). This feature is useful when performing the same type of calculation on multiple data sets.
The third section discusses the order of operations. When performing complex calculations, your formula must follow the correct sequence. For example, to calculate (2+8)/5, calculate the parentheses (2+8) first and divide its result by 5. Spreadsheet programs have formatting requirements for the order of operations. Pay special attention to Table 2.3, which shows the order of operations in Excel.
The final section discusses auditing formulas, which allows you to check or review the formulas you use in a spreadsheet using a simple command. You can also see which cells are referenced in each spreadsheet formula.
Watch this video to review the order of operations.
Statisticians use spreadsheet programs to perform statistical calculations for large data sets. Relying on a hand-held calculator to perform these calculations makes maintaining consistency and avoiding errors difficult and time-consuming.
As you read this text, pay attention to Table 1, which outlines the most common statistical functions in Microsoft Excel. You can apply these functions to large spreadsheet data sets. The rest of the section details how to use each type of function in a budget spreadsheet.
We can also use Google Sheets to perform statistical analysis. Watch this overview of performing a simple statistical analysis of a small data set.
We often use spreadsheet programs for personal finance applications, such as creating budgets or monitoring loan payments.
The PMT (payment) function is useful for helping determine the costs of borrowing money for personal loans. Use the interest rate and loan duration variables to calculate your payment options and make better decisions about taking out loans.
Remember that most loans are based on an annual interest rate. Since we usually make payments monthly, we must divide the annual or yearly rate by 12. If the interest rate is six percent, the rate should be 6%/12 in Excel's payment function because you only pay one-twelfth of the six percent each month (or 0.5 percent per month).
Let's say you are deciding the length of a loan. To calculate the monthly payment for a 10-year loan, you would multiply ten years by 12 months. Or, you could just use 120 months (10*12). Either way, the length, or NPER in the function box, must reflect monthly payments. No one wants to make a car payment once a year!
Read this text, which details how loans work and the different parts of the PMT function. Pay attention to Table 2.6, which shows the PMT function's arguments (or inputs).
Watch this demonstration of how to use the PMT function in your Microsoft Excel spreadsheet.