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 » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Charting the Sum of Two, or More, Series of Data



 
 
Thread Tools Display Modes
  #1  
Old April 20th, 2010, 06:06 PM posted to microsoft.public.excel.charting,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
mj
external usenet poster
 
Posts: 258
Default Charting the Sum of Two, or More, Series of Data

I know there must be a relatively simple solution to my challenge, short of
creating a new series od data which is the sum of desired series.

Let's say that I have the following

02/13/2010 02/20/2010 02/27/2010 ...
0-30 $7,072,987 $5,990,448 $8,978,002
31-60 177,472 160,460 142,547
61-90 24,976 25,644 2,413

Rather than tracking two series (0-30 and 31-60) as two separate series on
my graph, I would like to track a NEW series called 0-60 which is the sum of
these two series:

02/13/2010 02/20/2010 02/27/2010 ...
0-60 $7,250,459 $6,150,950 $9,120,549
61-90 24,976 25,644 2,413

So how can I do this simply within the chart, without needing to do all of
the summation separately?

--

MJ
Ads
  #3  
Old April 20th, 2010, 07:43 PM posted to microsoft.public.excel.charting,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
mj
external usenet poster
 
Posts: 258
Default Charting the Sum of Two, or More, Series of Data

Don,

Had not thought about that one, but I did try it. While might be a dark
horse possibility lacking a different solution for the sum of the series
group, due to the volume ot tracking points and the size of the graph it
looses a lot in the rendering. I even looked at the stacked area and line
graphs.

This is one of those times that a single line graph is the most productive
representation.

If you, or anyone else, has a solution on how to come up with a Series
values formulas what will sum the series values, that is what I am looking
for.

Thank you for your suggestion.

--

MJ


"Don Guillett" wrote:

How about a stacked bar chart

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MJ" wrote in message
...
I know there must be a relatively simple solution to my challenge, short of
creating a new series od data which is the sum of desired series.

Let's say that I have the following

02/13/2010 02/20/2010 02/27/2010 ...
0-30 $7,072,987 $5,990,448 $8,978,002
31-60 177,472 160,460 142,547
61-90 24,976 25,644 2,413

Rather than tracking two series (0-30 and 31-60) as two separate series on
my graph, I would like to track a NEW series called 0-60 which is the sum
of
these two series:

02/13/2010 02/20/2010 02/27/2010 ...
0-60 $7,250,459 $6,150,950 $9,120,549
61-90 24,976 25,644 2,413

So how can I do this simply within the chart, without needing to do all of
the summation separately?

--

MJ


.

  #4  
Old April 20th, 2010, 08:00 PM posted to microsoft.public.excel.charting,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
trip_to_tokyo[_3_]
external usenet poster
 
Posts: 932
Default Charting the Sum of Two, or More, Series of Data

EXCEL 2007

Hi MJ.

I have just pu up a file for you at:-

http://www.pierrefondes.com/

Item number 97.

I have created a Pivot Table and Pivot Chart from the data that you provided.

You can toggle on / off what's in the chart by using the drop down in F2.

For your 0 to 60 day period I created a Calculated Item (as you can see).

Hope this helps you out.

If my comments have helped please hit Yes.

Thanks.





"MJ" wrote:

I know there must be a relatively simple solution to my challenge, short of
creating a new series od data which is the sum of desired series.

Let's say that I have the following

02/13/2010 02/20/2010 02/27/2010 ...
0-30 $7,072,987 $5,990,448 $8,978,002
31-60 177,472 160,460 142,547
61-90 24,976 25,644 2,413

Rather than tracking two series (0-30 and 31-60) as two separate series on
my graph, I would like to track a NEW series called 0-60 which is the sum of
these two series:

02/13/2010 02/20/2010 02/27/2010 ...
0-60 $7,250,459 $6,150,950 $9,120,549
61-90 24,976 25,644 2,413

So how can I do this simply within the chart, without needing to do all of
the summation separately?

--

MJ

  #5  
Old April 21st, 2010, 03:57 PM posted to microsoft.public.excel.charting,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
mj
external usenet poster
 
Posts: 258
Default Charting the Sum of Two, or More, Series of Data

