Bar Chart Pls Help

[henrypuma]

Hi All
A Colleague is trying to create a Bar Chart in MS Excel, they would like to have it in order of the highest value working it's way down in value to the lowest, i.e. from left to right..I hope I am making myself clear, Any ideas please,

 

[xlhelp]

Why not sort the data in that order before plotting the chart?
"Amicule, num is sum qui mentiar tibi?"

 

[henrypuma]

The data on the chart came in this particular way, my colleague wants to edit/rearrange it in order of the highest down to the lowest... Is this possible?

 

[Zathras]

You can come close with an X-Y plot in conjunction with the RANK formula. It won't put bars, but it can put spots where the tops of the bars would go.

Set up a test workshet like this:

A2: 44
B2: 33
C2: 22
D2: 55
E2: 88
F2: 66
A3: =RANK(A2,$A$2:$F$2,0)
B3: =RANK(B2,$A$2:$F$2,0)
C3: =RANK(C2,$A$2:$F$2,0)
D3: =RANK(D2,$A$2:$F$2,0)
E3: =RANK(E2,$A$2:$F$2,0)
F3: =RANK(F2,$A$2:$F$2,0)

Use the chart wizard to create an X-Y chart, sub-type without lines.
Specify only a single series with the X and Y values:

X Values:

=Sheet1!$A$3:$F$3

Y Values:

=Sheet1!$A$2:$F$2

I think that's as close as you will come without following xlhelp's advice.

 

[xlbo]

Certainly in xl97, this is not possible (without sorting the data)
Can't speak for later versions as I don't have them Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[Zathras]

In thinking about this some more, I worked out a way for you to do exactly what you want with the addition of a few more formulas in the sheet:

First, assuming the labels of the data are in row 1 (as they were in my earlier post, but since they couldn't be used in the graph, I left them out):

A1: 'a
B1: 'b
C1: 'c
D1: 'd
E1: 'e
F1: 'f

[color]
Then three more rows of formulas:

A4: =MATCH(1,$A$3:$F$3,0)
B4: =MATCH(2,$A$3:$F$3,0)
C4: =MATCH(3,$A$3:$F$3,0)
D4: =MATCH(4,$A$3:$F$3,0)
E4: =MATCH(5,$A$3:$F$3,0)
F4: =MATCH(6,$A$3:$F$3,0)
A6: =INDEX($A$1:$F$1,0,A4)
B6: =INDEX($A$1:$F$1,0,B4)
C6: =INDEX($A$1:$F$1,0,C4)
D6: =INDEX($A$1:$F$1,0,D4)
E6: =INDEX($A$1:$F$1,0,E4)
F6: =INDEX($A$1:$F$1,0,F4)
A7: =INDEX($A$2:$F$2,0,A4)
B7: =INDEX($A$2:$F$2,0,B4)
C7: =INDEX($A$2:$F$2,0,C4)
D7: =INDEX($A$2:$F$2,0,D4)
E7: =INDEX($A$2:$F$2,0,E4)
F7: =INDEX($A$2:$F$2,0,F4)

This effectively puts the data from rows 1 and 2 into rows 6 and 7, sorted (descending) by the values from row 2.

Select cell A6 and click the chart wizard, then click Finish. (That's why I left row 5 blank -- to make the use of the chart wizard easier.)

Of course if your original data are in rows instead of columns, you will have to make adjustments, but the basic methode would be the same.

 

[Zathras]

(Sorry about that, I hit submit before preview.)

 

[xlbo]

Surely it would be easier to just sort the data ???
Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[xlhelp]

and..... the data that is plotted does not have to visible....You can copy and sort the data somewhere else on the sheet or another hseet. "Amicule, num is sum qui mentiar tibi?"

 

[henrypuma]

That all sounds very complicated, I think I will get my colleague to sort the data, thank you all for the help...