A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Ineffective change of plot order in series formula



 
 
Thread Tools Display Modes
  #1  
Old May 27th, 2004, 05:29 PM
Jon Peltier
external usenet poster
 
Posts: n/a
Default Ineffective change of plot order in series formula

Hi Leslie -

Are you using a clustered column chart or a stacked column chart? Or a
combination chart?

When I change the plot order, each series retains its formatting. If the
series is moved earlier in the plot order, it moves lower in the stack
or further to the left in the cluster. The legend entry moves accordingly.

Note that in combination charts, each chart type has its own layer it
stays within (and the same section of the legend). Area series are
always behind other types, Scatter series are always in front, and the
others lie at intermediate places. You can only rearrange like series
within their level (and legend section).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Leslie wrote:

I am trying to take advantage of the ability to change the plot order
of a series in a chart which has multiple series.

To test that ability, I opened a new workbook and filled in sheet 1
as follows: I put a-c in A2:A4; x-z in B11; and 1-9 in B24.

I then used the chart wizard to create charts which: were clustered
or stacked; had their series in rows or columns; and were embedded in
sheet 1 or appeared in chart 1.

In any of the permutations just mentioned, selecting a series and
then changing its plot order by editing the series formula which
appeared in the formula bar caused the series concerned to switch
places with one or both of the other series, just as expected.

Then I switched to my "real" chart, which is on a separate
chartsheet.

It has some columns consisting of a single series and some consisting
of multiple series.

When I tried to switch the place of series in a multiple series
column by editing their plot orders in their series formulas, all
worked as expected.

However, when I tried by the same method to switch the places of two
single series columns, although the plot orders were changed, the
places of the columns weren't.

They remained as before.

In case it is thought relevant, I add that the columns I tried to
switch were both plotted against the same axes.

Was the outcome I've just described inevitable (perhaps a consequence
of the fact that the chart has both single series and multiple series
columns?) or is there something which can be done to make the editing
of the plot orders of the single series columns effective?


  #2  
Old May 28th, 2004, 03:32 AM
Tushar Mehta
external usenet poster
 
Posts: n/a
Default Ineffective change of plot order in series formula

Hi Leslie,

If this is a continuation of the self-sorting problem with stacked and
non-stacked columns, here's a solution that will sort each column left-
to-right with the largest ones on the left. In addition, for any
columns that are stacked, the rows are sorted in descending order, so
that the largest values are at the bottom of the chart.

Suppose the raw data are in G7:O10:

G H I J K L M N O
7 11.90%3.50% 2.90% 3.30% 4.70% 2.90% 6.80% 8.40% 6.40%
8 3.70% 0.80% 42.80%
9 0.30% 1.20%
10 0.40%

[Hopefully, the above alignment will work out]

Then, first we sort the data in each column.

In G14, enter =IF(ISNA(INDEX(G$7:G$10,RANK(G7,G$7:G$10))),"",IND EX(G
$7:G$10,RANK(G7,G$7:G$10))). Copy G14 to G15:G17. Copy G14:G17 to
H14:O14.

Next, we get the sum of each colum. In G13, enter the formula =SUM
(G14:G17). Copy G13 to H13:O13.

Next we sort the data based on the total of each column. This is
basically what I posted some time back.

In G19:O19 enter the numbers 1,2,...8,9
In G20 enter the formula =INDEX($G$19:$O$19,RANK(G13,$G$13:$O$13,0))
Copy G20 to H20:O20.

In G21 enter the formula =INDEX($G$13:$O$17,ROW()-ROW($G$21)+1,MATCH
(COLUMN()-COLUMN($G$21)+1,$G$20:$O$20,0)) Copy G21 to G22:G25. Copy
G21:G25 to H21:O21.

Plot G22:O25. Use G21:O21 as labels for the top series.

Self sorting column chart. Largest columns to the left. In each
column self sorting stacks. Largest values at the bottom.

--
Regards,

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

In article ,
says...
Jon,

The chart I'm concerned with has columns only. Six of them are based on one series only, so I've described those as "simple" columns. The remaining three are based on either two, three or four series, so I've described those as "stacked" columns. The chart type I selected in the chart wizard was the "stacked columns" subtype. However, I arranged the data in the source worksheet in an unusual staggered fashion

which permitted the mixing of simple and stacked columns in the one chart. I discovered that method from others. You may have been one of those others or, if not, you at least have in the past drawn people's attention to the method and pointed them to other sources for it.

Two more things about the chart are that: first, the extreme righthand column, which is one of my three "stacked" ones, is plotted against a secondary y axis; and, secondly, to be able to show data labels at the top of all of my columns, including the "stacked" ones, I created invisible columns above the visible ones and of the same value as the visible ones and put data labels inside the bases of those

invisible columns.

When I try to manipulate one of my "stacked" columns on the primary y axis by changing the plot orders of the series formulas of the series involved, the stacks jump around within the column just as desired.

