Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 2
Default Create Macro to Increase Cell Reference

First post here and I thank everyone in advanced that will help out!

I've searched for this all day without any luck. Maybe I'm just overlooking it, or just really don't understand.

Anyways, I have an Excel sheet called "Snapshot" that has averages from a sheet called "Raw Data (I&R)". So, here is what I need some help with. I would like to create a Macro with either a keyboard shortcut or upon opening the doucment (it really doesn't matter) so that the "cell" will change using the macro. Notice the formula below, it's pulling the average from column "B" cell position "B177", so tomorrow, when I go back in, by using the macro, it would increase the cell position to "B178"so on and so on.

Example of the formula:
=AVERAGE('Raw Data (I&R)'!B2:B177)

Hopefully I explained this well enough.

Thanks,
Nick
  #2   Report Post  
Senior Member
 
Location: Philippines
Posts: 161
Wink

Quote:
Originally Posted by Darkh0st View Post
First post here and I thank everyone in advanced that will help out!

I've searched for this all day without any luck. Maybe I'm just overlooking it, or just really don't understand.

Anyways, I have an Excel sheet called "Snapshot" that has averages from a sheet called "Raw Data (I&R)". So, here is what I need some help with. I would like to create a Macro with either a keyboard shortcut or upon opening the doucment (it really doesn't matter) so that the "cell" will change using the macro. Notice the formula below, it's pulling the average from column "B" cell position "B177", so tomorrow, when I go back in, by using the macro, it would increase the cell position to "B178"so on and so on.

Example of the formula:
=AVERAGE('Raw Data (I&R)'!B2:B177)

Hopefully I explained this well enough.

Thanks,
Nick
No need to use a macro in this one. You can use the formula:

=AVERAGE(OFFSET('Raw Data (I&R)'!$B$2,0,0,COUNTA('Raw Data (I&R)'!$B:$B)-1,1))

The formula will adjust the range automatically.
__________________
Asobi Wa Owari Da
  #3   Report Post  
Junior Member
 
Posts: 2
Default

Quote:
Originally Posted by wickedchew View Post
No need to use a macro in this one. You can use the formula:

=AVERAGE(OFFSET('Raw Data (I&R)'!$B$2,0,0,COUNTA('Raw Data (I&R)'!$B:$B)-1,1))

The formula will adjust the range automatically.
I really appreciate your response Wickedchew as it does work. However, it's averaging the entire column of data, and I would like it to average the past 30 days of data and shift everyday. Is this possible? Thanks again!
  #4   Report Post  
Senior Member
 
Location: Philippines
Posts: 161
Default

Quote:
Originally Posted by Darkh0st View Post
I really appreciate your response Wickedchew as it does work. However, it's averaging the entire column of data, and I would like it to average the past 30 days of data and shift everyday. Is this possible? Thanks again!
Let's say your table has the following information:

Column B has all the dates
Column C has all the data
Cell A1 has the start date to be averaged
Cell A2 has the end date to be averaged

=AVERAGE(OFFSET(C1,MATCH(A1,B:B,0)-1,0,(A2-A1)+1,1))

If you want it just plain 30 days from the start date:

=AVERAGE(OFFSET(C1,MATCH(A1,B:B,0)-1,0,30,1))
__________________
Asobi Wa Owari Da
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I increase a cell reference in VLOOKUP by 11 when copying? John R. Excel Discussion (Misc queries) 2 October 19th 09 09:48 PM
Increase Cell reference by 1 for every step akoobra Excel Discussion (Misc queries) 3 May 13th 09 12:25 PM
How to Create Macro to Increase Cell Value to one and Print Worksheet [email protected] Excel Worksheet Functions 4 July 24th 08 08:48 PM
HOW DO COPY A CELL BUT INCREASE ITS REFERENCE BY TWO CELL POSITIO. Crusty Excel Discussion (Misc queries) 1 May 29th 07 05:59 PM
increase a cell reference by increments greater than one... Gary Fuller Excel Worksheet Functions 2 January 14th 05 07:51 PM


All times are GMT +1. The time now is 09:42 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"