Ranking tied numbers

M

mrvica

Version: 2008
Operating System: Mac OS X 10.4 (Tiger)
Processor: Power PC

I am trying to rank a series of numbers and have set the formulas to deal with up to 4 ties, but would like to handle up to 10 ties. The formula I am currently using is fairly complex because I am also only looking at cells that have an entry, I would like to find a more simple method of ranking and would appreciate any help I can get. The current formula I am using is below:

=IF(B10="","",IF(ISERROR(MATCH(RANK(N10,N7:N54,0),P7:p9,0)),RANK(N10,N7:N54,0),IF(ISERROR(MATCH(RANK(N10,N7:N54,0)+0.01,P7:p9,0)),RANK(N10,N7:N54,0)+0.01,IF(ISERROR(MATCH(RANK(N10,N7:N54,0)+0.02,P7:p9,0)),RANK(N10,N7:N54,0)+0.02,RANK(N10,N7:N54,0)+0.03))))

This is the 4th cell in a column of up to 54 cells.

Thanks for any help available.
 
M

mrvica

The original post cut off the formula, so here it is again:

=IF(B10="","",IF(ISERROR(MATCH(RANK(N10,N7:N54,0),P7:p9,0)),RANK(N10
,N7:N54,0),IF(ISERROR(MATCH(RANK(N10,N7:N54,0)+0.01,P7:p9,0)),RANK(N
10,N7:N54,0)+0.01,IF(ISERROR(MATCH(RANK(N10,N7:N54,0)+0.02,P7:p9,0))
,RANK(N10,N7:N54,0)+0.02,RANK(N10,N7:N54,0)+0.03))))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top