# Conditionally extend range reference in formula

• 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.

SLAMMER4LIFE.

• 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

[SIZE=3]The Only Dumb Question Is A Question Not Asked.[/SIZE]

• 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 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.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!