Ranking with SUMPRODUCT

Hi!

I'm trying to figure out how a "conditional ranking" is working on a spreadsheet that I'll attach to this thread. I want to learn/understand so I can apply this on different situations, the part that confuses me the most is the "*10^-6".

Basically, there are two formulas on this spreadsheet:

Ranking two criteria
=SUMPRODUCT(--(($C$3:$C$5+$D$3:$D$5*10^-6)>C3+D3*10^-6))+1
Captura de Tela 2021-12-26 às 23.21.48.png

Ranking three criteria
=SUMPRODUCT(--(($C$10:$C$20+$D$10:$D$20*10^-3+$E$10:$E$20*10^-6)>C10+D10*10^-3+E10*10^-6))+1
Captura de Tela 2021-12-26 às 23.22.04.png

Could anyone help understand this SUMPRODUCT?

I tried to test a formula but couldn't get it to work on the pictures...

Pl read the yellow banner on top of this page.

The first 5 values all tie, with a score of 1 (100%). Ties are broken with priority being assigned in the order: satisfaction, Surveys answered and (finally) Qs answered.

The ties are resolved first by multiplying the No. of surveys answered by 1/1000 (10^-3) and adding that to the % scores; and secondly by multiplying the number of Q answered by 1/1,000,000 (10^-6) and adding that to the total of % and Survey No/1000. In that way an overall ranking score taking into account all 3 parameters is achieved. You just have to be a little careful with the 10^-3 and 10^-6 to ensure that the 3rd parameter can not, on occasion, override the 2nd parameter...

in the attached sheet, the 3rd priority parameter (Qs answered) has more weight than the 2nd priority parameter (surveys taken) and so the ranking order is thrown out. Changing the 10^-6 to 10^-9 fixes it again.

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.