Automating changes in order of columns and/or stacks

My apologies for not expressing myself more clearly earlier. I hope what
follows makes my wish clearer.

I have a chart with nine columns. Some of those columns represent a value
from one cell only. Those are the ones I was calling "simple". Some of those
columns represent values from a number of cells. Those are the ones I was
calling "stacked".

To refer to my chart specifically, today's values for the five leftmost
columns are 11.9%, 3.5%, 2.9%, 3.3% and 4.7%. Each of those columns
represents a value from a single cell. Moving further to the right, the
total value shown for the next two columns is 7.2% and 7.7% respectively.
The value of 7.2% is derived from the values in four cells, one 3.7%, one
2.9%, one 0.4% and the last 0.3%. The value of 7.7% is derived from the
values in two cells, one 6.8% and the other 0.8%. (Obviously, there're some
rounding errors in the columns derived from more than one cell!). Next comes
a column with a value of 8.4%, derived from one cell only. Finally, there's
column whose total value is 50.4%, derived from the values in three cells,
42.8%, 6.4% and 1.2%.

What I want to achieve is a situation in which the place of a column in the
chart will be automatically changed if its value (or total value, as the
case may be) changes relative to another column or columns. For example, if
the two columns whose total values are 7.2% and 7.7% respectively change so
that the lower value one becomes the higher value one of the two, then the
chart will automatically switch their positions.

Is this capable of being done?

"Tushar Mehta" wrote in message
news:MPG.1b13404f27d3a1e39897cc@news-server...
I'm sorry, but I don't understand what it means to say that you want a
stacked column sorted with a simple column. A stacked column needs two
or more values. How does one rank this *set* of values against a
single value. For example, suppose I have

10
20
30 50 12

and the 10, 20, and 30 are in a stacked column, how does one sort that
*combination* of three numbers together with the two single values 50
and 12?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Tushar, you were of course right to say that the array formula worked as
intended. I wasn't clever enough to see that you were offering me the
formula I needed, rather than merely a formula to test what had gone wrong
with the formula which I had used. I have now used your array formula with
appropriate amendments in my worksheet.

That means, if I understand it correctly, that each of the three rows of
my worksheet which represents the stacks in a stacked column will be sorted
so that the stacks always go from largest at the x axis to smallest.

Now, the next question is whether it is possible to construct a formula
which will ensure that the seven columns between the leftmost and righmost
ones on the chart, whether stacked or simple, can be sorted so that they
will always appear in ascending order of value from left to right.

Any suggestions?

Here's how you can accomplish the task. Conceptually, pretend that all
you have are stacked columns.

Suppose the data are in G8:O11. I assumed that you can have only 4
values in a single stack, though the solution is easily extended to
include more items.

In G7 enter the formula =SUM(G8:G11). Copy G7 to H7:O7.

In G12:O12 enter the numbers 1 through 9.

In G14 enter the formula =INDEX($G$12:$O$12,RANK(G7,$G$7:$O$7)) Copy
G14 to H14:O14. Row 14 gives us the correct ranking of each stack. (*)

In G15, enter the formula =INDEX($G$7:$O$11,ROW()-ROW($G$15)+1,MATCH
(COLUMN()-COLUMN($G$15)+1,$G$14:$O$14,0)). Copy G15 to G16:G19. Copy
G15:G19 to H15:O15.

Format G15:O19 as %. If you don't want zeros to show, use a custom
format of 0.0%;(0.0%); [Note that there is a space after the last
semicolon.]

Plot G16:O19 in a stacked column chart. Use G15:O15 as the data label
(if so desired).

(*) This creates a descending chart. To create an ascending chart, the
formula in G14 should be =INDEX($G$12:$O$12,RANK(G7,$G$7:$O$7,1)) Copy
this new formula in G14 to H14:O14.

Voila. A self-sorting stacked column chart. Neat, even if I say so
myself. g

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
leslieunderscorekatz@agddotnswdotgovdotau says...
My apologies for not expressing myself more clearly earlier. I hope what
follows makes my wish clearer.

I have a chart with nine columns. Some of those columns represent a value
from one cell only. Those are the ones I was calling "simple". Some of those
columns represent values from a number of cells. Those are the ones I was
calling "stacked".

To refer to my chart specifically, today's values for the five leftmost
columns are 11.9%, 3.5%, 2.9%, 3.3% and 4.7%. Each of those columns
represents a value from a single cell. Moving further to the right, the
total value shown for the next two columns is 7.2% and 7.7% respectively.
The value of 7.2% is derived from the values in four cells, one 3.7%, one
2.9%, one 0.4% and the last 0.3%. The value of 7.7% is derived from the
values in two cells, one 6.8% and the other 0.8%. (Obviously, there're some
rounding errors in the columns derived from more than one cell!). Next comes
a column with a value of 8.4%, derived from one cell only. Finally, there's
column whose total value is 50.4%, derived from the values in three cells,
42.8%, 6.4% and 1.2%.

What I want to achieve is a situation in which the place of a column in the
chart will be automatically changed if its value (or total value, as the
case may be) changes relative to another column or columns. For example, if
the two columns whose total values are 7.2% and 7.7% respectively change so
that the lower value one becomes the higher value one of the two, then the
chart will automatically switch their positions.

