weighted average including N/A

Hello all.

First time post and I am hoping someone can help me out

I have attached a spreadsheet which I am working on. I have a percentage score for a number of criteria in a call centre. I want to ensure that the user can select N/A when a criteria is not applicable and make sure that the result of this selection does not affect the overall score. I have a scoring of 0%, 25%, 50%, 75% and 100% for each question/criteria. By selecting NA the user should not be skewing the data.
Hope this makes sense.

Many thanks in advance

Andrew

I think the way you have it set up will work. People can choose N/A and that choice will not affect your results because on the Summary Sheet, you are only Including Columns E:I. You might want to include a separate stat for how many N/A's the team received for a specific catagory and trend those to see if catagories are being appropriately fielded.

on a side note, you can clean up your formulas a bit. For example in Summary!E10
Instead of
=SUM('Call 1'!E10,'Call 2'!E10,'Call 3'!E10,'Call 4'!E10,'Call 5'!E10)
Try
=SUM('Call 1:Call 5'!E10)

and in O9, instead of
=SUM((I10*I9),(H10*H9),(G10*G9),(F10*F9),(E10*E9),(I11*I9),(H11*H9),(G11*G9),(F11*F9),(E11*E9),(I12*I9),(H12*H9),(G12*G9),(F12*F9),(E12*E9),(I13*I9),(H13*H9),(G13*G9),(F13*F9),(E13*E9))/SUM(E10:I13)
try
=SUMPRODUCT($E$10:$I$13*$E$9:$I$9)/SUM($E$10:$I$13)
Hope that helped.

It changes your data but doesn't skew it. You've removed an "Excellent" and put it into "NA" which isn't counted so your overall score will go down. Let's look at it from another angle. Let's say every call rated "Opened with Company name" as "Good". Your score would be (5 * 0.75)/5 or 75%. Now move 2 (a significant number) over to NA. Your calculation is now (3 * 0.75)/3 or 75%. It doesn't change your results. If it did, that would be skewing. Does that make sense?

I would include an IF statement to check the denominator. For example in Summary!O9
=IF(SUM(SUM(E10:I13)=0, "N/A", =SUMPRODUCT($E$10:$I$13*$E$9:$I$9)/SUM($E$10:$I$13))

Does that work for you?

OOps, that = shouldn't be in front of the SUMPRODUCT. I'll take a look at the rest of your sheet.

=IF(SUM(E10:I13)=0, "N/A", SUMPRODUCT($E$10:$I$13*$E$9:$I$9)/SUM($E$10:$I$13))

Find attached, an updated copy of your spreadsheet. Let us know if there are any questions or concerns.

No problem and thanks for the Rep points. Can you mark this thread solved? (Edit your first post and go advanced and change the title prefix).