Pierre,

I like your approach to my problem and I was looking at a pivot tablechart
as a possibility, but would require a major restucturing of our current
report format that our leadership is not prepared for at this time. I may
yet need to lead them down that path in the future, but right now they want a
simple brute force solution.

Thank you for your efforts and I will keep it handy as an example for the
future.

--

MJ


"trip_to_tokyo" wrote:

EXCEL 2007

Hi MJ.

I have just pu up a file for you at:-

http://www.pierrefondes.com/

Item number 97.

I have created a Pivot Table and Pivot Chart from the data that you provided.

You can toggle on / off what's in the chart by using the drop down in F2.

For your 0 to 60 day period I created a Calculated Item (as you can see).

Hope this helps you out.

If my comments have helped please hit Yes.

Thanks.





"MJ" wrote:

I know there must be a relatively simple solution to my challenge, short of
creating a new series od data which is the sum of desired series.

Let's say that I have the following

02/13/2010 02/20/2010 02/27/2010 ...
0-30 $7,072,987 $5,990,448 $8,978,002
31-60 177,472 160,460 142,547
61-90 24,976 25,644 2,413

Rather than tracking two series (0-30 and 31-60) as two separate series on
my graph, I would like to track a NEW series called 0-60 which is the sum of
these two series:

02/13/2010 02/20/2010 02/27/2010 ...
0-60 $7,250,459 $6,150,950 $9,120,549
61-90 24,976 25,644 2,413

So how can I do this simply within the chart, without needing to do all of
the summation separately?

--

MJ

  #6  
Old April 21st, 2010, 04:08 PM posted to microsoft.public.excel.charting,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
mj
external usenet poster
 
Posts: 258
Default Charting the Sum of Two, or More, Series of Data

Point of clarification... our current report format tracks datasets spanning
24 months. The sample data in original questions is only a small subset.

* Due to the amount of tracked data, while the stacked barchart might work
in a fashion, it looses so much in translation that it is not viable for this
application.

What I am looking for is...

Is there a way within the 'Select Data Source', 'Series' to identify the
desired series as a resulting summation of two, or more, series WITHOUT
having to actually create such a dataset/series to reference in the data
source selection.

Someone out there has to have this answer. Thank you in advance for your
time and efforts.

--

MJ


"MJ" wrote:

I know there must be a relatively simple solution to my challenge, short of
creating a new series od data which is the sum of desired series.

Let's say that I have the following

02/13/2010 02/20/2010 02/27/2010 ...
0-30 $7,072,987 $5,990,448 $8,978,002
31-60 177,472 160,460 142,547
61-90 24,976 25,644 2,413

Rather than tracking two series (0-30 and 31-60) as two separate series on
my graph, I would like to track a NEW series called 0-60 which is the sum of
these two series:

02/13/2010 02/20/2010 02/27/2010 ...
0-60 $7,250,459 $6,150,950 $9,120,549
61-90 24,976 25,644 2,413

So how can I do this simply within the chart, without needing to do all of
the summation separately?

--

MJ

  #7  
Old April 22nd, 2010, 05:06 PM posted to microsoft.public.excel.charting,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
Jon Peltier[_2_]
external usenet poster
 
Posts: 386
Default Charting the Sum of Two, or More, Series of Data

Excel charts chart data from Excel cells. There may be tricks using
named formulas, but these are difficult to get right and prone to
maintenance issues.

What is so terrible about putting the totals into the worksheet? You
could put them somewhere outside the print range if you don't want to
clutter up the worksheet.

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/


On 4/21/2010 11:08 AM, MJ wrote:
Point of clarification... our current report format tracks datasets spanning
24 months. The sample data in original questions is only a small subset.

* Due to the amount of tracked data, while the stacked barchart might work
in a fashion, it looses so much in translation that it is not viable for this
application.

What I am looking for is...

Is there a way within the 'Select Data Source', 'Series' to identify the
desired series as a resulting summation of two, or more, series WITHOUT
having to actually create such a dataset/series to reference in the data
source selection.

Someone out there has to have this answer. Thank you in advance for your
time and efforts.

 




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 08:44 PM.


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