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!

 

[cwhaley1]

Great ...!!!

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

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

Yes the scores in those columns (scored as 1 to 5) may sometimes contain "N/A". What I'm proposing is that on the scoring sheet (which feeds into the database sheet I have shared with you) is that if a question is not relevant, the scorer selects N/A and this would then be ignored and not used in the formula as it currently does if there's a blank (thanks to your original formula).

 

[Aladin Akyurek]

Could just

=SUMPRODUCT((O5:W5/4),O$2:W$2)/SUM(O$2:W$2)

suffice?

 

[cwhaley1]

Could just

=SUMPRODUCT((O5:W5/4),O$2:W$2)/SUM(O$2:W$2)

suffice?

It could if question weightings didn't matter, but once the result of dividing by 4 is reached this then needs to be multiplied by the weightings in black text at the top of the sheet (row 2, O to W).

 

[Aladin Akyurek]

It could if question weightings didn't matter, but once the result of dividing by 4 is reached this then needs to be multiplied by the weightings in black text at the top of the sheet (row 2, O to W).

You could post a tiny set of representative data (not the whole spreadsheet) along with the expected result for that tiny set.

 

[James006]

Seems the following Array formula avoids the N/A pitfall ...

=SUMPRODUCT((IF(O5:W5="N/A",0,O5:W5)/4)*O$2:W$2)/SUMPRODUCT(--(O5:W5<>"N/A")*--(O5:W5<>"")*(O$2:W$2))

Hope this will help

 

[cwhaley1]

Seems the following Array formula avoids the N/A pitfall ...

=SUMPRODUCT((IF(O5:W5="N/A",0,O5:W5)/4)*O$2:W$2)/SUMPRODUCT(--(O5:W5<>"N/A")*--(O5:W5<>"")*(O$2:W$2))

Hope this will help

That works exactly as intended -- thank you! I can now use the spreadsheet without the manual intervention on each scoring line.

Many thanks.

 

[cwhaley1]

You could post a tiny set of representative data (not the whole spreadsheet) along with the expected result for that tiny set.

Thanks for the input. I had posted a link to the file earlier on in the thread but James006 has saved the day.

 

[James006]

That works exactly as intended -- thank you! I can now use the spreadsheet without the manual intervention on each scoring line.

Many thanks.

Glad you could fix your problem ...