# newbie looking for Excel function help

#### curran_john46

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

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
=COUNTIF(D9:D101,"Y")/COUNTA(D9:D101)

Try

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

format D102 as percentage

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.

Thank you as well Barry.

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

Same idea, just combine the two ranges...

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

Replies
0
Views
2K
Replies
4
Views
3K
Replies
1
Views
492
Replies
1
Views
400
Replies
0
Views
377

1,219,819
Messages
6,150,404
Members
450,960
Latest member
GB2

### 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