How to avoid the RANK function finding equal results

Below is a mail from a site visitor around the use of the RANK function and how to avoid it finding the same ranking and therefore missing some of the information. On our site we highlight how to use the RANK function and how to do automatic ranking.

However in a situation where there is a chance that some of the ranking items will be the same number then you need to differentiate them some how. If there are other criteria you need to try and build them into the ranking column and the way we do it is by using small numbers. So add 0.0000000001 to a number and then 0.0000000002 to another. It won’t affect what you see but Excel will rank it differently.

Below a YouTube video showing this technique to avoid the RANK function finding equal results

The mail below


Thank you very much on your tutorial on ranking. It is GREAT! I am using your suggestion in a spreadsheet for ranking offers received.

You noted the problem, I have three Offerors with offering the same unit price in the attached workbook. They are Howie

Preci Mfg and Basic Rubber all offering $1.23 unit price for the same item. My Hlookup formula correctly notes Howie at the 4th

Ranked Offeror and N/A for Preci Mfg and Basic Rubber. In lieu of N/A I would like the formula to say Preci Mfg $1.23 and Basic Rubber $1.23

In cells L12,L13 and M12,M13 respectively.

You mention your tutorial their was help somewhere but I have not found it. Any help appreciate.