Weighted Average Ignoring Blanks

cwhaley1

New Member
Joined
Nov 22, 2017
Messages
36
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!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Hello,

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

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

Hope this will help
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Would you have the possibility to upload your sample file to Dropbox or Google drive ... ?
 
Upvote 0
Great ...!!!

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

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

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top