3.1 More on Formulas and Functions
Learning Objectives
- Review the use of the =MAX function.
- Examine the Quick Analysis Tool to create standard calculations, formatting, and charts very quickly.
- Create Percentage calculation.
– Use the Smart Lookup tool to acquire additional information about percentage calculations.
– Review the use of Absolute cell reference in a division formula.
Another use for =MAX
Before we move on to the more interesting calculations we will be discussing in this chapter, we need to determine how many points it is possible for each student to earn for each of the assignments. This information will go into Row 25. The =MAX function is our tool of choice.
Download Data File: CH3 Data
- Open the data file CH3 Data and save the file to your computer as CH3 Gradebook and Parks.
- Make B25 your active cell.
- Start typing =MAX (See Figure 3.2) Note the explanation you see on the offered list of functions. You can either keep typing ( or double click MAX from the list.
- Select the range of numbers above row 25. Your calculation will be: =MAX(B5:B24).
- Press Enter after selecting the range.
- Now, use the Fill Handle to copy the calculation from Column B through Column N.
Note that as you copy the calculation from one column to the next, the cell references change. The calculation in column B reads: =MAX(B5:B24). The one in column N reads: =MAX(N5:N24). These cell references are relative references.
By default, the calculations that Excel copies change their cell references relative to the row or column you copy them to. That makes sense. You wouldn’t want column N to display an answer that uses the values in column L.
Want to see all the calculations you have just created? Press Ctrl ~ (See Figure 3.3.) Ctrl ~ displays your calculations (formulas). Pressing Ctrl ~ a second time will display your calculations in the default view – as values.
Quick Analysis Tool
The Quick Analysis Tool allows you to create standard calculations, formatting, and charts very quickly. In this exercise we will use it to insert the Total Points for each student in Column O.
Mac Users: the Quick Analysis Tool is not available with Excel for Mac. We have alternate steps for Mac Users below. Skip down below Figure 3.5 to continue.)
Be sure to press Ctrl ~ to return your spreadsheet to the normal view (the formula results should display, not the formulas themselves).
- Select the range of cells B5:N25
- In the lower right corner of your selection, you will see the Quick Analysis tool (see Figure 3.4).
- When you click on it, you will see that there are a number of different options. This time we will be using the Totals option. In future exercises, we will use other options.
- Select Totals, and then the SUM option that highlights the right column (see Figure 3.5). Selecting that SUM option places =SUM() calculations in column O.
Alternate steps for Mac Users:
- Select the range B5:O25 then click the AutoSum button on the Ribbon (Home tab or Formulas