Formula Question - Ranking

[ststern45]

Hi everyone,

I have a question.

In cell B10 I have the number 756

In cell C10 I have the number 7
In cell D10 I have the number 5
In cell E10 I have the number 6

In cell F10 I would like to enter a formula that tell me the "ranking" of the 7 in relation to the other 2 numbers in cells D10 and E10

Or if possible a formula the would look at the 756 in cell B10 and tell the ranking:
So in the above example it would be 132 since the 7 is the highest, the 5 is the third and the 6 would be second.

Thanks in advance!!

 

[barry houdini]

Is it always a 3 digit number?

What if you had 771 how would that rank, 113?

 

[ststern45]

Yes it would always be a 3 digit number.

The double digit (771) 1-1-2 number would be correct. Same as it would be with 3 exact digit.

Thanks

 

[barry houdini]

This formula would give you 113 for 771, 777 would be 111

=SUMPRODUCT(MMULT({1,1,1},(MID(B10,{1,2,3},1)< MID(B10,{1;2;3},1))+0)+1,{100,10,1})

 

[tushiroda]

hi Barry,

can you explain the effect of the use of ";" vs. ","? also, why doesn't using "," in both work?

thanks!

 

[ststern45]

Thanks Barry.

Could you change the formula so that 771 = 221?

Also, could you check 3 digit numbers that start with 00X, Example, 007 would be 112

Thanks for all your help!!

 

[barry houdini]

can you explain the effect of the use of ";" vs. ","? also, why doesn't using "," in both work?

If you were to use

MID(B10,{1,2,3},1)< MID(B10,{1,2,3},1)

then the array returned would always be {FALSE,FALSE,FALSE}

because you would just be comparing each digit against itself. By changing the notation you are comparing a "column" against a "row" and each value will be compared against all three, the result being a 3x3 matrix of values

 

[ststern45]

Hi Barry,

Thanks for all your help.

Is it possible to change the formula:

For example: 0-1-0 (010) the rank should be 121 but the formula returns 123

Thanks!!

 

[barry houdini]

Why is it 121, zero counts higher than a 1? Does zero count higher than any number, what should 307 return?

 

[tushiroda]

If you were to use

MID(B10,{1,2,3},1)< MID(B10,{1,2,3},1)

then the array returned would always be {FALSE,FALSE,FALSE}

because you would just be comparing each digit against itself. By changing the notation you are comparing a "column" against a "row" and each value will be compared against all three, the result being a 3x3 matrix of values

ah, very interesting. Is it the case where I can use either "," or ";" for all array based formulas? I tried switching the "," and ";"s in the formula and it seemed to not work. I did try searching on this, but the special character searches don't work very well.