I really wanted to have a chart on my sheet which was sorted from high to low. Imagine if you wanted to create a bar chart which showed net income by company and wanted the chart to be sorted high to low. One solution is to just manually sort the data. Another option is to create a macro which copies the data somewhere (values only) and sorts the data, then chart the sorted data. These options all require the user to 'do something' to get the sorted chart.
I came up with a new approach using just the MAX feature, VLOOKUP, and a simple IF. The original data is in the left columns C3:C8. I just take the MAX of the group, then use a simple IF statement to assign a "One", "Two", etc, then just a VLOOKUP to get the order back at the bottom of the sheet. The IF statement is just =IF(C4=$C$9,"One",C4).
The reason I spell out the rank order is because if you don't do this and have negative numbers the logic will get screwed up as rank "5" will be greater than 99 for example and mess up your order.
Let me know if you have questions, the chart is then done on the table below. Pretty simple.
