Excel Formulas Halfwit to Hero

The Tutorials
Chapter 1 Chapter 2 Chapter 3 Chapter 4 Chapter 5 
Chapter 6 Chapter 7 Chapter 8 Chapter 9 Chapter 10

Chapter 4

Using Functions

Life is easy, when you find someone else to do the work. In these exercises, you practice using functions that get Excel to do the calculating for you.

Exercise #1: The Princess of trig

Rapunzel's hairdresser needs to know: how long must Rapunzel's hair be to reach the bottom of her tower? It's exactly the kind of question that high school trigonometry can answer. In this exercise, you aren't expected to come up with the math, but you do need to find the Excel function that can put it into practice.

  1. Begin by opening the Princess of trig.xlsx spreadsheet.

    Click the link above to download the workbook file to your computer, then open it. Excel will start in protected view, because it doesn't know whether it should trust your file. To accept the file and switch out of protected view, click Enable Editing in the yellow bar at the top of the Excel window.

  2. You start with knowing two details: the distance to the tower and the angle that points to Rapunzel. Math nerds know there's a handy equation that can use this information to find the distance from the ground to Rapunzel (if you're curious, it's called a trigonometric identity). Look at the worksheet until you spot the picture that has the magic equation (in a big red box).

    The equation looks like this:

    This formula uses d to represent the distance to the tower and Θ to represent the angle between the ground and Rapunzel. The formula tells you that if you take the distance and multiply it by the tangent of the angle, you'll get the mystery value you want.

  3. To write this formula in Excel, you need a way to calculate the tangent of your angle. For that, you use an Excel function. Try looking in the ribbon for a function that can calculate tangents. Your best bet is to go to the Formulas►Function Library section and look in the Math & Trig list.

    The function that does the trick is TAN.

  4. The TAN function takes one parameter: the angle. But there's a catch. You need to supply the angle in radians, not degrees. Fortunately, the worksheet lists the angle in both units, with degrees in D4 and radians in D5. Using the radians measurement, how would you write the TAN function in a formula?

    Here's how you use TAN:

    =TAN(D5)

    Now that you understand how TAN works, you're one step closer to writing the formula you really want.

  5. You're finally ready to recreate the formula shown on the worksheet. Before continuing, identify which cells you're going to use.

    You need cell D3, (the distance to the tower), and cell D5 (the angle to Rapunzel in radians).

    Remember, you want to use cell references instead of typing the numbers directly into your formula. That way your formula will never go out of date, even if you change the numbers on your worksheet.

  6. In cell D7, write the formula. If you've written it correctly, you'll get a result of 6.381 meters.

    The formula is  =D3*TAN(D5)

  7. Here's a bonus challenge. Right now, the worksheet is cluttered up because it has the angle shown twice, in two different units (degrees in D4 and radians in D5). That's because people expect to see that angle in degrees but Excel wants it in radians. You can please both using a nested function. First, take a look at the formula that calculates the angle in radians to see how it works.

    The formula is in cell D5. It uses the RADIANS function to do the conversion  =RADIANS(D4)

  8. Now, rewrite the formula in cell D7 so that it uses the angle in degrees (D4) instead of in radians. The trick to doing this is to use a nested RADIANS function inside the TAN function.

    Here's the revised formula, with the changed part in bold:

    =D3*TAN( RADIANS(D4) )

  9. Finally, delete the contents of cell D5. If you've adjusted your formula correctly, you don't need it any more.

    You can find the reworked solution in the Bonus spreadsheet. (Click the Bonus tab at the bottom of the Excel window.)

Exercise #2: Take the temperature

Some Excel functions work with individual cells, but others use cell ranges, which are groups of cells. In this exercise, you'll try out seven of the most useful functions that use cell ranges.

  1. Begin by opening the Take the temperature.xlsx spreadsheet. It shows a list of high and low temperatures recorded on different days in July. You may notice that there are some gaps in the temperature data, which are represented by blank cells. For example, on July 7, no one measured the daily high. On July 13, no one measured the temperature low. Don't worry, you can still do your analysis without this information.

  2. To complete this exercise, you'll fill in cells F3 to F9 with different formulas. First is cell F3, which asks you to find the highest temperature. What function can do this?

    The MAX function.

  3. The MAX function needs one single parameter: the range of values you want it to look at. What's the right range for this example?

    You only need to look at the daily highs in column B. The exact range of cells is B3:B33, but you can get the same result by selecting the whole column with the range B:B (either answer works). Don't worry about the blank values in the B column, because the MAX function will just ignore them.

  4. Now that you know the function name and cell range, you should be able to write the complete formula. Put it in cell F3. What answer do you get?

    If you get a temperature of 95, you successfully found the highest temperature. Here's one way you might have written the formula to get the whole column:

    =MAX(B:B)

    Another way is to find the first and last cells with temperature information use them to make a smaller range:

    =MAX(B3:B33)

    Either way, the result is the same.

  5. You use a similar approach to find the lowest temperature from the column of daily lows. Figure out what function to use, and write the formula in cell F4.

    If you get a temperature of 63, you successfully found the lowest temperature. Here's one way you might have written the formula:

    =MIN(C:C)

  6. Now it should be easy to add formulas to find the average high and average low with the AVERAGE function. You can also write the formulas that find the median high and low using the MEDIAN function. Remember, the AVERAGE function is calculated by adding everything together and dividing the total by the number of values. The MEDIAN is found by grabbing the middle number (or if there are two middle numbers, averaging just those two numbers.) One reason people sometimes prefer the median to the average is because the average can get skewed if your data includes a really high or really low reading.

    To check your formulas, compare your results with the results in the Solution worksheet. You can also check how your values compare with the averages from the previous year, which are in column H.

  7. The last calculation you need is to count the number of measurements that were made in July. Remember, Excel has several counting functions. COUNT counts all the numbers in a range, COUNTBLANK counts the blank values, and COUNTA counts the cells that aren't blank. Which function do you need?

    You are interested in the number of temperature measurements. Each measurement is a number. You don't want to count cells that have text or blank cells. So COUNT is the function for you.

  8. Now write the formula in cell F9. Are you using the same range as you used for the other calculations?

    The easiest way to perform this calculation is to count all the cells in both the high and low columns. You can do that with a rectangular range like this:

    =COUNT(B:C)

    Another, slightly wordier approach is to count the cells in both columns separately by using the COUNT function twice. You can then add the two counts together:

    =COUNT(B:B) + COUNT(C:C)

PREFER PAPER? If you'd rather do your work offline, you can print this page. Use this button to expand all the hidden sections and then print the exercises for this chapter: