rank tie break using another value

[koi]

hi all,


if you have 3 different category in A1:C10 then you have the sum in D1:D10 and then rank the D1:D10 but then you have a ties rank example 1,2,2,4,5,6,7,8,9,10


all the rank formula i've read is using like countif but can we do it by comparing one of the value example A1:A10 or B1:B10?


so the logic will be.. looking D1:D10 for rank..if there is ties then look for A1:A10 whenever the ties position are then compare those values


result that i wanted is 1,2,3,4,5,6,7,8,9,10

 

[Hui]

Koi


If you have some other field you can use that by adding it to the original values using a helper column before the rank


ie: If Column E had some measure you could use in Column D =Sum(A1:C1)+E1/1000

try and get the value in E1/1000 to always be less than 1


If you don't have some other value you can simply use the Row Number

in Column D =Sum(A1:C1)+Row()/1000

This gives each Rank a slightly different value

 

[koi]

thanks Hui,


thats really work to add helper in E1/1000


regards,

koi