I'm trying to return the rank of a relative value for a row based upon a conditional range. Basically  if K25="Yes", then include the adjacent cell L25 as a member of the searchable range. I've had no problem returning the MIN or MAX from this range using an array function, but no luck with RANK.
Thanks in advance.
SLAMMER4LIFE.
Conditionally extend range reference in formula



Re: Array Function Help: Rank within a conditional range
=SUM(IF($K$25:$K$30="Yes",$L$25:$L$L30))
Something like this? this is an array 
Re: Conditionally extend range reference in formula
I have found a way to achieve what you want with a formula, but its a pain to enter and not really practical if you have a large range. As a sample:
=RANK($A2,(IF($B$2="Yes",$A$2,$A$7),IF($B$3="Yes",$A$3,$A$7),IF($B$4="Yes",$A$4,$A$7),IF($B$5="Yes",$A$5,$A$7),IF($B$6="Yes",$A$6,$A$7)),1)
For column of numbers in A2:A6 and Yes/No conditions in B2:B6. In addition you need an additional value in A7. If using a RANK order of 0 then it needs to be =MIN(A2:A6)1. For RANK order of 1 then needs to be =MAX(A2:A6)+1. Although I did find that setting A7 to 9.99E308 (a VERY large number) worked for both order types, I think this is because of an overflow condition within the RANK function so probably best avoided.
This method relies on submitting the reference to RANK as a series of potentially noncontiguous areas, for which you have to separate the ranges with <comma> and enclose the entire argument with () so that Excel does not misinterpret the <comma> as end of the argument. If there was a way to create the series without the sequence of IF functions then clearly it could be used. So far I have not found a way to do that.
A more practical approach might be to use Advanced Filter and then RANK on the filtered range.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!