Before I launch into the many advantages of using Excel, a bit of nostalgia: Remember the old desktop and handheld calculators? Maybe you do, or maybe you’ve only seen them in a museum or in your grandparent’s home. If you really want one of these for your very own, maybe you can get one free (with a lovely company logo on it) like I did from the bank where my sister-in-law used to work.
If you’ve used Windows for a while, odds are you’ve seen its Calculator program. It’s like a handheld calculator, but without the need to cradle it in your palm or find real estate for it on your desk. Find the Windows Calculator by clicking the Start button, looking under All Programs, and then under Accessories.
One last thing, I promise, and I’ll finally get to extolling the virtues of Excel. If you’re a student or are otherwise interested in learning more about math, Microsoft Mathematics is a downloadable tool you can use to solve equations step-by-step while learning the fundamentals of algebra, trigonometry, physics, chemistry, and calculus. Get Microsoft Mathematics 4.0 (free!) here, at the Microsoft Download Center.
By now, you’ve gotten used to using digital calculators, such as the one in Windows or in your phone, and you know their limitations. Say you want to calculate mortgage interest or your grade point average. This is where it’s time to make the move to Excel and see how it’s a vast improvement over a simple calculator.
Something all calculators have in common is that when you mistype a number or press the wrong key, there’s not much in the way of “Undo” functionality, with the exception of the CE (Clear Entry) key. Sorry to say, that CE key won’t get you very far.
Excel to the rescue
If you’ve entered simple formulas in Excel, such as adding, subtracting, multiplying, or dividing numbers, you know that the formula it creates looks a bit like what you’re doing on a calculator…but Excel saves your work, and is much more forgiving. In an Excel formula, instead of having to start over if you make a minor mistake, you can just fix a thing or two without losing what you’ve typed.
Yet another nice thing about Excel is that after you’ve completed your formula, you can see it in the formula bar and its results in the cell, all at the same time. Here’s a formula and its result (29).
The formula shown below is entered in Excel pretty much the same way you’d enter it into a typical calculator – using lots of correctly(!) placed parentheses and math operators. Those math operators are very similar to what you’ll find on calculator…the plus sign (+), minus sign (-), slash symbol for division, (/), and the asterisk (*). On old calculators and on paper, multiplication is ”x,” but don’t try that in Excel – I promise you, that won’t work! Get used to using that * key (also known as “star”).
=((4*3.3)+(3*3.6)+(4*3.1)+(2*3.7)+(3*3.2))/(4+3+4+2+3)
OK, I could enter this in a calculator. But let me be honest – I’m not the world’s most careful button puncher, so I could easily make one or more entry errors using a calculator and end up with the wrong answer.
Messy! Or, I could enter these numbers in an Excel grid and use a smaller (but powerful!) formula to get the same result. This formula will find a student’s grade point average, based on 5 courses that have a few different credits. This type of average is known as a “weighted average.”
The formula (in cell C7) looks like the following:
=((B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6))/(SUM(B2:B6))
That’s a little less messy, since it uses the SUM function to total the credits, but it’s still a pretty long formula. One nice thing about this formula is that it uses cell references (cells B2 through B6 for the credits and cells C2 through C6 for the grades). If I need to change a value or two for either the credits or the grades, I don’t need to change the formula.
The awesome SUMPRODUCT function
Here’s a much more efficient formula I can use to calculate a weighted GPA:
=SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6)
Aha! Try doing that in a calculator! Instead of stringing together all those multiplication operations - (B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6) - I’m using Excel’s SUMPRODUCT function.
What does that SUMPRODUCT function do, you may wonder? Glad you asked! SUMPRODUCT multiplies the corresponding elements of arrays (an array is a group) of numbers and then sums those products of the array elements. Here’s an example of two arrays (bear with me, this is getting a little bit “mathy!”):
[2 3 4] [3 2 5]
The first array [2 3 4] contains three numbers, each assigned a position. The first position of this array contains 2, the second position contains 3, and the third position contains 4. The second array [3 2 5] also contains three numbers – 3, 2, and 5 – in first, second, and third positions. If I were to multiply these two arrays using the SUMPRODUCT function, the formula would look like the following, returning 32 as the answer (the two arrays are enclosed in squiggly braces, and their elements are separated by semicolons):
=SUMPRODUCT({2;3;4},{3;2;5})
Intrigued? Learn about and interact with the SUMPRODUCT function in a browser worksheet.
So…remember algebra? Think back to multiplying two expressions by each other, such as (x+2 )(y-3). You can consider this expression the same as multiplying two arrays ( [ x 2 ] and [y -3] ) times each other. In this algebraic expression, you multiply x by y, x by 3, 2 by y, and 2 by -3. Then, you add them all together to get xy + 3x + 2y -6. Look familiar?
I’ll expand the size of my sample arrays ( [2 3 4] and [3 2 5] ) so that they’ll have the five credit values and the corresponding grade values for each course. These two arrays each contain 5 elements instead of just 3. And instead of putting 5 numbers in each array ([4 3 4 2 3] and [3.3 3.6 3.1 3.7 3.2]), I’ll instead use two ranges of 5 numbers that contain the values of 5 Excel cells. For the class credit array, I’ll assign the range B2:B6 (that’s 5 cells). Its corresponding grade array is C2:C6.
The first part of the formula, SUMPRODUCT(B2:B6,C2:C6), results in a total of 53.4. That’s the total of the credit value times the grade value for each course.
Class |
Credits |
Grade |
Credits * Grade |
Econ 102 |
4 |
3.3 |
13.2 |
Math 171 |
3 |
3.6 |
10.8 |
History 143 |
4 |
3.1 |
12.4 |
Computer Sci 114 |
2 |
3.7 |
7.4 |
Business 155 |
3 |
3.2 |
9.6 |
Total |
16 |
3.34 (GPA) |
53.4 |
The second part, SUM(B2:B6), uses the SUM function to total the credit values, resulting in 16. Dividing the result of the SUMPRODUCT operation by the result of the SUM operation, I get 53.4/16 = 3.34. The image here shows you where the two arrays are used in the formula:
I think the SUMPRODUCT function is pretty cool, and I hope you’ll agree. And for doing math operations that use more than just a few numbers or calculations, using Excel is a lot less error prone than using a calculator. Especially for me!
Resources
Watch Doug Thomas and a math whiz take Microsoft Mathematics 4.0 through its paces in this short video: What Math 4.0 can do for students
To learn more about using Excel as your calculator, follow these links:
Use Excel as your calculator (Office.com article)
How to use Excel as a calculator (eHow.com article)
How to Use Excel as a Financial Calculator (eHow.com article)
How to Create an Excel Financial Calculator (wikiHow article)
Use Excel as a Calculator (University of Sydney article)
Learn about and interact with the SUMPRODUCT function in a browser worksheet here: SUMPRODUCT function
And finally, learn more about using math operators here: Calculation operators and precedence
– Gary Willoughby