Weighted values in Excel

Question

Question

I am developing a progress report for individuals. Each day, the individual is evaluated with about 20 different criteria with a rating of meets expectations, fails expectations or exceeds expectations. However, some of the criteria is more important than others and needs to be weighted heavier. And, some of the criteria may not be evaluated on that day and shouldn't be counted against the individual. Any ideas on how to do this and have it weigh the values fairly?

I have included my formulas that I used. Does this look correct? The weighted average result is concerning me. The more I evaluate, the weighted score goes above 100. Should that be the case?

Thanks

Scoring formula I used:

How my weighted formula is set up:

Answer 1

To get a weighted average, you need to divide by the sum of the weights, which isn't a big issue for your subscores since your weights seem to add up to very close to 1.

But, when you arrive at an overall score, you need to determine what your best and worst cases are and what the relative weight of a 'fail' is relative to an 'exceeds', and the value of a 'meets', then somehow use those values to interpolate between them.

For example, the relative values of an Exceeds could be 2, a Meets 1, and a Fails -3

Then, what is the best case? That could be 3 Exceeds for each category, so the best score would be +6 (including the weights).

Then, what is the worst case? That could be 3 Fails for each category, so the worst score would be -9 (including the weights).

So, then, on a 100 point scale (or %), the formula in AH20 would be

=(SUMPRODUCT(AI...) + SUMPRODUCT(AJ...)*2-SUMPRODUCT(AH...)*3)+9)(6+9) formatted for percent (the +9 is because we need to subtract a -9, the lowest score).

Answer 2

Thanks Bernie

I came to the same conclusion and kept working on it. So I added 3 different level of weights. The first corresponds to fails, 2nd to Meets, and 3rd to Exceeds.

So, check my calculations on the average. I subtracted the negative responses from the positive and divided to get a "score".

On the weighted Average, i calculated them individually by column. So now my question is do these "low values" make sense? It seems like they would have to be higher values.

And how would I come up with a final Weighted Score?

Answer 3

Can you share a copy of the file? Its better to use your data structure...

Answer 4

Can you share a copy of the file? Its better to use your data structure...

Can you show me how to do that? I dont see a share or attach function.

Answer 5

You can upload a file at onedrive.live.com - it should be part of your 365 license - and then share it to create and post a link here.