However, when I try to manipulate my leftmost "simple" column on the primary y axis by changing the plot order of its series formula from 1 to 2, the change in number shows up (and the next column's plot order, that column also being a "simple" column, changes to 1), but the columns stand still.

Perhaps the chart has some peculiarity of construction which prevents me from switching simple columns by changing plot orders of their series formulas. If so, I'll give up on the idea of automating movement of columns or parts of columns in the chart.

Leslie

  #3  
Old May 28th, 2004, 03:04 PM
Jon Peltier
external usenet poster
 
Posts: n/a
Default Ineffective change of plot order in series formula

Leslie -

The columns will not reposition themselves over a different category
label just by changing the plot order. You would have to somehow
rearrange or sort the data so the data moved within its worksheet range.

I didn't read Tushar's explanation in detail, but his formulas probably
do the rearranging for you.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Leslie: wrote:

Jon,

The chart I'm concerned with has columns only. Six of them are based
on one series only, so I've described those as "simple" columns. The
remaining three are based on either two, three or four series, so
I've described those as "stacked" columns. The chart type I selected
in the chart wizard was the "stacked columns" subtype. However, I
arranged the data in the source worksheet in an unusual staggered
fashion which permitted the mixing of simple and stacked columns in
the one chart. I discovered that method from others. You may have
been one of those others or, if not, you at least have in the past
drawn people's attention to the method and pointed them to other
sources for it.

Two more things about the chart are that: first, the extreme
righthand column, which is one of my three "stacked" ones, is plotted
against a secondary y axis; and, secondly, to be able to show data
labels at the top of all of my columns, including the "stacked" ones,
I created invisible columns above the visible ones and of the same
value as the visible ones and put data labels inside the bases of
those invisible columns.

When I try to manipulate one of my "stacked" columns on the primary y
axis by changing the plot orders of the series formulas of the series
involved, the stacks jump around within the column just as desired.

However, when I try to manipulate my leftmost "simple" column on the
primary y axis by changing the plot order of its series formula from
1 to 2, the change in number shows up (and the next column's plot
order, that column also being a "simple" column, changes to 1), but
the columns stand still.

Perhaps the chart has some peculiarity of construction which prevents
me from switching simple columns by changing plot orders of their
series formulas. If so, I'll give up on the idea of automating
movement of columns or parts of columns in the chart.

Leslie


  #4  
Old May 30th, 2004, 02:15 AM
Leslie
external usenet poster
 
Posts: n/a
Default Ineffective change of plot order in series formula

I'm writing this in response both to Tushar's and Jon's latest posts.

By way of preface, I mention I'm always very anxious to find as soon as
possible a reply to any question I post in these newsgroups, although I'm
aware that any answerers are unlikely to feel the same way about further
posts of mine! However, I do feel guilty if I don't reply immediately, if
only to express thanks for the assistance I'm always getting. This time I
plead guilty with an explanation (two, actually): computer problems and a
(human) virus.

I know that I'm a fool who rushed in where angels feared to tread, but once
I discovered that there was in VBA something called the PlotOrder property,
which seemed tailor-made to produce the automatic switching of series I was
interested in, I thought that using it (if only I could figure out how) must
be better than using such complex combinations of worksheet functions as
were obviously required in an attempt to achieve the same result. Whether
directly or indirectly, I've been told I'm wrong by everyone who's answered
any of my questions on the topic and I think the time must now have come to
give up on the idea.

When my head is clear enough and assuming my computer is truly working
properly again, I'll knuckle down to trying to adapt Tushar's latest
formulas to my precise situation.

With thanks,

Leslie

"Jon Peltier" wrote in message
...
Leslie -

The columns will not reposition themselves over a different category
label just by changing the plot order. You would have to somehow
rearrange or sort the data so the data moved within its worksheet range.

I didn't read Tushar's explanation in detail, but his formulas probably
do the rearranging for you.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Leslie: wrote:

Jon,

The chart I'm concerned with has columns only. Six of them are based
on one series only, so I've described those as "simple" columns. The
remaining three are based on either two, three or four series, so
I've described those as "stacked" columns. The chart type I selected
in the chart wizard was the "stacked columns" subtype. However, I
arranged the data in the source worksheet in an unusual staggered
fashion which permitted the mixing of simple and stacked columns in
the one chart. I discovered that method from others. You may have
been one of those others or, if not, you at least have in the past
drawn people's attention to the method and pointed them to other
sources for it.

Two more things about the chart are that: first, the extreme
righthand column, which is one of my three "stacked" ones, is plotted
against a secondary y axis; and, secondly, to be able to show data
labels at the top of all of my columns, including the "stacked" ones,
I created invisible columns above the visible ones and of the same
value as the visible ones and put data labels inside the bases of
those invisible columns.

When I try to manipulate one of my "stacked" columns on the primary y
axis by changing the plot orders of the series formulas of the series
involved, the stacks jump around within the column just as desired.

However, when I try to manipulate my leftmost "simple" column on the
primary y axis by changing the plot order of its series formula from
1 to 2, the change in number shows up (and the next column's plot
order, that column also being a "simple" column, changes to 1), but
the columns stand still.

Perhaps the chart has some peculiarity of construction which prevents
me from switching simple columns by changing plot orders of their
series formulas. If so, I'll give up on the idea of automating
movement of columns or parts of columns in the chart.

Leslie




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:34 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.