Automating changes in order of columns and/or stacks

[Leslie]

After much time by me and assistance by people in this newsgroup, I am
almost finished what I confidently expect to be the only chart I'll ever
produce in my lifetime. Only one matter remains to be done, if possible.

The chart displays six simple columns and three stacked ones. I've arranged
the columns so that the seven columns on the right, whether simple or
stacked, are shown in ascending order of value from left to right. I've
arranged the three stacked columns so that the stack with the biggest value
is at the bottom of each column and the values of the stacks decrease as one
moves up the column.

Is there any way to have the chart automatically rearrange itself in the
above fashion if a change in value of either a stack, an entire column or
both warrants it?

If there is, will it still work if there's displayed above each column the
value data label of an invisible stack equal in value to the value of the
entire visible column?

 

[Tushar Mehta]

Why make this your last chart? You seem to be getting the hang of
things...

In any case, the below addresses 9 data points arranged as 3 stacked
items on the left and 6 clustered columns on the right.

Suppose the three stacked values are in A1:A3. Then, in B1 enter
=LARGE($A$1:$A$3,ROW()). Copy B1 to B2:B3.

Suppose the six clustered values are in D16. Then, in E2 -- note,
that is E2 -- enter =LARGE($D$1:$D$6,ROW()-1). Copy E2 to E3:E7.

Plot B1:B3 in a stacked chart. In step 2 of the wizard, ensure that
'data in rows' is checked.

Now, select E1:E7 -- note E1 is the starting cell, not E2. Drag the
selection onto the chart. In the dialog box that pops up, ensure that
'new series' is selected, 'Series name in first row' is *not* selected,
and 'data in columns' is selected.

Select the first data point of the 2nd series. To do so, in the chart
click on the clustered column. Pause. Use the right arrow key to
select the first point (it will be on top of the stacked column). Add
a data label from the cell that contains the total of the stacked
column. For more on how, see the Excel/Tutorials/Data Labels page of
my web site.


--
Regards,

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

 

[Leslie]

Tushar,

Thanks very much for your response. It will obviously take me some time to
try to absorb what you say, but I'll certainly do my best. I'll also go to
the relevant page of your website right now to look at the page you mention.
Thanks again.

 

[Leslie]

Tushar's message yesterday alerted me to the existence of the LARGE and ROW
worksheet functions. Afterwards, I found the COLUMN function too.

I thought I would make a start at understanding his first formula. I played
with it and also used COLUMN in it instead of ROW. Ultimately, I did the
following:

1. I put simple numbers in cells A12 to A14.

2. I filled cells A1 to A3 with =A12 to =A14 respectively. In cells B1 to
B3, I put his formula. Those three cells then returned my three numbers in
cells A12 to A14, from largest to smallest.

3. I filled cells A6 to C6 with =A12 to =A14 respectively. In cells A7 to
C7, I put his formula with necessary amendments, including using COLUMN
instead of ROW.

4. Everything worked perfectly. Each time I changed the numbers in A12 to
A14, cells B1 to B3 and A7 to C7 showed the new numbers, from largest to
smallest. I also changed A12 to A14 from simple numbers to percentages to
one decimal place. That worked too.

I then went to my worksheet which is the immediate source of my chart.

In the worksheet, I have a number of rows with multiple cells in them. Each
such row represents those columns in my chart which are stacked. The first
such row has only two cells filled (apart from a cell with a category label
in it). I copied the contents of those two cells to a blank area of the
worksheet. I also made sure that the formatting of those two new cells
equalled that of the original two cells, percentages to one decimal place. I
then replaced the contents of the original two cells by Tushar's first
formula, suitably amended. It said something like =LARGE($G$16:$H$17,
COLUMN()).

It didn't work. Both of the cells with the formula in them showed the #NUM
error.

Is it obvious to anyone reading this that I've made some fundamental error?
If so, I'd be grateful to be told what it is.

 

[Leslie]

A minute ago, I sent a message. At one point in it, I referred to
$G$16:$H$17. I meant to say $G$16:$H$16.

 

[Tushar Mehta]

Thanks for your detailed explanation. My suggestions, on the other
hand, are short. If the 'numbers' that you have are actually entered
as text values, the LARGE function will return #NUM! In some empty
cell, enter =ISNUMBER(G16). What do you get?

The #NUM! result can also be because you are asking for the n-th
largest number where n is greater than the number of entries in the
array. With data in G16:H16, select 2 cells in one row and enter the
array formula =LARGE(G16:H16, COLUMN(G16:H16)-COLUMN($G$16)+1)

What the COLUMN(G16:H16) above does is create a vector of 2 values
{7,8}. Then, -COLUMN($G$16)+1 subtracts 7 and adds 1 from each of the
values to yield {1,2}. Finally, LARGE(G16:H16.{1,2}) yields the
desired result.

An array formula is entered with CTRL+SHIFT+ENTER rather than just
ENTER.

--
Regards,

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

 

[Leslie]

Thanks for your detailed explanation. My suggestions, on the other
hand, are short. If the 'numbers' that you have are actually entered
as text values, the LARGE function will return #NUM! In some empty
cell, enter =ISNUMBER(G16). What do you get?

The #NUM! result can also be because you are asking for the n-th
largest number where n is greater than the number of entries in the
array. With data in G16:H16, select 2 cells in one row and enter the
array formula =LARGE(G16:H16, COLUMN(G16:H16)-COLUMN($G$16)+1)

What the COLUMN(G16:H16) above does is create a vector of 2 values
{7,8}. Then, -COLUMN($G$16)+1 subtracts 7 and adds 1 from each of the
values to yield {1,2}. Finally, LARGE(G16:H16.{1,2}) yields the
desired result.

An array formula is entered with CTRL+SHIFT+ENTER rather than just
ENTER.

--
Regards,

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

 

[Leslie]

Apologies for an empty message yesterday. I was having problems which first caused the empty message and secondly meant I couldn't continue with the message

A very brief recap: cells G7 and H7 in the relevant worksheet contain formulas which return values from two cells in another worksheet, 6.8% and 0.8% respectively. I copied the formulas from those cells to G18 and H18 (not 16, as I mistakenly said earlier). I then changed the formulas in G7 and H7 to both say =LARGE($G$18:$H$18,COLUMN()). Each then returned #NUM

Tushar asked me to try =ISNUMBER(G18) and =ISNUMBER(H18). Each formula returned the value TRUE

Tushar asked me next to try an array formula which I won't repeat. For G18, it returned 0.06820049; for H18, it returned 0.8%. When I reformatted appropriately the cell which returned the plain number, it changed to 6.8%

If I understand what's happening, those two tests didn't disclose the reason why I got the two #NUMs

I then tried evaluating the formula I'd put into G7 and H7. When I started the evaluation in respect of each cell, I got my formula with COLUMN() underlined. The next evaluation changed COLUMN() into an italicised 7 for G7 and an italicised 8 for H7. The final evaluation shown in each case was #NUM

Where do I go from here

 

[Tushar Mehta]

Actually, the array formula that returned 0.068 and 0.8 worked as
intended. Didn't it? The reason for the #NUM was that you were trying
(with the old formula) to calculate the 7th and 8th largest values from
a list of 2 values (in G7:H7).

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2004

 

[Leslie]

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 Mehta]

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

 

[Leslie]

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?

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

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.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.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.

 

[Tushar Mehta]

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

 

[Leslie]

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.

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

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?

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. 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. 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.

 

[Leslie]

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?

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

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?

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. 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. 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.