newbie looking for Excel function help

[curran_john46]

Hi,

I have been struggling to figure out a function to perform a specific task in excel. Might anyone have any pointers? I was looking at the percentage function but can't quite figure out how to use it for the scenario below. Perhaps there is another more suitable function. Below is what I am trying to do in my excel spreadsheet (very similar to calculating a simple batting average):

The fields D9 though D101 can be set to 'Y' or 'N.' Each 'Y' represents a successful transaction in our business application. Each 'N' represents a failed transaction in our business application. I want to calculate the success rate in percentages and show that success rate in D102.

My hope is to have the 92 fields between D9 and D101 prepopulated with 'Y' and have D102 showing a 100% success rate. And if I change any of the fields between D9 and D101 to 'N' then the success rate percentage in D102 would lower itself accordingly.

So, if D9 - D101 all show 'Y' then D102 = 100%. However, if 46 of the 92 fields between D9 - D101 are set to 'N' then D102 = 50.00%. Or if 45 of the 92 fields between D9 - D101 is set to 'N' then D102 = 48.91%. Or if two of the 92 fields between D9 - D101 is set to 'N' then D102 = 02.17%. You get the point now.

Can this work the way I intend in excel? Is there a function and formula that could help me here?

Thank you so much.

John

 

[Oaktree]

=COUNTIF(D9:D101,"Y")/COUNTA(D9:D101)

 

[barry houdini]

Try

=COUNTIF(D9:D101,"Y")/ROWS(D9:D101)

format D102 as percentage

 

[curran_john46]

Thank you Oaktree. That was perfect. I formatted the cell for percentages as well and now it works like a champ. Thanks again. I appreciate it.

 

[curran_john46]

Thank you as well Barry.

 

[curran_john46]

What if I want to show the percentage of fields set to 'Y' in D50 through D75 and D100 through D150?

 

[Oaktree]

Same idea, just combine the two ranges...

=(COUNTIF(D50:D75,"Y")+COUNTIF(D100:D150,"Y"))/(COUNTA(D50:D75)+COUNTA(D100:D150))