Confused on how to properly use ranking formula w/ties. And Vlookups and IF statements.

[dmejia174]

Hello,

Was hoping to get some assistance..and i appreciate everyone's time for taking time to read this. In the attachment, i have one attachment with 2 sheets. Stores , and TOP 20.
All the data is in Stores sheet...however, my ranking that is in Column A - is incorrect. I am trying to figure out what is the proper formula so that it doesnt skip when there is a tie. For example on Column O - there is multiple 0% - which comes from M/I..... so it calculates the ranking with multiple 1st place rankings.

Then hopefully, i can go into TOP 20 sheet, and fill in the 1st table correctly without having to do copy and paste.

Table 2 (below table 1 to the left) i have been advised if i can complete the table and create formulas.....please help! if the goal is 95% which comes from Column O. how can i create a formula that will give me the ranks, along with the district name and whether it was 95% or under...so hopefully E33 can give me whether it was plus or minus the 95% threshold.

Table 3- same as above. i am hoping i can fill in the metrics based upon the 95% goal that is found in Column O in the stores sheet.

Please help! and thank you for your time!

[martindwilson]

ranking col O
=SUM(IFERROR(1/COUNTIF($O$8:$O$114,IF($O$8:$O$114<O8,$O$8:$O$114)),0))+1 array entered gives a rank like
1
1
1
2
2
3
4
4
4
4
5

[martindwilson]

ranking col O
=SUM(IFERROR(1/COUNTIF($O$8:$O$114,IF($O$8:$O$114<O8,$O$8:$O$114)),0))+1 array entered gives a rank like
1
1
1
2
2
3
4
4
4
4
5
=RANK(O8,$O$8:$O$114,0)+COUNTIF($O$8:O8,O8)-1
will give
100
99
98 which is probably what you are trying to achieve since you can now lookup large() 1 through 20

[dmejia174]

Thank martindwilson! I have now resolved table 1....i hope to now resolve table 3...Thanks!!!