I apologize - after replying I realized that I mispoke when I said "The
reference to 10 is based on the number of rows of data. If you had 20 items
that you needed to chart, change the 10 in the formulas to 20." This
reference is only needed to break the tie in case two of the numbers are the
same. In reality it does not need to change.
Also, the array formula that sorts the data could be made much shorter and
easier to understand. I'll post the example on my site tomorrow.
----
Regards,
John Mansfield
http://www.pdbook.com
"BuriedInSpreadSheets" wrote:
THANK YOU! This was a HUGE help!
"John Mansfield" wrote:
You can achieve an automatic sort for the bar chart via the use of array
formulas. For example, assume you start with the following information in
cells A1:B11:
Sales Data
Item 1 4
Item 2 8
Item 3 7
Item 4 3
Item 5 6
Item 6 5
Item 7 2
Item 8 4
Item 9 6
Item 10 5
Set up a second range in columns D and E. Add the following formula in cell
D2 as a single cell array (do so by pressing Control-Shift-Enter at the same
time):
=INDEX(A$2:A$11,MATCH(LARGE($B$2:$B$11-ROW($B$2:$B$11)/10^10,ROWS($A$2:A2)),$B$2:$B$11-ROW($B$2:$B$11)/10^10,0))
Copy the formula down the range D2:D11.
Add the following formula in cell E2 as a single cell array:
=INDEX(B$2:B$11,MATCH(LARGE($B$2:$B$11-ROW($B$2:$B$11)/10^10,ROWS($A$2:B2)),$B$2:$B$11-ROW($B$2:$B$11)/10^10,0))
Copy the formula down the range E2:E11.
The formulas will sort the original data high-to-low:
Sales Data
Item 2 8
Item 3 7
Item 5 6
Item 9 6
Item 6 5
Item 10 5
Item 1 4
Item 8 4
Item 4 3
Item 7 2
The reference to 10 is based on the number of rows of data. If you had 20
items that you needed to chart, chnage the 10 in the formulas to 20.
Base your bar chart on the second range of data. Any time you make a change
to the first range, the second range will be automatically sorted high-to-low
by the formulas.
You can find more information on this technique he
http://www.pdbook.com/index.php/exce...rmulas_part_1/
http://www.pdbook.com/index.php/exce...rmulas_part_2/
http://www.pdbook.com/index.php/exce...rmulas_part_3/
----
Regards,
John Mansfield
http://www.pdbook.com
"BuriedInSpreadSheets" wrote:
Every day I update a spreadsheet for our daily sales figures. Associated to
that spreadsheet are several different charts including some bar charts. Is
there a way to auto-sort bars within a bar chart from the lowest number to
the highest or vice-versa without having to manually change the order of the
data series?