Tips for Excel, Word, PowerPoint and Other Applications
A Function is a Function is a Function ... Right?
Why It Matters To You
A function is a function is a function …. right? Not exactly. I think just about anyone who's ever tried to unravel someone else's worksheet before, would agree that there are definitely many ways NOT to put a worksheet together.. At best, you have to edit your function a bit because you forgot to make some of the references absolute. At worst, someone else takes over your spreadsheet and wastes hours on working and reworking analysis because they didn't realize you had hard coded values in your formula.
Discussion
I came across a scoresheet that had an odd formula. It weighted 5 individual scores to come up with a single weighted score. The scorecard did this for 5 possible sets of scores.
The examples below refer to functions.xls
The original formula looked like this: =C5*45%+C6*15%+C7*15%+C8*10%+C9*15%
The problem here is that the weights are buried inside the formula, meaning someone has to physically look at the cell reference to see how the scores are weighted. This also means that if you ever wanted to change the weighting, you'd have to manually go into each cell (and there were 5 columns of scores) and change all of the weights. Lastly, because all of the weights are manually entered, the chances of making a mistake are high.
Here are some options for other ways to edit the scorecard and rewrite the formula:
Option 1: Replace hardcoded numbers with variables
Now the formula looks like this: =D16*$C$16+D17*$C$17+D18*$C$18+D19*$C$19+D20*$C$20
First, I add a column for scoring weights, so that they are readily apparent. In this example, the formula is still clunky, but at least I can easily see what all the weighting factors are and can run a quick check to make sure they all add up to 100%. Also, if I want to change weight factors, I can do it right on the spreadsheet and the changes will flow through to all 5 scores ... the source of the numbers is very transparent.
Option 2: Replace the clunky string of products with a SUMPRODUCT
This formula looks like: =SUMPRODUCT($C$27:$C$31,D27:D31)
Anytime you are looking for a series of products, across two equally sized columns or rows, SUMPRODUCT is the easiest and least error-prone way to do it. Even so, you can't tell what C40:C44 and D40:D44 are just by looking at the formula. Forgot how to write a SUMPRODUCT, take a peek at the SUMPRODUCT article
Option 3: Replace the column references with named ranges in the SUMPRODUCT
Now the formula looks like: =SUMPRODUCT(Score_1, Weights)
This formula corrects the deficiency of Option 3. Now, just by looking at the formula, it's very easy to tell what it's calculating. If you don't know how to name ranges, just take a peek at the Named Ranges article.
Summary
A function is not a function is not a function. Take a look at the following 4 ways to get to the same result and ask yourself, which would be easier for you to work with?
=C14*45%+C15*15%+C16*15%+C17*10%+C18*15%
=D30*$C$30+D31*$C$31+D32*$C$32+D33*$C$33+D34*$C$34
=SUMPRODUCT($C$44:$C$48,D44:D48)
=SUMPRODUCT(Score_1, Weights)
Notes
| Last updated | 9/4/07 |
| Application Version | Excel 2003 |
| Author | Michael Kan |
| Pre-requisites | None |
| Related Tips |