newbie looking for Excel function help

curran_john46

New Member
Joined
Sep 27, 2006
Messages
4
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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

curran_john46

New Member
Joined
Sep 27, 2006
Messages
4
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

New Member
Joined
Sep 27, 2006
Messages
4
What if I want to show the percentage of fields set to 'Y' in D50 through D75 and D100 through D150?
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
Same idea, just combine the two ranges...

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

Forum statistics

Threads
1,136,510
Messages
5,676,281
Members
419,617
Latest member
Shane50GT

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top