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.

    Thanks in advance.

    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!