how do I 'shadow rank' a number into a field it isn't a part of

[Guest]

I want to 'shadow rank' a number to determine how that number would rank IF
it were a part of the main group HOWEVER, I don't want to include it in the
main field, since it will increase 'n', lead to double counting and cause a
downstream domino impact if I were to include it.

e.g..,

A1 through A15 is the population ... Standard function = RANK(A1, A1:A15,0)

A22 in my fictitious example is a 'cluster' of my stores (let's say Store A1
and Store A2) and I want to know how the summed performance of that store
would would rank if they were compared to the A1:A15 population. So, I want
to RANK(A22,A1:A15,0).

To make matters more frustrating, I've fired that into my spreadsheet and
get errors across the board except for one single line where it does return a
'shadow rank' .... Haven't a clue why that one line sems to work, but it
doesn't work elsewhere ... Any thoughts?

 

[Bernie Deitrick]

Greg,

=RANK(A22,A1:A15,0)
will only work if the value in A22 is an exact match for a value in A1:A15.

To rank a value that doesn't appear in the list, you need to use this:
=SUMPRODUCT((A1:A15>A22)*1)+1

And if you wanted to rank lower values first, then you would use:
=SUMPRODUCT((A1:A15<A22)*1)+1

(This would be the equivalent of RANK(A22,A1:A15,1))

HTH,
Bernie
MS Excel MVP