Unit 2: Mathematical Computations
Spreadsheet programs shine when used to handle formulas and functions. Formulas are mathematical equations you write yourself, while functions refer to the pre-built formulas that Microsoft Excel or Google Sheets include for you to use. Examples of functions are calculations of the average or standard deviation of a set of data. In this unit, we review basic functions and writing formulas. Then, we discuss special types of functions and formulas used in statistics and business.
Completing this unit should take you approximately 2 hours.
You need to use the proper syntax when writing formulas in Excel or other spreadsheet software programs. It is important to define the calculation you are performing and the data you will input in the calculation. A range is the 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, to add two cells, we use the formula (=A3+B3) 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.
2.2: Statistical Functions
Spreadsheet programs are powerful for performing statistical calculations. In statistics, we often need to perform calculations using large sets of data. This can be extremely difficult and time-consuming to do by hand or using just a calculator. By thoughtfully formatting a spreadsheet, we can quickly perform statistical calculations.
2.3: Functions for Personal Finance
We often use spreadsheet programs for personal finance applications like creating budgets or monitoring loan payments.
The PMT, or payment function, is a useful function for personal loans. You can use the variables of interest rate and time duration of a loan to work out payment options so you can make better decisions when deciding whether to take out a loan.
Most loans are based on an annual interest rate. Since we usually make payments monthly, we need to 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 6 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 10 years times 12 months. Or, you could just use 120 months as the length (10*12). Either way, the length, or NPER in the function box, needs to reflect monthly payments. No one wants to make a car payment once a year!