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!!

 

[ststern45]

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

2-1-3

 

[barry houdini]

OK, different approach, try this version.....

=TEXT(SUMPRODUCT(MMULT(ISNUMBER(SEARCH({1,2,3,4,5,6,7,8,9,0},TEXT(B10,"000")))*({1,2,3,4,5,6,7,8,9,0}< MID(TEXT(B10,"000"),{1;2;3},1)+0),{1;1;1;1;1;1;1;1;1;1})+1,{100;10;1}),"0-0-0")

 

[ststern45]

OK, different approach, try this version.....

=TEXT(SUMPRODUCT(MMULT(ISNUMBER(SEARCH({1,2,3,4,5,6,7,8,9,0},TEXT(B10,"000")))*({1,2,3,4,5,6,7,8,9,0}< MID(TEXT(B10,"000"),{1;2;3},1)+0),{1;1;1;1;1;1;1;1;1;1})+1,{100;10;1}),"0-0-0")

Very good. Excellent work.

Everything is correct. Tested every possible combination.

The result is in the format of "0-0-0".
I removed the very last entry in the formula to "000".

Is there a way for the result to be without the " "?

This is awesome work Barry.

Thanks so much!!

 

[barry houdini]

If you want it to look like just 213 etc. then use just

=SUMPRODUCT(MMULT(ISNUMBER(SEARCH({1,2,3,4,5,6,7,8,9,0},TEXT(B10,"000")))*({1,2,3,4,5,6,7,8,9,0}< MID(TEXT(B10,"000"),{1;2;3},1)+0),{1;1;1;1;1;1;1;1;1;1})+1,{100;10;1})

 

[ststern45]

If you want it to look like just 213 etc. then use just

=SUMPRODUCT(MMULT(ISNUMBER(SEARCH({1,2,3,4,5,6,7,8,9,0},TEXT(B10,"000")))*({1,2,3,4,5,6,7,8,9,0}< MID(TEXT(B10,"000"),{1;2;3},1)+0),{1;1;1;1;1;1;1;1;1;1})+1,{100;10;1})

Barry,

Thanks so much for all your help!!

Man your gooood!!!!