Need help on how to calculate the average

[Excel_newbie1210]

Hi,

I am working on a form and am i stuck in figuring out what formula to use in order to get the result that I need.
This involves criteria with result of N/A and its effect on the whole score.

I have attached detailed questions and raw data in excel hoping someone can help me.


Thanks in advance for your help.

[JohnTopley]

This ???

=COUNTIF(E4:E16,"=YES")/COUNTIF(E4:E16,"<>N/A")

[twiggywales]

im not 100% sure what you are trying to acomplish but is this what you mean?

[Excel_newbie1210]

Hi,

Thank you for your quick reply. Apology if I was not able to explain my self correctly. I am trying to find a way wherein the criteria with N/A result will not affect the total score. Meaning if there is a result of N/A the score will still be 100%. For example if I choose all YES, the total score will be 100%. If I have 1 N/A (with equivalent points of 0.03) the total will be 97 points but the percentage will still be 100%. This means that with the N/A the maximum points that could be given will be 97/97 = 100%. Hope it makes sense.

Appreciate your help.

[twiggywales]

im still not sure what you mean, can you create a sheet showing some expected results (several versions some with NA some with yes etc) so i can try replicate results

[Excel_newbie1210]

Hi,

I attached sample scenarios.

[JohnTopley]

Try

=SUMIF(E4:E32,"=YES",D4:D32)/SUMIF(E4:E32,"<>N/A",D4:D32)

[Kaper]

Hmm, as I see it in G35

Please Login or Register  to view this content.

of course you can add =

Please Login or Register  to view this content.

PS. Somehow - I can't see average (as posted in title) here

[twiggywales]

to be honest i may have gone a bit wonky on this but i think i got it to work (however if the above post works use that as its more elegant)



also you say the last scenario should be 96% i think it should be 97% (92/95)*100 = 96.84%

[Excel_newbie1210]

@twiggywales, is there a possibility that the formula would also include the data in section "part 2"? attached is a file with sample scenario and expected result.

[Kaper]

Please comment also on John's and mine suggestions (they may be wrong, but anyway - let us know. we can stand it ).

[twiggywales]

im not sure why it wasn't anyway i must have missed something but i have update to suit.


personally this formula has got way outa hand i would be looking for a better solution. if you try the other guys ideas they might work better without all this junk.


to be fair i wouldn't use my own formula here i would find another way.

[Excel_newbie1210]

Hi all, thank you so much for your help! I was able to combine all your suggestions and come up with a solution. All your inputs are greatly appreciated.

[Kaper]

I'm glad to hear so, and thanks for marking thread solved and for reputation point.