Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
a simple 'sum' formula is not adding all of the data in range | Excel Discussion (Misc queries) | |||
how to put 'SUM' formula in VB after picking up certain names and their bonus | Excel Discussion (Misc queries) | |||
'SUM' Tables | Excel Worksheet Functions | |||
How do I write a VBA code in excel that will compare two columns . | Excel Worksheet Functions | |||
How can I write vertically in some columns on an Excel spreadshee. | Charts and Charting in Excel |