Dynamic Ranges

[Guest]

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

Many Thanks

 

[Guest]

Try this:

=COUNTIF(INDIRECT("J"&B2&":J"&A2),"Yes")/COUNTA(INDIRECT("J"&B2&":J"&A2))*100

HTH,
Elkar

 

[Guest]

=COUNTIF(INDIRECT("J" & B2 &":J"&A2),"Yes")/COUNTA(INDIRECT("J" & B2
&":J"&A2))*100

 

[Harlan Grove]

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),"Yes")
/COUNTA(INDEX(J1:J1000,B2):INDEX(J1:J1000,A2))*100

Then there's shorter.

=100*PRODUCT(COUNTIF(INDEX(J1:J1000,B2):INDEX(J1:J1000,A2),
{"Yes","<>"})^{1,-1})

 

[Guest]

Many thanks to you both!

Try this:

=COUNTIF(INDIRECT("J"&B2&":J"&A2),"Yes")/COUNTA(INDIRECT("J"&B2&":J"&A2))*100

HTH,
Elkar

 

[Guest]

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 ?