Results 1 to 6 of 6
Thread: Cumulative values chart (XP)

20030324, 11:28 #1
 Join Date
 May 2002
 Location
 Bristol, Avon, England
 Posts
 110
 Thanks
 0
 Thanked 0 Times in 0 Posts
Cumulative values chart (XP)
Please see attached example
I have some source data (yellow section) which contains some values each month. These values can fall into 3 different categories A, B or C.
The pink section then uses a SUMIF to find the totals for each category.
The green section then finds the cumulative total for each category. This is the source data for the chart.
Is there any way I can get the same chart straight from the source data (yellow section)?
The attached sheet is just sample data the actual sheet contains many more line items and categories and it is a tedious task to do each month.

20030324, 15:01 #2
 Join Date
 Dec 2001
 Location
 Br. Columbia, Canada
 Posts
 28
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Cumulative values chart (XP)
Have you thought of using a pivot table with calculated fields to accumulate the monthly amounts?
It does not matter if you add new categories as these are picked up automatically. All you have to do is to update you chart source data.

20030324, 15:14 #3
 Join Date
 May 2002
 Location
 Bristol, Avon, England
 Posts
 110
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Cumulative values chart (XP)
I had thought of it but...... I can't work out how to do the calculation to do the cumulation on the pivot table. I just get the sum for each month rather than a cumulative total.
Any ideas?

20030324, 15:34 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Cumulative values chart (XP)
You'd have to set up your data this way (only partly shown):
<table border=1><td align=center>Category</td><td align=center>Month</td><td align=center>Amount</td><td>A</td><td>Jan</td><td align=right>5</td><td>A</td><td>Feb</td><td align=right>72</td><td>A</td><td>Mar</td><td align=right>66</td><td>A</td><td>Apr</td><td align=right>86</td></table>
Then, set up a pivot table based on this table. Put Month in the row header and Category in the column header (or vice versa), and Amount in the Data part. It'll become SumOfAmount. Double click SumOfAmount, then click Options. In the dropdown list, select Cumulative Total In, then select Month as base field. That should do it.

20030324, 15:42 #5
 Join Date
 Dec 2001
 Location
 Br. Columbia, Canada
 Posts
 28
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Cumulative values chart (XP)
Start with a very simple table with Category as the rows and Jan as the data. Right click in the table, select formulas and calculated field. In the Insert Calculated Field dialog box, enter Feb and two spaces as the Name. (You could also name this field Feb and add a period. The important thing is that it does not use the Feb field name.)Tab to the formula box and back space to remove the zero. Select Jan in the fields box and click the insert field bar. Insert a + and then select Feb, insert the field and then OK. You now have a cumulative field for Jan and Feb. Once you have established the Feb cumulative, you can go through the same process using the Feb cumulative name and adding Mar to get Mar cumulative and so one.

20030324, 16:15 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Cumulative values chart (XP)
<P ID="edit" class=small>(Edited by sdckapr on 24Mar03 12:15. Added ARRAY option to get rid of Totals )</P>Another option is to make your formulas more "copyable" so that you don't need to edit them for differences and changes. Instead of multiple formulas, it can be done with 2 formulas: 1 from SUMIF and one for cumulative, thus much easier editing and updates.
1) Create a named range for your datafield (eg MyRange =Sheet1!$A$3:$M$11)
2) Instead of listing the "lookup item" with a " tot" attached, just place the lookup item for reference. So A14, A15, A6, would be A, B, and C, repectively.
3) In B14 enter the formula:
<pre>=SUMIF(INDEX(MyRange,0,1),$A14,INDEX(MyRange, 0,COLUMN()))</pre>
This can now be copied throughout the range B14:M16 (and beyond with more categories in column A)
4) In B19 enter the formula:
<pre>=SUM($B14:B14)</pre>
This can be copied in B19:M21 (and beyond)
With more data, MyRange can change size. The formulas will not NEED to be updated, since the range takes care of that. If more categories are added, just insert a row in each of the calcs and copy the formulas
It could be automated if desired, but it will save time if you don't frequently add categories.
Steve
Another option to NOT use the totals only the cumulative totals:
1) Change A19, A20, A21 to A, B, C (get rid of the " cum")
2) Enter into B19 the ARRAY formula (confirm with ctrlshiftenter):
<pre>=SUM(IF((INDEX(MyRange,0,1)=$A19)*(COLUMN(MyR ange)<=COLUMN()),MyRange))</pre>
Then copy this through B19:M21. This does NOT require the "totals" to calculate but calcs directly from the data.
Steve