Compare a number to 3 others - give it's position relative t

[Chris The Rock]

I have a number, and I'd like to compare it to 3 others. If it's Less than all 3, it would be the 4th. If it's Less than only 2 of them, it would be 3rd. If it's less than only 1 of them, it would be 2nd. Finally, if it's the largest number, it would be 1st.

Picture the number used in the comparison in A1. Picture the other 3 numbers in B1, C1, and D1. The numbers in B1, C1, and D1 are NOT sorted in any way.


Would I need an array formula for this?

 

[Aladin Akyurek]

On 2002-08-28 07:57, Chris The Rock wrote:
I have a number, and I'd like to compare it to 3 others. If it's Less than all 3, it would be the 4th. If it's Less than only 2 of them, it would be 3rd. If it's less than only 1 of them, it would be 2nd. Finally, if it's the largest number, it would be 1st.

Picture the number used in the comparison in A1. Picture the other 3 numbers in B1, C1, and D1. The numbers in B1, C1, and D1 are NOT sorted in any way.


Would I need an array formula for this?

You bet...

=SUMPRODUCT((A1< B1:D1)+0)+1

operates on arrays, but normally entered.
This message was edited by Aladin Akyurek on 2002-08-28 08:06

 

[Chris The Rock]

Aladin and SUMPRODUCT to the rescue.

Thanks!