Entering formulas to do calculations is one of the features of Excel. Before trying to enter a formula, it is important to understand the math operators. The asterisk (*) is the operator for multiplication. The right slash (/) is the operator for division; the plus sign (+) for addition; the minus sign (-) for subtraction. And operations that are to be calculated first are put in parentheses ( ). We will create a formula, and we will click in F14. What we want to do is to add this number for Assignment 1, this number for Assignment 2, and divide by 200 because that is the number of possible points. Begin a formula by clicking the equals (=) sign. Click on Margaret Johnson's Assignment 1 grade, and notice that D14 is placed in the formula. We want to add so we will click a (+) sign. Click on the Assignment 2 value, and we want to divide by 200. Our formula is not complete because we need to place parentheses around the D14 + E14 so that will be calculated first and that total will be divided by 200. Enter to accept the value, and the average is 71. We will look at formatting that a little later. To copy that formula down to Rows 15-18, click on the fill handle and drag down. And then let's look at each of the formulas. Excel, by default, uses "relative cell reference." What that means is when a formula is copied down a row, the references are changed to refer to that row. So, on Row 14, notice it is D14 + E14. When we look at the formula that was copied to Row 15, it changed to D15 + E15. Row 16 changed to D16 + E16. The next row D17 + E17, and for Row 18, likewise. That's referred to as "relative cell reference." We'll look at another type of reference in another video.
Now we want to create a formula to create the final average. Click in the cell where the formula is to be entered. Touch the equals (=) key to start the formula. We want to take the Assignment Average and multiply it times 30 percent and the Test Average and multiply it times 70 percent. I'll need to locate the cell for the Assignment Average. I'm on Row 9 so I will select this cell. To that, I want to multiply it times K5, which is the 30 percent. Click in the cell. I need to put parentheses around that part of the formula so that will be calculated before anything outside the parentheses is calculated. I need to add to that, and I'll start another parentheses, the Test Average for this student times 70 percent, and I will close the formula. We are not quite finished with this formula yet because we need what is called an "absolute cell reference." An absolute cell reference says, "always go to this particular cell and don't change the reference as it did in a relative reference." The way to do that is to put a dollar sign ($) in front of the cell reference. We always want it to be K and we always want it to be 5. For the test average, we always want it to go to K, so we will put a $ in front of it and we always want it to go to 6. Now we have our new formula: We will copy this formula down for each of the students, and now we have the averages. By using absolute cell references, each time we have done a calculation, we have referred to Cell K5. Each time we wanted to calculate the test average, we have referred to K6.
Functions are built-in formulas that perform special calculations automatically. Excel has a number of functions including some math and trig, but we will look at the simple function key. This is the AutoSum key, and there is a dropdown list of the 1, 2, 3, 4, 5 types of functions that can be performed: SUM, Average (this will total and average a column), Count the number of entries in a column, MAX (locate the largest number) and MIN (locate the smallest number.) Let's look at the average. What we will do here is look at the Final Average column, but we would like to know what the average of all the students' grades is. I will click in the cell where I want to place the result, click on the AutoSum button and choose Average. Excel guesses the range you want to sum. If the selection is incorrect, you can drag through the range. I think I will drag through here--it selected an additional cell and it may or may not make a difference. Once you select a cell, Enter. We see that the average grade for all student put together is 77 percent. For Count, the function will count the number of entries. We have clicked in the cell. We will choose Count. Let's drag through the list we want counted and touch Enter. There are 10 entries. The Maximum function will locate the largest number. It is easy for us to look at this short list of numbers and locate the largest, but if you had a very long column, it would be nice for it to be done automatically. Click on AutoSum, Max. We are going to need to select the column and touch Enter. So 93 percent is the highest number. Let's look at the lowest final average. Select the cell, choose Minimum, select the range and Enter. The lowest final average was 61. These are very basic easy functions. If you want to learn more about functions, look at the Help menu. There is much more to learn about Excel functions.