Bar graphs in pedro

Originally Posted by J2000k

Does anyone know how to take the bar graph chart and put them in order from lowest to highest values? Is there a way to change the order in an existing bar graph or does a formula have to be entered

Sample file attached.

.

How this works:

Your normal data is in A1:B6. This range has the range name "Normal"

Your sorted data is in A9:B14. This range is called "Ascending"

The source data for the chart is in A17:B22.

Above the chart, there is a cell with data validation, type List and allowed values are "Normal" and "Ascending". These values are also the range names from above.

The data table for the chart is filled with an formula that looks at H4, which displays either "Normal" or "Ascending". The formula then converts that text into a cell address with the use of the INDIRECT function.

=INDIRECT(H4)

The result of this function is the range that is named in H4.

By selecting A17 to B22, entering =INDIRECT(H4) in A17 and confirming with CTRL-SHIFT-ENTER, the individual cells of the range named in H4 will be distributed to the selected cells. This is called an array formula.

May take a while to get used to. Array formulas can cause major wonderment and headache, but once you understand them, they're quite handy in certain situations.

Without array formulas, the data table could be filled with something like

=IF($H$4="Normal",A1,A9)

copied down and one to the right.

If you want to use this technique with charts that display lots of different variations, the IF statement will near its capacity with seven nested IFs (in Excel 2003), but with the array approach, you can have a virtually unlimited number of data sources that can be beamed into the data table for the chart. Just give it a name and add it to the validation list.

Good work, Yurttas!

Just beware that the SMALL and INDEX does not handle repeated values well.

e.g. If all the Points are 100 all the Names are A

That appears to work.

This is my version.

The cell uses Data validation, list.

Use the menu, Data > Validation...

First you need to create a list of descending positions.
Formula for F3 and copied down.

F3: =MATCH(LARGE($D$3:$D$12,ROW()-ROW($E$2)),$D$3:$D$12,0)

Then you need to make a new table of values next to the Ascending table.

Copy A18:B29 to D18:E29

change the formula in D20 and E20

D20: =INDEX($A$3:$B$12,$F3,1)
E20: =INDEX($A$3:$B$12,$F3,2)

Add a new named range
DESCENDING: =Sayfa1!$D$18:$E$29

Select H4 and add Descending to the List.

source: Normal,Ascending,Descending