Is this capable of being done?

"Tushar Mehta" wrote in message
news:MPG.1b13404f27d3a1e39897cc@news-server...
I'm sorry, but I don't understand what it means to say that you want a
stacked column sorted with a simple column. A stacked column needs two
or more values. How does one rank this *set* of values against a
single value. For example, suppose I have

10
20
30 50 12

and the 10, 20, and 30 are in a stacked column, how does one sort that
*combination* of three numbers together with the two single values 50
and 12?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Tushar, you were of course right to say that the array formula worked as
intended. I wasn't clever enough to see that you were offering me the
formula I needed, rather than merely a formula to test what had gone wrong
with the formula which I had used. I have now used your array formula with
appropriate amendments in my worksheet.

That means, if I understand it correctly, that each of the three rows of
my worksheet which represents the stacks in a stacked column will be sorted
so that the stacks always go from largest at the x axis to smallest.

Now, the next question is whether it is possible to construct a formula
which will ensure that the seven columns between the leftmost and righmost
ones on the chart, whether stacked or simple, can be sorted so that they
will always appear in ascending order of value from left to right.

Any suggestions?

Thanks very much, Tushar. I'll try to understand it and then use it, no
doubt with the usual stumbling on my part which will provoke yet further
queries by me.


"Tushar Mehta" wrote in message
news:MPG.1b149c5c72eb70009897d4@news-server...
Here's how you can accomplish the task. Conceptually, pretend that all
you have are stacked columns.

Suppose the data are in G8:O11. I assumed that you can have only 4
values in a single stack, though the solution is easily extended to
include more items.

In G7 enter the formula =SUM(G8:G11). Copy G7 to H7:O7.

In G12:O12 enter the numbers 1 through 9.

In G14 enter the formula =INDEX($G$12:$O$12,RANK(G7,$G$7:$O$7)) Copy
G14 to H14:O14. Row 14 gives us the correct ranking of each stack. (*)

In G15, enter the formula =INDEX($G$7:$O$11,ROW()-ROW($G$15)+1,MATCH
(COLUMN()-COLUMN($G$15)+1,$G$14:$O$14,0)). Copy G15 to G16:G19. Copy
G15:G19 to H15:O15.

Format G15:O19 as %. If you don't want zeros to show, use a custom
format of 0.0%;(0.0%); [Note that there is a space after the last
semicolon.]

Plot G16:O19 in a stacked column chart. Use G15:O15 as the data label
(if so desired).

(*) This creates a descending chart. To create an ascending chart, the
formula in G14 should be =INDEX($G$12:$O$12,RANK(G7,$G$7:$O$7,1)) Copy
this new formula in G14 to H14:O14.

Voila. A self-sorting stacked column chart. Neat, even if I say so
myself. g

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
leslieunderscorekatz@agddotnswdotgovdotau says...
My apologies for not expressing myself more clearly earlier. I hope what
follows makes my wish clearer.

I have a chart with nine columns. Some of those columns represent a
value
from one cell only. Those are the ones I was calling "simple". Some of
those
columns represent values from a number of cells. Those are the ones I
was
calling "stacked".

To refer to my chart specifically, today's values for the five leftmost
columns are 11.9%, 3.5%, 2.9%, 3.3% and 4.7%. Each of those columns
represents a value from a single cell. Moving further to the right, the
total value shown for the next two columns is 7.2% and 7.7%
respectively.
The value of 7.2% is derived from the values in four cells, one 3.7%,
one
2.9%, one 0.4% and the last 0.3%. The value of 7.7% is derived from the
values in two cells, one 6.8% and the other 0.8%. (Obviously, there're
some
rounding errors in the columns derived from more than one cell!). Next
comes
a column with a value of 8.4%, derived from one cell only. Finally,
there's
column whose total value is 50.4%, derived from the values in three
cells,
42.8%, 6.4% and 1.2%.

What I want to achieve is a situation in which the place of a column in
the
chart will be automatically changed if its value (or total value, as the
case may be) changes relative to another column or columns. For example,
if
the two columns whose total values are 7.2% and 7.7% respectively change
so
that the lower value one becomes the higher value one of the two, then
the
chart will automatically switch their positions.

Is this capable of being done?

"Tushar Mehta" wrote in
message
news:MPG.1b13404f27d3a1e39897cc@news-server...
I'm sorry, but I don't understand what it means to say that you want a
stacked column sorted with a simple column. A stacked column needs
two
or more values. How does one rank this *set* of values against a
single value. For example, suppose I have

10
20
30 50 12

and the 10, 20, and 30 are in a stacked column, how does one sort that
*combination* of three numbers together with the two single values 50
and 12?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Tushar, you were of course right to say that the array formula
worked as
intended. I wasn't clever enough to see that you were offering me the
formula I needed, rather than merely a formula to test what had gone
wrong
with the formula which I had used. I have now used your array formula
with
appropriate amendments in my worksheet.

That means, if I understand it correctly, that each of the three
rows of
my worksheet which represents the stacks in a stacked column will be
sorted
so that the stacks always go from largest at the x axis to smallest.

