• 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.

    • Upon successful completion of this unit, you will be able to:

      • create basic formulas;
      • explain cell referencing;
      • use the order of mathematical operations in Excel formulas;
      • identify formula auditing tools;
      • use relative and absolute cell references in formulas; and
      • use the COUNT, AVERAGE, MAX, MIN, PMT, and FV functions.

    • 2.1: Formulas

      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.

      • Formulas, Relative References, Order of Operations, and Auditing Formulas Book

        Read this overview of how to create formulas in Excel. Pay close attention to the figures that show 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). If you copy the formula cell into the next column (column B), it will automatically change to =Sum(B1:B10). This feature is useful when you are performing the same type of calculation on multiple sets of data.

        The third section discusses the order of operations. When you perform complex calculations, your formula must follow the correct order of operations. For example, to calculate (2+8)/5, you first calculate the parentheses (2+8) and then 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 Excel follows.

        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.

      • Order of Operations Review Page

        Watch this video if you need to review the order of operations.

      • 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.

        • Statistical Functions in Microsoft Excel Book

          As you read, review Table 1, which outlines the most common statistical functions in Microsoft Excel. You can apply these functions to a large set of data entered in a spreadsheet. The rest of this section details how to use each type of function in a budget spreadsheet.

        • Statistical Functions in Google Sheets Page

          You can also use Google Sheets to perform statistical analysis. Watch this overview of performing simple statistical analysis on a small set of data.

    • 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!