Duplicate Rank Problem

I have used the function that have been suggested in the following link that seems to work well for a small number of data points.

http://www.excelforum.com/excel-gene...-formulas.html

However when I use a large series I get two errors in the attached spreadsheet. I have highlighted them in red line 42 & 92. This occurs when ranking the 100 number. I have decomposed the full function in columns E & F and the count function is working fine but the rank function in these two examples jumps from the usually seen (see yellow highlights) 221 to 285. I have used RANK as well as RANK.EQ as here and get the same result.

Does anyone have a reason why this is occurring? I am not able to sort the data - this is the order that it comes and I don't want to change it.

Thank you for your help

Stewart

Thanks Trevor for getting back to me - I tried your option and sadly still got the same issue.

However, after having a think (and a good night's sleep works wonders) I have resolved it. Before I get the numbers into column A if I round them to say 6 decimal places it works. It must be that those two 100s that were causing problems are stored by Excel at a slightly higher value than the rest of the 100s although I took the dec places up to maximum and could see no difference. By rounding to six dec places it seems to resolve my issue.

What is odd in the example is that the RANK.EQ function could see a difference between the 100s whereas the COUNTIF function could not.

Thank you

Stewart