Calculating a percentage of "Y" and "N" in dynamic cell range

Technically you can just apply for formula like you did for Avg duration, except that the formula will look like =COUNTIF($I$2:$L$3535,"Y")/COUNTA($I$2:$L$3535)

Please Login or Register  to view this content.

Regards, TMS

Hi and welcome to the forum.

Using a macro to accomplish this task seem rather OTT, particularly one which involves a loop. Loops are inherently slow and should only be used if there's no standard Excel functions that you can use. In this case you can use simple COUNTIF() and COUNTA() functions.

First create a dynamic range name which will adjust automatically to cater for any number of rows that you may add. I created one called
'Data_YN' and defined this in the name manager as

Formula:

Please Login or Register  to view this content.

Now all you need is to put this formula in N1, or anywhere for that matter

Formula:

Please Login or Register  to view this content.

Make sure when you define the name that the Scope is set to Sheet level. Then assuming you copy the sheet to create the other 23 the same name will be present on each one but scoped to the sheet its on. If the 24 sheets are already in place then put the following line of code in a macro that iterates through all the sheets

Please Login or Register  to view this content.

late edit...just spotted an error. ....wait one... Now Corrected

Oops, missed a dot or two ...

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Regards, TMS

Originally Posted by InkyDrinky

TMS... it was amazing how fast you did that!!!

I had to make one change...

lLR = Range("H" & Rows.Count).End(xlUp).Row

I had to change xlUp to xlDown...

xlUp was giving me the range of

=AVERAGE(H2:H3461)

xlDown was giving me the range of

=AVERAGE(H2:H3535)... which is the correct range.


Dan

Hi Dan,

There seems something wrong there. I see no reason why Trevor's xlUp would return 3461 as the last row. The technique of always selecting the very last cell and doing an xlUp is generally regarded as the safest way to find the last row, particularly where there might be blanks in the column since an xlDown would in that case not return the last row.

If all the cells are contiguous with some value in each then an xlUp and an xlDown will return the same value.
It might be worth double checking what was happening with your particular example.


...late edit... I see Trevor has just responded. However it's still better practice to use xlUp for the reason stated.

@Richard: my error. Probably means the first/active sheet only had 3461 rows. As there was only one sheet, that aspect didn't get tested. And even if I'd copied the sheets, they'd probably just have been straight copies.