what is the largest gap between rank and approximate rank?

Rank function

• QUESTION: While using the rank function in ansheet some positions/ranks skips. For ex. the entries are a2 = 100, a3 = 100, a4 = 60 , a5 = 50, a6 = 40, then the rank will appear like this b2=1,b3=1,b4=3,b5=4,b6=5. If we observe closely it skips rank 2 and jumps to subsequent rank, if both numbers are equal. Guide me how to solve this problem ? ANSWER: UP, you have a tie for 1st. 100 and 100. so it gives them both a rank of 1 and skips the 2nd ranking because two positions are ranked first. this is detailed on the help on the rank function and is common practice. What criteria would you use to assign A2 or A3 a rank of 2 since they are tied? if you want to just assign tie breakers by what row the tied values fall in, then in B2 put the formula =RANK(A2,$A$2:$A$6)+COUNTIF($A$2:$A2,A2)-1 and drag fill it down the column. ---------- FOLLOW-UP ---------- QUESTION: As per your guidance if I enter the above formulae it works fine, but after that if I change the value of a3=70 rank was still 1 and if a3=60 then rank of a3 & a4 was 1. Pl. guide me ... ANSWER: UP I tested the formula with your data. It worked fine for me. It sounds like you have calculation set to manual. Unless you make it automatic, the your formulas won't recalculate. If you want to control calculation and leave it as manual, then you need to hit F9 everytime you make a change if you want the sheet to be recalculated. But, most people leave calculation as automatic. So there is nothing to change in terms of the formula. Everything works fine as far as the formula is concerned. ---------- FOLLOW-UP ---------- QUESTION: Sir as you said the if the same made automatic then it would provide accurate result. Will you kindly provide me a sample.

• Answer:

  See the attached picture (I revised my answer to explore another possiblity. Hopefully the picture remains with the answer). > I change the value of a3=70 rank was still 1 and if a3=60 then rank of a3 & a4 was 1. A3 had a rank of 3 when it had the value of 60 using your sample data (initial situation you described). Changing it to 70 did not change that. Changing it back to 60 did not change that. It stay with a rank of 3. It did not affect other cells. also recall what I said in my first answer: >if you want to just assign tie breakers by what row the tied values fall in, then in B2 put the formula >=RANK(A2,$A$2:$A$6)+COUNTIF($A$2:$A2,A2)-1 >and drag fill it down the column. Did you drag fill the formula down the column? In B6, it should look like this =RANK(A2,$A$2:$A$6)+COUNTIF($A$2:$A6,A2)-1 compare that to B2 =RANK(A2,$A$2:$A$6)+COUNTIF($A$2:$A2,A2)-1 note how in the countif the range has changed to $A$2:$A6 from $A$2:$A3 Other than that, I don't know what you mean by provide you a sample.