query regarding index and match

You can change the formula in F7 to this:

=RANK(D7,$D$7:$D$14)+COUNTIF($D$7:$D7,D7)-1

to give each a unique ranking. Then your top-5 table will work.

Hope this helps.

Pete

Thanks Pete.. Awesome...

It works..

but i dont understand what this COUNTIF($D$7:$D7,D7)-1 is doing exactly, can u explain a bit..

Thanks alot

Saad

Notice that the range in the COUNTIF is anchored at D7, but the second element of the range is not anchored, so as it is copied down the range varies. It counts how many times D7 occurs in the range D7:D7 (obviously once) and then subtracts 1 from it to leave the RANK part of the formula unchanged.

When it gets to F9 (the first time that D7 is 0%) that part of the formula is now:

COUNTIF($D$7:$D9,D9)-1

and again this results in zero so the RANK is unchanged.

However, at F12 it becomes:

COUNTIF($D$7:$D12,D12)-1

and now it counts that there are two 0% values in the range D7:D12, and so this expression results in 1, to be added on to the RANK.

In the next cell the expression results in 2 added on to the RANK value, and so on.

Hope this helps.


Pete

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum (in terms of posts) you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Thanks a bunch Pete.. got your explanation aswell..

I will mark it as resolved.