Weighted Average Ignoring Blanks

[cwhaley1]

Hello. I have a spreadsheet which collates scores received and calculates a weighted average based on a number entered into a reference cell. The point of this sheet is to record scores received for projects after they have completed.

What I cannot understand is how to get the formula to ignore any blank scores. Not all questions asked are relevant to a project. At the moment if a blank score is input, the whole project score is reduced which is unfair.

This is my current formula (I can't post attachments):

=((O5/4*O$2)+(P5/4*P$2)+(Q5/4*Q$2)+(R5/4*R$2)+(S5/4*S$2)+(T5/4*T$2)+(U5/4*U$2)+(V5/4*V$2)+(W5/4*W$2))/(O$2+P$2+Q$2+R$2+S$2+T$2+U$2+V$2+W$2)

The cells in row 5 are the question scores (from 1 to 5) and the cells in row 2 are the scoring weightings.

I have tried IFERROR, but this doesn't work as intended (I may be using it wrong). I don't know which other formula could do the calculation and ignore the blanks. Maybe the source data for the formula needs to be input differently.

Just looking for some help if anybody can offer it!

 

[James006]

Hello,

You can use the sumproduct function .... ( adjust ranges to your situation )

[COLOR=#545454][FONT=arial]=SUMPRODUCT(A2:A5,$B$2:$B$5)/SUMPRODUCT(--(A2:A5<>""),$B$2:$B$5)[/FONT][/COLOR]

Hope this will help

 

[cwhaley1]

Thank you @James006 I will give this a go later today.

 

[James006]

You are welcome ...

 

[cwhaley1]

I adapted your formula to my example at it has worked very well so thank you for this. My only remaining query is whether or not this can be tweaked further to ignore "N/A" as typed as text. This is what I have on the spreadsheet:

=SUMPRODUCT((O5:W5/4)*O$2:W$2)/SUMPRODUCT(--(O5:W5<>"")*(O$2:W$2))

O5:W5 divided by 4 are the question answers and O2:W2 are the weightings. The reason I divide by 4 is because the scores are numbered 1 to 5. 4 is the target mark which we class as 100% and 5 would be going above and beyond and therefore a better mark.

Where I have (--(O5:W5<>""), why does typing N/A between the quotation marks result in a VALUE error? Not a biggie but just something which would be good.

 

[James006]

Glad to hear things are improving ...:wink:

For you latest question ... as a blind test :

=SUMPRODUCT((O5:W5/4)*O$2:W$2)/SUMPRODUCT(--(O5:W5<>"")*ISNUMBER(O5:W5)*(O$2:W$2))

Hope this will help

 

[cwhaley1]

Glad to hear things are improving ...:wink:

For you latest question ... as a blind test :

=SUMPRODUCT((O5:W5/4)*O$2:W$2)/SUMPRODUCT(--(O5:W5<>"")*ISNUMBER(O5:W5)*(O$2:W$2))

Hope this will help

Thank you for this. I have tried this example by copying it into the sheet but unfortunately I am still presented with the VALUE error. I'll spend the rest of this afternoon thinking how I could re-work it.

 

[James006]

Would you have the possibility to upload your sample file to Dropbox or Google drive ... ?

 

[cwhaley1]

Would you have the possibility to upload your sample file to Dropbox or Google drive ... ?

I have uploaded this to my Google Drive account if you can view at the below link? Let me know if any problems and again, thank you for your time.

https://drive.google.com/open?id=1rB6YYDC99usfTDLYD7VRzk9OI4IGrbnM

 

[James006]

Great ...!!!

Do you mean that within your KPI database ... O5 to W19 ...

you could get Text ...instead of Numbers ...?