[blujein]

I am currently working on a spreadsheet for a complex budget, and the spreadsheet is constantly expanding by way of additional columns (a new column for each day).

At the end of the columns that represent each day, there are five columns that show calculations based on the entries in the columns that are before them. One of those columns calculates the average of every entry in that row. In the sixth column, we want to be able to calculate the average of the last four day entries without having to manually adjust the range each time we expand the spreadsheet.

That's probably not particularly clear, so in summary, we have a column for every day of the last twelve months. Each cell shows a dollar amount. At the end of each row, we have five columns that display various calculations for each row. At the end of those calculations, we want a further calculation that will show us only the previous four days.

Needless to say, every time we add a column for a new day, the range (showing the average of the previous four days) expands to show the average for the last five days. Rather than this happening, we need the range to move forward, rather than expanding, essentially following the cell at the end of the row that shows the average of those four days.

In essence, we need a cell to calculate the average of four cells that are five cells behind it, without ever expanding its field of reference.

Apologies if this description isn't particularly helpful...it's a difficult scenario to explain. If anyone can help with this, it'd be greatly appreciated.

[Zack Barresse]

Hi there,

Can you provide some detail about how your data is setup? Will it always be the five cells to the left of it? Is there any time where there are not 5 cells behind it?

Just a matter of curiosity on my part, it generally isn't too efficient to add data to a spreadsheet like you're doing. Generally the data goes down, as we have more rows than we do columns. Which leads me to another question about your data, what version are you using? In 97-2003 we have 65536, but in 2007 we have 1M+ rows. Columns get larger too, in 97-2003 we have 256, but in 2007 we have 16k+.

Perhaps you can give us a small sample of your data set? If you can't upload a sample file (preferrably your actual file), can you re-create a sample? Worst case scenario just describe it in as much detail as possible (a few scenarios of data).

[blujein]

Thanks for the quick reply, mate!

Yes, there will always be five cells between the cells we wish to obtain the average for, and the cell where we will actually display said average.

As for the software, we're running Microsoft Office 2007. The way the spreadsheet is configured is to record the year - 357 days, so showing it in columns is easier for us given we have graphs and charts below the data.

I've uploaded a small example of what I'm talking about. Just to do it quickly, I haven't filled-in all of the five columns between the columns representing the days and the column representing the last 4-day average (I didn't think it'd matter).

As you will be able to see, the cells in red show the average for the last four cells under the date headings. The formula, though, showing that four-day range has been entered manually and would need to be changed every time a new day was added before the Average field. I would like the cells in red to always know to count the last four columns of the row under the date headings.

I hope that makes sense, and thank you for your help.

[Zack Barresse]

Gotcha. Try this...

=AVERAGE(OFFSET(L2,0,-8):OFFSET(L2,0,-5))

HTH

[blujein]

Originally Posted by Zack Barresse

Gotcha. Try this...

=AVERAGE(OFFSET(L2,0,-8):OFFSET(L2,0,-5))

HTH

Brilliant! Thank you so much for your help!

[Zack Barresse]

You're very welcome.

If that works for you, can you please mark your thread as Solved?

[blujein]

Done. Thank you again.