How to give Y, N and NA responses values and average

[dmassmini]

I have put together a QC scorecard with Y, N and NA responses. I need help setting Y=1, N=0, summing the results and then averaging the results, excluding any NA responses from the denominator. Help!

 

[Robert Mika]

Hi dmassmini and welcome to the board.

Maybe SUMPRODUCT is what you need.


Otherwise post an example of your data

 

[dimassimini]

My column header is "Did the auditor perform complete anaylsis & research." Available options in the column drop down list is Y, N or N/A. I would like to be able to determine an average for this attribute but exclude any responses with N/A from denominator. So for example 100 total files reviewed, 50 Y, 30 N and 20 N/A. I would like to create a formula that says Y=1, N=0 and exclude N/A so my average above would reflect a 62.5% pass rate. Sorry, a newbie so hard to explain....

THANKS!

 

[Aladin Akyurek]

My column header is "Did the auditor perform complete anaylsis & research." Available options in the column drop down list is Y, N or N/A. I would like to be able to determine an average for this attribute but exclude any responses with N/A from denominator. So for example 100 total files reviewed, 50 Y, 30 N and 20 N/A. I would like to create a formula that says Y=1, N=0 and exclude N/A so my average above would reflect a 62.5% pass rate. Sorry, a newbie so hard to explain....

THANKS!

Try...

=COUNTIF($A$2:$A100,"Y")/SUM(COUNTIF($A$2:$A100,"Y","N"))