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.
Re: Array Function Help: Rank within a conditional range
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:
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 non-contiguous 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.