Automatically changing references to last 12 values when new value is added

**itregub** · 06-23-2009, 05:30 PM

I have a chart that sums up the last 12 months of data. At the end of each new month, a new row of data is added and the last row is taken out of accounting (though is not deleted). Right now I'm manually changing the summation argument to sum the last 12 months of data. Is there a macro or formula I could write for the chart, which would switch down each time a new value is added? (For example, once a month elapses, going from A1:A12 to A2:A13). Thanks for any insight!

- IT

**deadlyduck** · 06-23-2009, 05:48 PM

Try this formula:

Please Login or Register  to view this content.

I'm assuming that you've a single cell in which the running total is displayed- the formula above should sum the most recent 12 values. It also requires that you've at least 12 entries to total- less than that and it will probably generate an error.

**DonkeyOte** · 06-23-2009, 06:04 PM

Assuming no blanks interspersed amongst values...first value in Row 1

Using INDEX (non-Volatile)

=SUM(INDEX(A:A,MAX(1,COUNT(A:A)-11)):INDEX(A:A,MAX(12,COUNT(A:A))))

Using OFFSET (Volatile like INDIRECT)

=SUM(OFFSET(A1,MAX(0,COUNT(A:A)-12),,12,1))

both should handle any number of values in A (ie above / below 12)

**broro183** · 06-23-2009, 06:26 PM

hi Itregub,

You have been given a couple of solutions already but here are some links to Jon Peltier's site which may give you even more ideas of what's possible:
http://peltiertech.com/Excel/Charts/DynamicLast12.html
from
http://peltiertech.com/Excel/Charts/Dynamics.html
or for even more possibilities check out his other pages:
http://peltiertech.com/Excel/Charts/index.html

hth
Rob