Dynamic Ranges
Thank-you to you too Harlan. When you say 'better way to do this', in what
context ? The previous works, would it be worth my while to change it to your
suggestion ? There often appear to be different ways of achieving the same
result in Excel ?
"Harlan Grove" wrote:
Graham wrote...
Hi, Following previous advice, the following formula returns the %
"Yes" in a range from J84:J**; ** being referenced from a value in
cell A2. Is it possible to now tweek this formula so that the first
part of the range (J84)is also remotely referenced from a value in
say B2 ? i.e. J"&B2:J"&A2 ?
=COUNTIF(INDIRECT("J84:J"&A2),"Yes")/COUNTA(INDIRECT("J84:J"&A2))*100
Yes, but there's a better way to do this.
=COUNTIF(INDEX(J:J,B2):INDEX(J:J,A2),"Yes")
/COUNTA(INDEX(J:J,B2):INDEX(J:J,A2))*100
Better still would be to restict this to the largest possible range.
If that were J1:J1000,
=COUNTIF(INDEX(J1:J1000,B2):INDEX(J1:J1000,A2),"Ye s")
/COUNTA(INDEX(J1:J1000,B2):INDEX(J1:J1000,A2))*100
Then there's shorter.
=100*PRODUCT(COUNTIF(INDEX(J1:J1000,B2):INDEX(J1:J 1000,A2),
{"Yes","<"})^{1,-1})
|