Cumulative values chart (XP)

[buck46]

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.

[MikefromCanmore]

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.

[buck46]

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?

[HansV]

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.

[MikefromCanmore]

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.

[sdckapr]

<P ID="edit" class=small>(Edited by sdckapr on 24-Mar-03 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 ctrl-shift-enter):
<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