I've noticed that certain functions don't return what I call "true ranking" when there are duplicates in a range.
Here's is an example. In cells B2:B21 I have 20 numbers and I want to sort them from the largest values first. To do this I have used an array formula which is =LARGE( B2:B21,ROW(INDIRECT("1:20")))
. Note the braces at either end of the formula in the picture. You don't enter them yourself, they appear automatically when you push Ctrl, Shift and Enter which is how array formulas are entered (as opposed to just using Enter). Select your range first, then enter the formula as mentioned above.
So far so good. The problem is that any duplicates will cause inaccurate results. For example, I've now entered 200 into cell B7, which causes 200 to appear twice in Column C. Not good, the first and second largest values should definitely not be the same.
How to get around this? One way would be to use the wonderful Morefunc.xll
add-in by Laurent Longre
which contains 41 useful functions. For example, I have used the UNIQUEVALUES
function here in an array to help solve the duplicates problem.
Well, this solved the problem very nicely, but unfortunately the same add-in must be installed in every computer where the file is used, so if this is the case, you might find yourself wondering if there is an alternative solution.
Here's a way that I thought up (probably not the best but it seems to work) First I'll enter a "duplicate ignoring" formula in cell C2 and drag down.
Here's how it looks.
Now I enter the same LARGE
array formula as before like this.
Hmm... I don't like that #NUM! error at the bottom so I'll rewrite it like this.
And here is the picture.
Well, that seems to do the job. Works for me!
By the way, you can read more about Mr. Longre and other Excel wizards at my friend Colo's site, Colo's Excel Junk Room
, on the Cell Masters