Formula explination

[Skybluekid]

Hi All,

I am using this formula, SUMPRODUCT((S2>=$S$2:$S$110)+0)+1, (S2 is a text string), which I got from searching on this forum.Not being one for just pasting formula's, I would like to understand how this works.

Thank you

 

[Aladin Akyurek]

1. The bit

(S2>=$S$2:$S$110)

returns an array of TRUE/FALSE evaluations: S2>=$S$2 >> TRUE, S2>=$S$3 either TRUE or FALSE, and so on.

2. SUMPRODUCT understands only numbers.

3. TRUE is 1 and FALSE is 0 in Excel.

4. TRUE+0 >> 1, FALSE+0 >> 0

5. By 2 to 4, the bit

(S2>=$S$2:$S$110)+0

returns an array of 1's and 0's.

6. The bit

SUMPRODUCT((S2>=$S$2:$S$110)+0)

returns each time a count where the comparison value S2, S3, etc. is >= than the values in $S$2:$S$110.

7. Adding 1 to each SUMPRODUCT yields a rank, just like the RANK() function would.

 

[Skybluekid]

Thank you Alan. It makes a lot of sense now

 

[Aladin Akyurek]

Thank you Alan. It makes a lot of sense now

You are welcome.