To celebrate the 6 month anniversary of the last post, here is another tip that might be useful.

While www.alltime-athletics.com provides an overall ranking number for each performance, many people are interested in performances ranked by athlete, Top5, Top10 etc.

You can use a formula that relies on the data being sorted in the right order (eg by Athlete name) but this can be dangerous as the formula will break if you add extra data and forget to resort or sort for some other purpose and forget to return to the name sort.

So what we need is a formula that can calculate a rank by category, irrespective of the order of data.

Unfortunately, Excel does not have an explicit category ranking function but we can achieve the same effect by using another Excel function called SUMPRODUCT. There is a sample formula and an explanation in the attached image.

Let me know if you find this useful.

Excel_SUMPRODUCT_ranking.JPG

While www.alltime-athletics.com provides an overall ranking number for each performance, many people are interested in performances ranked by athlete, Top5, Top10 etc.

You can use a formula that relies on the data being sorted in the right order (eg by Athlete name) but this can be dangerous as the formula will break if you add extra data and forget to resort or sort for some other purpose and forget to return to the name sort.

So what we need is a formula that can calculate a rank by category, irrespective of the order of data.

Unfortunately, Excel does not have an explicit category ranking function but we can achieve the same effect by using another Excel function called SUMPRODUCT. There is a sample formula and an explanation in the attached image.

Let me know if you find this useful.

Excel_SUMPRODUCT_ranking.JPG

## Comment