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

#### dmassmini

##### New Member
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!

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Hi dmassmini and welcome to the board.

Maybe SUMPRODUCT is what you need.

Otherwise post an example of your data

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!

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"))

Replies
14
Views
243
Replies
4
Views
140
Replies
0
Views
183
Replies
1
Views
102
Replies
1
Views
174

1,203,683
Messages
6,056,728
Members
444,887
Latest member
cvcc_wt

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back