Weighted Average with NA...can't calculate

**salmanjan** · 04-17-2008, 02:38 PM

I am looking to get weighted average at the very end of the spreadsheet, but due to NA (some items not applicable) the forumal is giving me error ie., #VALUE!

The reason for this is that it is including the text NA into calculation, whereas I want to calculate the weighted average, can anyone please help me in finding the solution to this problem.

Problem statement:
The Total Score should exclude NA, and should give weighted average percentage for percentages shown for Data Entry & Underwriting only.

Thanks in advance!

**Richard Buttrey** · 04-17-2008, 03:26 PM

Originally Posted by salmanjan

I am looking to get weighted average at the very end of the spreadsheet, but due to NA (some items not applicable) the forumal is giving me error ie., #VALUE!

The reason for this is that it is including the text NA into calculation, whereas I want to calculate the weighted average, can anyone please help me in finding the solution to this problem.

Problem statement:
The Total Score should exclude NA, and should give weighted average percentage for percentages shown for Data Entry & Underwriting only.

Thanks in advance!

Why not just leave out the references to C7 & C33? i.e.

Please Login or Register  to view this content.

Rgds

**oldchippy** · 04-17-2008, 03:43 PM

Or if you must refer to it, may be this

=IF(G7="NA",(C34*C18+C30*C35)/(C18+C30),(C33*C7+C34*C18+C30*C35)/(C7+C18+C30))

**daddylonglegs** · 04-17-2008, 04:00 PM

Perhaps try

=SUMPRODUCT((A7:A30="")+0,C7:C30,G7:G30)/SUMPRODUCT((A7:A30="")+0,C7:C30)

**salmanjan** · 04-18-2008, 03:28 PM

Cell with NA has to be included, as there could be a possibility that in some cases it is applicable, so we can not have different work sheets for different scenarios.

Ok....so the Weighted Average Percenatge is coming out as:

74.8684368%
with =(C34*C18+C30*C35)/(C18+C30) and

=IF(G7="NA",(C34*C18+C30*C35)/(C18+C30),(C33*C7+C34*C18+C30*C35)/(C7+C18+C30)) formula.....and since that field with NA is sometimes applicable and sometimes not therefore I guess this formula works best for me in curent situation.

This formula =SUMPRODUCT((A7:A30="")+0,C7:C30,G7:G30)/SUMPRODUCT((A7:A30="")+0,C7:C30) is giving me 72.6920288%, which is not correct, or am I referring to incorrect cells.

I have found the solution courtesy oldchippy post......thanks Richard Buttery, oldchippy and daddylonglegs.....

Regards,

**daddylonglegs** · 04-18-2008, 08:24 PM

Originally Posted by salmanjan

This formula =SUMPRODUCT((A7:A30="")+0,C7:C30,G7:G30)/SUMPRODUCT((A7:A30="")+0,C7:C30) is giving me 72.6920288%, which is not correct, or am I referring to incorrect cells.

Yes, I see. Try

=SUMPRODUCT((A7:A30="")+0,C7:C30,G7:G30)/SUMPRODUCT((A7:A30="")+0,--ISNUMBER(G7:G30),C7:C30)