i like to have numbers for management to base a decision on. sometimes this is easy (just hand them a dollar figure), other times it is not. i came around to weighted averages for the simple reason that i wanted to prove, with numbers, that just because an option is cheaper, that doesn’t mean its better. let me explain.

in the process of evaluating 3 different vendors as a replacement product, say you pick out 5 criteria to base them on. for my purposes, i throw this in a spread sheet and then i grade each vendor on how i think they do for each criteria (which is subjective, of course).  for an example, see the screen shot below:

very quickly you can see that it is almost a dead heat between vendor x and y, and vendor z is out of the mix, right?

i would say this is misleading for the simple fact that criteria 1 might not (and probably won’t) be as important to you as criteria 2 or 3, etc.

in this example, i care a lot about criteria 2 & 5 (lets say those are the core functions of what i am evaluating) and only a moderate amount about criteria’s 1,3 and 4 (suppose those are things like look, feel, etc). so, i modify the spread sheet to add a weight column, assign each criteria a weight  (which is also subjective) and i get totally different numbers. take a look:

now, instead of being a slight advantage to vendor x, you can clearly see that vendor y is a much better fix for my environment and vendor x is the worst fit, not the best.

how do you do it? it all revolves around two functions: sumproduct() and sum().

all the weights are subjective, so you provide those. now, instead of just summing criteria 1-5 and dividing by the number of criteria, you are going to use sumproduct() to multiply each criteria with its corresponding weight and then divide the sumproduct() with the sum() of all the weights.

it sounds complicated when you read it, but here is what it looks like (this is based off the screenshot, looking at the formula in cell c7):

here is the code:

=sumproduct($b$2:$b$6;c2:c6)/sum($b$2:$b$6)

that’s it. just something handy to have for professional or personal evaluation on what option is best.

note: probably not necessary to state, but the “$” sign in the formula is an anchor of sorts, it keeps that field from changing when you do a autocomplete function (in this case the weights are always at b2-b6, so i lock them down with a “$”). just in case you didn’t know that 😉