How To Rank Or Create A Top 5 Count / Rank With If Condition

[PaddyP]

Hello,

Looking for some help on this please see attached.

The data is in columns D & E. What I need is a formula that can give me the results in column F.

I only need the top 3 but if the formula ranks all values for A that would be fine too.

If the values are the same they can return the same rank that is not a big deal.

Note A may have more or less entries than B or C etc.

Thank you!

[Melvosh]

Try this:

Please Login or Register  to view this content.

You could use an IF statement or conditional formatting to hide values larger than 3. Does that give you what you're looking for?

[PaddyP]

AMAZING!

I am not entirely sure how it works but it does!

Thank you very much.

[Melvosh]

You're welcome, happy to help, thanks for the rep

I'll offer the explanation the way I understand it, which may not be exactly correct as to how Excel functions behind the scenes. Basically, it creates two arrays of TRUE/FALSE statements, also seen as arrays of 1/0. It compares the arrays and finds the one spot where both are TRUE, and returns that common location. The +1 is so the ranks start at 1 instead of 0. You can always go to the Formulas tab in the ribbon and click Evaluate Formula to see what's going on. It's one of the most useful tools in Excel!

[PaddyP]

Very useful. Thanks again. I will certainly check that out in the future.

[PaddyP]

Hello Again,

Wanted to follow up and say how much my team has appreciated the shortcuts that this formula has provided!

I am wondering if we can perfect it with one further step. Right now if two values are the exact same it will return the same rank.

Current Example

A $10 1
A $9 3
A $10 1
A $8 4

What I would love to see is one of the 1's switched to a 2. (Doesn't matter which 1 is swapped)

Let me know if there is an easy solution. If not I can swap the ties manually. It is not a huge deal the original formula solves 99% of my problems.

[Melvosh]

This is based on the file in post #1. Try this:

Please Login or Register  to view this content.

It may need some adjustments, depending on the ranges in your actual file. Hopefully it works for you.

[PaddyP]

Thank you Melvosh!

We are almost there! This does solve the problem I had for the above example however if there are more than 2 items with the same value the 2nd and 3rd will now return same value.

Anyway around this?

[Melvosh]

Try this:

Please Login or Register  to view this content.

[PaddyP]

BRAVO!

Very grateful for your help on this.

Thank you!

[Melvosh]

You're welcome, thanks for the rep!