There's no such thing as a stupid question, but they're the easiest to answer.
JoinTour
Login
Search
Business Applications
Tag Cloud
access acer asus bios bsod computer crash dns drive driver drivers error ethernet excel freeze games gaming graphics hard drive hardware hdmi internet java laptop malware memory monitor motherboard network printer problem ram random registry router slow software sound trojan usb video virus vista wifi windows windows 7 windows 7 32 bit windows 7 64 bit windows xp wireless
Search
Search for:
Tech Support Guy Forums > Software & Hardware > Business Applications >
Solved: Relative Cell Ranges In An Expanding Spreadsheet (Microsoft Office Excel)

Reply  
Thread Tools
blujein's Avatar
Member with 153 posts.
 
Join Date: May 2008
Location: Australia
Experience: Intermediate
05-Nov-2009, 10:52 PM #1
Question Solved: Relative Cell Ranges In An Expanding Spreadsheet (Microsoft Office Excel)
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's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
05-Nov-2009, 11:27 PM #2
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's Avatar
Member with 153 posts.
 
Join Date: May 2008
Location: Australia
Experience: Intermediate
06-Nov-2009, 12:16 AM #3
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.
Attached Files
File Type: xls DemonstrationFile.xls (22.0 KB, 41 views)
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
06-Nov-2009, 12:27 AM #4
Gotcha. Try this...
Code:
=AVERAGE(OFFSET(L2,0,-8):OFFSET(L2,0,-5))
HTH
blujein's Avatar
Member with 153 posts.
 
Join Date: May 2008
Location: Australia
Experience: Intermediate
06-Nov-2009, 12:32 AM #5
Quote:
Originally Posted by Zack Barresse View Post
Gotcha. Try this...
Code:
=AVERAGE(OFFSET(L2,0,-8):OFFSET(L2,0,-5))
HTH
Brilliant! Thank you so much for your help!
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
06-Nov-2009, 12:43 AM #6
You're very welcome.

If that works for you, can you please mark your thread as Solved?
blujein's Avatar
Member with 153 posts.
 
Join Date: May 2008
Location: Australia
Experience: Intermediate
17-Nov-2009, 12:18 AM #7
Done. Thank you again.
Reply

THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
WELCOME TO TECH SUPPORT GUY! Are you looking for the solution to your computer problem? Join our site today to ask your question -- for free! Our site is run completely by volunteers who want to help you solve your computer problems. See our Welcome Guide to get started.
Thread Tools



Facebook Facebook Twitter Twitter TechGuy.tv TechGuy.tv Mobile TSG Mobile
You Are Using:
Server ID
Advertisements do not imply our endorsement of that product or service.
All times are GMT -4. The time now is 01:38 PM.
Copyright © 1996 - 2011 TechGuy, Inc. All rights reserved.

Powered by Cermak Technologies, Inc.