-1

I'm trying to calculate an average score based on a list of parameter scores (between 0 and 5). The trick is that I want to be able to weight each parameter. Eg: 

        Parameter A     Parameter B     Parameter C
Weight  100%            70%             0%
Score   4               5               0

In the above example, the average score should be 3,75 as parameter c is left out.

I've tried with this formula: =IF.ERROR(SUM((A3*A5);(B3*B5);(C3*C5))/COUNTA(A3:C3);""). The formula seems to work if none of the parameters weight is equal to 0. How can I adjust the formula, so it excludes a score if weight is equal to zero?

I think it should be rather easy, I just can't get it to work.

letsintegreat
  • 3,328
  • 4
  • 18
  • 39
Emilia Wasik
  • 3
  • 2
  • It's unclear to me what's your definition of average score. Why do you think that *"the average score should be 3,75"*? Shouldn't it be (4*1 + 5*.7 + 0*0)/(1+0.7+0) = 4.42? – Bob__ Apr 15 '18 at 08:18
  • (4*1 + 5*0.7) / 2 = 3,75 :-) – Emilia Wasik Apr 15 '18 at 08:21
  • Then I'll undelete my answer, but it doesn't seem the right way to calculate a weighted mean to me ;). – Bob__ Apr 15 '18 at 08:27

4 Answers4

1

Check this :

=SUMPRODUCT( A2:A4, B2:B4 ) / SUM( B2:B4 )

Source : https://exceljet.net/formula/weighted-average

ANKUR KHUNT
  • 145
  • 1
  • 9
  • Yeah, except this does not allow some of my scores/weights to be zero. I need a formula that takes that into account. – Emilia Wasik Apr 15 '18 at 08:12
  • It will be helpful, Please go through this question: https://stackoverflow.com/questions/39222679/how-to-calculate-weighted-average-for-0-values?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa – ANKUR KHUNT Apr 15 '18 at 08:22
  • Or you can use IFERROR to suppress the #DIV/0! error. For eg. =IFERROR(A1/A3,0) – ANKUR KHUNT Apr 15 '18 at 08:42
0

It would be like this:

(1*4 + 0.7*5) / 2 = 3.75

In other world the formula is:

((WeightA/100 * scoreA) + (WeightB/100 * scoreB) + (WeightC/100 * scoreC)) / 3
  • Yes, but I need a formula that works no matter what parameter weight is changed. That will differ from time to time. – Emilia Wasik Apr 15 '18 at 07:47
  • This formula doesn't allow scores / weights to be zero either. – Emilia Wasik Apr 15 '18 at 08:15
  • Yes it does. if weight is zero then it's 0/100 * score and if score is zero then it's weight/100 * 0, so the result is 0 nevertheless –  Apr 15 '18 at 08:25
  • You're right. Had to do with the counta formula I used for division. When using count if(A3:C3;"<>0") for division, It worked. Thanks :) – Emilia Wasik Apr 15 '18 at 08:57
0

With COUNTA you are counting the non empty cells, while you should count the non zero cells. So, assuming that the weights are in A3:C3 and the scores in A5:C5:

=IFERROR(SUMPRODUCT(A3:C3;A5*C5)/COUNTIF(A3:C3;">0");"Error: all the weigths are 0")
Bob__
  • 12,361
  • 3
  • 28
  • 42
0
=SUMPRODUCT(A1:A3;B1:B3) / COUNTIF(B1:B3;"<>0") / 100

Something like this would work

Krzysztof Rosiński
  • 780
  • 1
  • 8
  • 9
  • If you do the countif like this, then if any of my weights are equal to zero, it won't work. And the is my point. Some of the weights will be 0 and the corresponding score should just be ignored then. – Emilia Wasik Apr 15 '18 at 08:17
  • Can you add more test cases then? It seems to work okay for the test case you provided. `(100*4+70*5+0*0) / 2 = 3.75` It doesn't ignore 0 weights explicitly but they're automatically ignored (multiply by 0). – Krzysztof Rosiński Apr 15 '18 at 08:45