Techniques for Calculating Scores and Auditing Formulas

Tips and Fundamentals. In this section, we will review some of the topics we covered in Level One class. In this first exercise, we'll take a look at some common calculations you can use to calculate the total, average, and high score for a list of students who've taken five tests.

So, I'm going to start with the total. Let's start with the first student, Alex. We want to be able to calculate his total.

A simple way that you could calculate his total is simply to go to the Home tab, head over to the Editing group, and click Autosum. Then you'll press ENTER, and we have his total. Now I'd like to calculate the average.

I could also use the Autosum function that's also available on the Formulas tab. I'll click the drop-down because I want to go beyond Sum and actually choose Average. I'll click Average.

Now, I need to be careful here not to go on automatic pilot and simply press ENTER. I want to notice that the cells being selected are the tests but also the total. So, I don't want to include the total.

So, I'll just reselect and only select the tests. Now I could press ENTER, but I'll press TAB because it will take me to the next cell. Now, I want to get the high score.

Here's another method that's available to you. You can simply select the cells you want to calculate. Then, you'll go to the Autosum function that you're looking for.

In this case, I want to go to MAX. That will tell me what the maximum score is, and it will add that value in the next empty cell. The next empty cell to the right is High Score.

So, with one click of MAX, I get my high score right there. Now that I have all three test scores for Alex, I want to be able to calculate the same results for all the other students.

So, I'm going to select all three and go to the Autofill handle, which is the black plus sign that shows up on the bottom right-hand corner when you hover your mouse over that square. I'll click and drag down, and there are my calculations. Another method that's available to you is simply to double-click on the black plus sign, and that will copy those values down.

Yet another method that you can use with the keyboard shortcuts is to simply hold on to the Shift key, press the Down Arrow key, and you'll select multiple blank cells as well as the original formulas. The keyboard shortcut to copy the formula down is CTRL + D, as in the word Down. And then, let's take a look at another way you can copy formulas, but this time from a horizontal orientation.

I'll need to get the total for Test 1. There is a keyboard shortcut for Autosum that's very easy to figure out. All you have to do is hover your mouse over the Autosum function, and Excel will pop up a little tooltip that will let you know that the keyboard shortcut for Autosum is ALT + equal.

So, in the current cell that I'm in, if I hold down ALT and press equal, it would be the same as if I had clicked on Autosum. Now, I'll press ENTER, and there's my total, but I want to copy that value across horizontally and come up with the total for Tests 2, 3, 4, and 5. Now, if the keyboard shortcut to copy down is CTRL + D, well, then you can probably guess the keyboard shortcut to copy a formula to the right is CTRL + R. Now, don't worry about all the methods that I just mentioned for copying formulas.

If you go over to column N, you'll notice that the letter M is missing between L and N. And if you click the plus sign, we have some tips here that were hidden until we reviewed this particular topic. All right, let's now take a look at formula auditing. Formula auditing allows you to audit your formulas and fix errors.

So, here I have a formula that gives me the percentage of the total, but I'm only seeing the percentage of the total for the first value. For the rest of the values, I get a division by zero error. So, I'm going to use the F2 key to take a look underneath the formula to see what's going on.

So, press F2 on your keyboard, and if you're on a laptop, you may need to use the function key. So, I'll press F2, and it displays the values that make up the result, and what I notice is I am selecting 500 and I'm selecting the value underneath the total. I'll press ESC to release that selection and I'll go up.

Let me take a look at the original values. That's the big difference. I am selecting the total in the first formula, but when I move down a row to select the value for Tablet, I'm also moving down a row for the total.

So, I need to make sure that the total does not move. I need to lock its position. The keyboard shortcut for that is F4.

If I press F4 and press ENTER, the result is the same, but when I use Autofill, I get different results for the rest of the values, and they're actually the correct results. Now, there's a little green triangle next to the total. This is Excel's way of letting you know there is an inconsistency in the pattern that gives me the result.

So, let me click and take a look at the yellow diamond error. It says the formula omits adjacent cells. I could press F2, but let's use another method called Trace Precedence.

On the Formulas tab, if you head over to the Formula Auditing group and click Trace Precedence, you'll see visually the values that make up the result. Here, what I notice is that I'm not including the price of the keyboard. So, I can go into the formula or go over to the edge and move this down one more row. I'll go into the formula by pressing F2 and simply drag the edge, and you'll need to see a double arrow. You have to be in just the right position, and I can drag it down.

Now, when I press ENTER, that will update my total. It also updates my percentages. Let's move over to 700.

I may be interested in seeing which formulas are dependent on 700. So, on the Formula tab, in the same area where you found Trace Precedence, if you look directly underneath it, you'll find Trace Dependence. What this does is it shows you the formulas that are dependent on 700 for their result.

So, this is another way to audit the formulas in your spreadsheet. If I want to remove the arrows, right underneath Trace Dependence is Remove Arrows. In the next exercise, you want to figure out why we're not getting the first letter of each name in this list.

So, I could press F2. I could also use Trace Precedence or Trace Dependence. What I'm going to do in this case is use a function or an option called Show Formulas.

If I click on Show Formulas, it shows all the formulas in the spreadsheet. Here, I can look underneath and see what's happening, and what I notice is that F34 is locked, so the values are not changing, and I'm not able to select Mary, Keith, Carol, and Steve. We have the opposite problem here.

We need to remove the lock from that cell. So, I'll backspace on the dollar sign, remove them for the first value, and press ENTER. Now, I can use Autofill, and the function will be able to automatically increment the cell references, so I'm picking up each of the names.

Now, I'm not seeing the result here because I'm still in Show Formulas. So, I want to remove Show Formulas. I'll click on it, and there I can see the result.

And then for this last exercise, if I press F2, the issue here is that the columns are not the same size. So, there are certain formulas that require the arguments in your function to be exactly the same size. So, I'll go over to K34 through 36 and extend it to include the value for Avocados.

And when I press ENTER, I get the final result, which is the total price of 10 apples at 50 cents, 15 oranges at a dollar, 20 bananas at 25 cents, and 10 avocados at $1.15. So, in this section, we just reviewed ways to work with the Autosum functions and formula auditing to troubleshoot and fix errors in your formulas by using key auditing tools.