Bar graphs in pedro

[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

[yurttas]

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.

.

[J2000k]

Ok that is the basic idea for whatever reason people call it pedro..anyway how is that option embedded into the excel sheet with that pull down option from normal to ascending

[teylyn]

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!

[yurttas]

Hi, teylyn.

Thanks for your very nice explanation.

.

[Andy Pope]

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

[J2000k]

Thanks to both of you now I just have to try it and see if this works i guess a simple click to make this chart is out the window.

[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

Hi, Andy Pope

What about this...



.

[Andy Pope]

That appears to work.

This is my version.

[J2000k]

So far setting up the chart in ascending order i got that now setting up the drop down arrow so i can toggle from normal to ascending is the next hurdle. If we refer to back to chart 22 for the first set of data A1 B6 what is the exact procedure to add the pull down menu

[Andy Pope]

The cell uses Data validation, list.

Use the menu, Data > Validation...

[J2000k]

Ok so I selected Data->Validation and selected Input Message and typed in List. Then clicked over to the Settings tab and highlighted the entire column with only the numbers values inside for the source data. This however only made a drop down of all the values i just highlighted. The goal here is to create it so i can select ascending or descending from that drop down menu and change the order and have it displayed and functioning on the chart.

[Andy Pope]

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