Now, the next question is whether it is possible to construct a
formula
which will ensure that the seven columns between the leftmost and
righmost
ones on the chart, whether stacked or simple, can be sorted so that they
will always appear in ascending order of value from left to right.

Any suggestions?

Tushar, may I please return to the matter of the array formula which you
gave me earlier, which ensured that the values from which the stacks in a
stacked column were derived would always appear in the relevant row of my
source worksheet in descending order from left to right?

It has since occurred to me that that outcome doesn't achieve all that I
want, which is that the stacked column concerned have its largest stack on
the x axis, with the rest of the stacks appearing in descending size as one
looks up the stacked column.

It doesn't do that because the series formula for each of the stacks
concerned hasn't had its plotting order changed. The stacks will therefore
not necessarily appear from largest to smallest, but in the same order they
did when I constructed the chart.

Is there some way that, as well as making the values appear in the relevant
row of the worksheet from largest to smallest, the series formulas of the
relevant stacks will be changed so that they're plotted from largest to
smallest?

"Tushar Mehta" wrote in message
news:MPG.1b149c5c72eb70009897d4@news-server...
Here's how you can accomplish the task. Conceptually, pretend that all
you have are stacked columns.

Suppose the data are in G8:O11. I assumed that you can have only 4
values in a single stack, though the solution is easily extended to
include more items.

In G7 enter the formula =SUM(G8:G11). Copy G7 to H7:O7.

In G12:O12 enter the numbers 1 through 9.

In G14 enter the formula =INDEX($G$12:$O$12,RANK(G7,$G$7:$O$7)) Copy
G14 to H14:O14. Row 14 gives us the correct ranking of each stack. (*)

In G15, enter the formula =INDEX($G$7:$O$11,ROW()-ROW($G$15)+1,MATCH
(COLUMN()-COLUMN($G$15)+1,$G$14:$O$14,0)). Copy G15 to G16:G19. Copy
G15:G19 to H15:O15.

Format G15:O19 as %. If you don't want zeros to show, use a custom
format of 0.0%;(0.0%); [Note that there is a space after the last
semicolon.]

Plot G16:O19 in a stacked column chart. Use G15:O15 as the data label
(if so desired).

(*) This creates a descending chart. To create an ascending chart, the
formula in G14 should be =INDEX($G$12:$O$12,RANK(G7,$G$7:$O$7,1)) Copy
this new formula in G14 to H14:O14.

Voila. A self-sorting stacked column chart. Neat, even if I say so
myself. g

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
leslieunderscorekatz@agddotnswdotgovdotau says...
My apologies for not expressing myself more clearly earlier. I hope what
follows makes my wish clearer.

I have a chart with nine columns. Some of those columns represent a
value
from one cell only. Those are the ones I was calling "simple". Some of
those
columns represent values from a number of cells. Those are the ones I
was
calling "stacked".

To refer to my chart specifically, today's values for the five leftmost
columns are 11.9%, 3.5%, 2.9%, 3.3% and 4.7%. Each of those columns
represents a value from a single cell. Moving further to the right, the
total value shown for the next two columns is 7.2% and 7.7%
respectively.
The value of 7.2% is derived from the values in four cells, one 3.7%,
one
2.9%, one 0.4% and the last 0.3%. The value of 7.7% is derived from the
values in two cells, one 6.8% and the other 0.8%. (Obviously, there're
some
rounding errors in the columns derived from more than one cell!). Next
comes
a column with a value of 8.4%, derived from one cell only. Finally,
there's
column whose total value is 50.4%, derived from the values in three
cells,
42.8%, 6.4% and 1.2%.

What I want to achieve is a situation in which the place of a column in
the
chart will be automatically changed if its value (or total value, as the
case may be) changes relative to another column or columns. For example,
if
the two columns whose total values are 7.2% and 7.7% respectively change
so
that the lower value one becomes the higher value one of the two, then
the
chart will automatically switch their positions.

Is this capable of being done?

"Tushar Mehta" wrote in
message
news:MPG.1b13404f27d3a1e39897cc@news-server...
I'm sorry, but I don't understand what it means to say that you want a
stacked column sorted with a simple column. A stacked column needs
two
or more values. How does one rank this *set* of values against a
single value. For example, suppose I have

10
20
30 50 12

and the 10, 20, and 30 are in a stacked column, how does one sort that
*combination* of three numbers together with the two single values 50
and 12?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Tushar, you were of course right to say that the array formula
worked as
intended. I wasn't clever enough to see that you were offering me the
formula I needed, rather than merely a formula to test what had gone
wrong
with the formula which I had used. I have now used your array formula
with
appropriate amendments in my worksheet.

That means, if I understand it correctly, that each of the three
rows of
my worksheet which represents the stacks in a stacked column will be
sorted
so that the stacks always go from largest at the x axis to smallest.

Now, the next question is whether it is possible to construct a
formula
which will ensure that the seven columns between the leftmost and
righmost
ones on the chart, whether stacked or simple, can be sorted so that they
will always appear in ascending order of value from left to right.

Any suggestions?