144 E1.07: Section 4
Section 4. Formulas and functions
We have similar issues about order of operations when we enter formulas into a spreadsheet as when we enter them into a calculator. In these two types of problems below, it is necessary to insert parentheses. When we write these by hand, the order of operations is implied by the placement of the expressions, so that when we type them in, we must use parentheses to compensate for that.
| Evaluate each expression | In a spreadsheet, if the x-value is in cell A2 | |
| [latex]{{3}^{2x}}[/latex], where [latex]x=3[/latex] | =3^(2*A2) | |
| [latex]\frac{{{x}^{2}}-6x}{4x+2}[/latex], where [latex]x=3[/latex] |
=(A2^2-6*A2)/(4*A2+2) |
|
| [latex]\sqrt{4x+9}[/latex] | =SQRT(4*A2+9) or =(4*A2+9)^0.5 |
In addition to the usual mathematical operations, there are various named functions you may use in spreadsheet formulas.
| Named function | Example (usual notation) | Example (spreadsheet notation) |
| Square Root | [latex]\sqrt{4x-5}[/latex] | =SQRT(4*A2-5) |
| Average (of a set of five numbers, in cells A2 through A6) | [latex]\frac{{{x}_{1}}+{{x}_{2}}+{{x}_{3}}+{{x}_{4}}+{{x}_{5}}}{5}[/latex] | =AVERAGE(A2:A6) |
| Maximum (of a set of seven numbers, in B2 through B8) | [latex]\max({{x}_{1}},{{x}_{2}},{{x}_{3}},{{x}_{4}},{{x}_{5}},{{x}_{6}},{{x}_{7}})[/latex] | =MAX(B2:B8) |
| Pi (the value 3.14…) | [latex]\pi[/latex] | =PI() |
To find additional functions, in Excel, choose Insert > Function and explore the dialog box. Many of the functions will be things you don’t know about, but some will have familiar names. Explore them.
Example 13. Use a spreadsheet to graph [latex]y=\sqrt{12x+3}[/latex] for input values between 0 and 6.
Solution: Put in the values for x, then insert the formula. Then graph.
|
The formula in cell B2 is =SQRT(12*A2+3)
Then that formula was copied to cells B3 through B8. |
 |
Example 14. Put the formula for the value into the spreadsheet and see what number it gives.
Solution. In cell A1, put =PI(). Enter that. See that it gives the value 3.14 … with high precision, as does the key on a calculator.
Example 15. Use a spreadsheet to compute the average of these six test grades: 72, 85, 69, 79, 92, 71
Solution:
Cell C4:
|
First, type the scores into a blank spreadsheet. (In this case, copying and pasting doesn’t work because the scores above are not in separate cells of a table in the original document.) If you put them in the upper left corner, they will be in cells A1 through A6.
Next choose another cell to put the formula in. If you choose cell C4, then in C4, put this formula =AVERAGE(A1:A6)
As soon as you enter that formula, the average of the six numbers, which is 78, will appear in the cell.
To “play” with this, go back to the cells with the data and replace the first value of 72 with 102. Notice that the average in cell C4 changes to 83. |
Example 16. Use a spreadsheet to find the maximum of all the output data values in the dataset below.
Solution:
Cell F3:
|
First, copy and paste these data values into a blank spreadsheet. If you paste the whole set into the upper left corner, the output data will be in cells B2 through C6
Next choose another cell to put the formula in. If you choose cell F3 then, in cell F3, put this formula =MAX(B2:C6)
As soon as you enter this formula, that cell should show 68, which is the maximum of these numbers.
Now, to “play” with this, go back to the cells with the data and replace the first value in the “Men” column, 67, with the number 85. Notice that the maximum, over in cell F3, changes to the new maximum 85. |
Note: well-organized data will have all the data in a single column (or sometimes a single row), except when there are subcategories like those shown here.
