Auto-Sort Chart
This example allows a fixed range for inputs, automatically sorts the results, and charts those results. The input area and chart source look like this:
The formulas in column D calculate a very small number that is subtracted from the original. The purpose is to prevent possible duplication of entries. Below is the formula in cell D4.
=C4-ROW()/10^10
The formulas in column F sort the data in column D from highest to lowest value. Below is the formula in cell F4.
=LARGE($D$4:$D$13,ROWS($B$4:B4))
Finally, the formulas in column E utilize the INDEX and MATCH worksheet functions to perfrom a left-sided look-up on the data in column F. Below is the formula in cell E4.
=INDEX($B$4:$B$13,MATCH($F4,$D$4:$D$13,0))
The chart below provides the end-result.
Reference: An explanation of the LARGE sorting array formula was obtained from John Walkenbach's book titled Excel 2003 Formulas. The INDEX and MATCH functions are also explained in this book as well as at Debra Dalgleish's Contextures site.
