Creating formula in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-1428625%22%20slang%3D%22en-US%22%3ECreating%20formula%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1428625%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20create%20a%20formula%20in%20Excel%20that%20gives%20a%20total%20of%20ratings%20that%20can%20then%20be%20divided%20to%20give%20an%20average%20rating.%20Please%20see%20screenshot%20of%20information%20%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22448%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EB%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EC%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ED%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ETotal%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ETotal%20Seedings%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EAv%20Score%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%23VALUE!%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E%23VALUE!%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20wish%20to%20create%20a%20formula%20that%20multiplies%20every%20A%20rating%20x%204%2C%20every%20B%20rating%20x%203%2C%20every%20C%20rating%20x%202%20and%20every%20D%20rating%20x%201.%20In%20this%20case%2C%20column%20A%20is%20K%2C%20B%20is%20L%2C%20C%20is%20M%2C%20D%20is%20N%2C%20Total%20of%20rating%20scores%20is%20O%2C%20Total%20Seedings%20is%20P%2C%20Av%20Score%20is%20Q%20and%20Line%20is%204.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20that%20I%20have%20tried%20to%20create%20is%20%3A%3C%2FP%3E%3CP%3E%3DSUM((K4*4)%2B(L4*3)%2B(M4*2)%2B(N4*1))%3C%2FP%3E%3CP%3EBut%20this%20returns%20a%20message%20%23ERROR!%3C%2FP%3E%3CP%3EIn%20this%20example%20using%20the%20formula%20(Which%20I%20used%20last%20year%20with%20no%20problems%20and%20have%20tried%20copying%20and%20pasting%20but%20getting%20the%20same%20error%20message)%2C%20Cell%20O4%20should%20be%209%2C%20P4%203%20and%20Q4%203.00%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20help%20to%20get%20the%20correct%20formula%20to%20be%20used%20%3F%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1428625%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1428627%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20formula%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1428627%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F684284%22%20target%3D%22_blank%22%3E%40nordenvillain%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%20error%20message%20is%20%23VALUE!%20not%20%23ERROR!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1428634%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20formula%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1428634%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F684284%22%20target%3D%22_blank%22%3E%40nordenvillain%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMost%20probably%20there%20is%20a%20text%20at%20least%20in%20one%20of%20the%20cells%20instead%20of%20number.%20But%20better%20to%20check%20sample%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1428866%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20formula%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1428866%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F684284%22%20target%3D%22_blank%22%3E%40nordenvillain%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20may%20also%20be%20spaces%2C%20commas%2C%20text%2C%20special%20characters%20among%20the%20data%20which%20might%20be%20causing%20the%20issue.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1428875%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20formula%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1428875%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F402789%22%20target%3D%22_blank%22%3E%40AshaKantaSharma%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIs%20that%20all%20not%20a%20text%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1428890%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20formula%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1428890%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20Excel%20considers%20them%20all%20as%20text%20%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1428927%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20formula%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1428927%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F402789%22%20target%3D%22_blank%22%3E%40AshaKantaSharma%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20check%20by%20ISTEXT()%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1428892%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20formula%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1428892%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F402789%22%20target%3D%22_blank%22%3E%40AshaKantaSharma%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPerhaps%20some%20of%20the%20blank%20cells%20are%20not%20empty%20but%20contain%20the%20null%20string%20(%22%22)%2C%20e.g.%20as%20a%20result%20of%20formulas%20in%20those%20cells%3F%20If%20so%2C%20use%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000080%22%3E%3CSTRONG%3E%3DSUMPRODUCT(K4%3AN4%2C%7B4%2C3%2C2%2C1%7D)%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThough%20note%20that%20if%20you%20are%20not%20using%20an%20English%20language-version%20of%20Excel%20then%20the%20separator%20within%20the%20array%20constant%20in%20the%20above%20may%20need%20amending.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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 :

 

ABCDTotalTotal SeedingsAv 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

@nordenvillain 

Sorry error message is #VALUE! not #ERROR!

Highlighted

@nordenvillain 

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

Highlighted

@nordenvillain 

 

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

Highlighted

@AshaKantaSharma 

Is that all not a text?

Highlighted

Hello @Sergei Baklan 

 

Does Excel considers them all as text ? 

Highlighted

@AshaKantaSharma 

 

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

@AshaKantaSharma 

You may check by ISTEXT()