Write an excel 'sum' formula for a certain # of columns
 

I am trying to build a rolling spreadsheet based on days. I want to have a
total for the trailing 30 days, but keep all the information that is older
than 30 days. If I lock the formula, and add new days it keeps the original
30 day calculation, instead of adding on 1 new day and dropping off the
oldest day of the 30.

Write an excel 'sum' formula for a certain # of columns
 

On Sat, 30 Jan 2010 13:11:02 -0800, ctante
wrote:

I am trying to build a rolling spreadsheet based on days. I want to have a
total for the trailing 30 days, but keep all the information that is older
than 30 days. If I lock the formula, and add new days it keeps the original
30 day calculation, instead of adding on 1 new day and dropping off the
oldest day of the 30.


Please give some more information about the layout of your
spreadsheet.
Is there one row per day, or one column per day?
Do you have data for every day, or can there be gaps in the dates?
If there are gaps, what do you mean by trailing 30 days?
Is it the 30 last dates, or all dates higher than the highest date
minus 30 days?

Lars-Åke

Write an excel 'sum' formula for a certain # of columns
 

Hmm,

What's a 'trailing' day? I think your saying you want to sum the last 30
entries of data. This sums the last 30 entries in column A and is dynamic so
as new entries are added it stills sums the last 30

=SUM(OFFSET(A1,COUNTA(A:A)-30,):OFFSET(A1,COUNTA(A:A),))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"ctante" wrote:

I am trying to build a rolling spreadsheet based on days. I want to have a
total for the trailing 30 days, but keep all the information that is older
than 30 days. If I lock the formula, and add new days it keeps the original
30 day calculation, instead of adding on 1 new day and dropping off the
oldest day of the 30.