Poor, lonely array formulas. They get so little love but they can be very powerful tools in spreadsheets. Array formulas can run multiple calculations in a single formula and I use them most for 2 things: Weighted matrices and conditional aggregation calculations.
Weighted matrices are probably the more commonly used of the two. Sometimes, you have to create a scorecard where some of the factors may be weighted more heavily than others. This can often be the case in vendor evaluations.
They look something like this:
Most of the time, folks will add additional columns to apply the weight to the score to get the extended score. This adds width to your table and spreadsheet but it also adds bulk, only so you can get the ultimate score that you want.
It's much easier to use an array formula to create a total weighted score in a single column
Weighted matrices are probably the more commonly used of the two. Sometimes, you have to create a scorecard where some of the factors may be weighted more heavily than others. This can often be the case in vendor evaluations.
They look something like this:
Most of the time, folks will add additional columns to apply the weight to the score to get the extended score. This adds width to your table and spreadsheet but it also adds bulk, only so you can get the ultimate score that you want.
It's much easier to use an array formula to create a total weighted score in a single column
- First let's build the scorecard so create a table with your evaluation criteria (or Factors as they are shown here) as column headings
- Insert a row above your table and add the weights you want applied to each Factor in its corresponding column.
- Now add a column for the Total Weighted Score. This is where you'll put your array formula. What you're trying to do here is add up the scores multiplied by their weights, so you're looking for a Sum of Products. To make this easier to understand, we'll put in a dummy formula and then fill in the references later.
- In the first row of data in the Total Weighted Score column (E2, here), type in the formula:
- Now, don't hit <Enter>! Instead hit <Ctrl>+<Shift>+<Enter>. If you accidentally hit <Enter>, just go back to the cell, hit <F2> to edit, then <Ctrl>+<Shift>+<Enter>. The "CSE" combination is what lets Excel know to calculate this formula as an array.
In the formula bar, you should now see the formula with the curly braces around it. This means that it is now calculating as an array. Of course, you'll also see that the cell has an error because you didn't put in any data for it to calculate; just variables.
- Now, we’re going to fill in the rest of the formula. [x] is going to be your set of scores. [y] will be the corresponding weights for those scores.
- Add a temporary column to get the reference for your set of scores. In the first data row of this column (F2, here), type the "=" to start your formula. Select the columns that contain all of your scores for that row. In this example, I have 3 columns so my resulting formula is:
- Add another temporary column to get the reference for your set of weights. In the first data row of this other column (G2, here), type the "=" to start your formula. Select the cells that contain your weights but before you hit <Enter>,hit <F4> twice. This will change the row references to absolute so as you copy your formula, it will always reference the right now that contains your weights (Row 1, here).
- Now, let's replace the variables in the array formula with the right cell references. Go to the cell you created in Step 7 and copy the formula, except the “=” equal sign. Go to your array formula and replace the [x] by pasting. Do the same for the formula from Step 8, using it to replace the [y].
- Did you remember to <Ctrl>+<Shift>+<Enter>? If not, just go back to the cell, hit <F2> to edit, then <Ctrl>+<Shift>+<Enter>.
- Now just copy your formula down and you’ll see that the Total Weighted Scores are the same with just one column and an array formula, as they are with 4 columns!
