May 30 2020 07:11 AM
I am trying to create a formula in Excel that gives a total of ratings that can then be divided to give an average rating. Please see screenshot of information :
A | B | C | D | Total | Total Seedings | Av Score |
3 | #VALUE! | 3 | #VALUE! |
I wish to create a formula that multiplies every A rating x 4, every B rating x 3, every C rating x 2 and every D rating x 1. In this case, column A is K, B is L, C is M, D is N, Total of rating scores is O, Total Seedings is P, Av Score is Q and Line is 4.
The formula that I have tried to create is :
=SUM((K4*4)+(L4*3)+(M4*2)+(N4*1))
But this returns a message #ERROR!
In this example using the formula (Which I used last year with no problems and have tried copying and pasting but getting the same error message), Cell O4 should be 9, P4 3 and Q4 3.00
Can you help to get the correct formula to be used ?
Thanks
May 30 2020 07:12 AM
Sorry error message is #VALUE! not #ERROR!
May 30 2020 07:16 AM
Most probably there is a text at least in one of the cells instead of number. But better to check sample file.
May 30 2020 11:00 AM
There may also be spaces, commas, text, special characters among the data which might be causing the issue.
May 30 2020 11:27 AM
May 30 2020 11:36 AM - edited May 30 2020 01:14 PM
Perhaps some of the blank cells are not empty but contain the null string (""), e.g. as a result of formulas in those cells? If so, use:
=SUMPRODUCT(K4:N4,{4,3,2,1})
Though note that if you are not using an English language-version of Excel then the separator within the array constant in the above may need amending.
Regards