 Highlighted

# Creating formula in Excel

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

7 Replies
Highlighted

# Re: Creating formula in Excel

Sorry error message is #VALUE! not #ERROR!

Highlighted

# Re: Creating formula in Excel

Most probably there is a text at least in one of the cells instead of number. But better to check sample file.

Highlighted

# Re: Creating formula in Excel

There may also be spaces, commas, text, special characters among the data which might be causing the issue.

Highlighted

# Re: Creating formula in Excel

Is that all not a text?

Highlighted

# Re: Creating formula in Excel

Hello @Sergei Baklan

Does Excel considers them all as text ?

Highlighted

# Re: Creating formula in Excel

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

Highlighted

# Re: Creating formula in Excel

You may check by ISTEXT()