New to board...How to select # of cells immediately prior to
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: New to board...How to select # of cells immediately prior to

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have an spread sheet that contains sales information in columns representing monthly sales. The manager would like the last four months to be averaged into a fifth field. On the fifth month, she inserts a new column between last month and the 4 month average. She wants the average to automatically recalculate the average of the new month plus the prior three months. How can this be done?
    ie. a1=January, b1=February, c1=March, d1=April, e1=Average sales, she inserts May into e1 and average sales becomes f1. She then wants f1 to automatically update using b1 through e1..and so on each month.
    Is this possible? I am a novice on this board and writing this type of formula. Any help is greatly appreciated. I do not know how to write macros either.
    THANKS for any input!

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    HI
    Just change the formula in F1
    =AVERAGE(A1:D1)
    To
    =AVERAGE(A1:E1)
    Copy it
    Paste it down the column
    Tom

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks, but the manager does not want anyone to have to change the formula...Currently, if you add in a column between D1 and E1, the formula does change to =average(a1:e1)(5 months), but I need to know if there is a way that when the new column is added in, the formula would automatically change to =average(b1:e1)(4 months), and the following month change to =average(c1:f1). Basically, I think I have to link the range (4 cells prior to the average) to the formula, not the absolute cell name. Does that make more sense? Thank you for any input given.

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    okay, had to play a little but this does work....

    In this example there are values in cells a1 to d1 that need to be averaged.

    This equation for the sum is in cell e1, and will move to f1 when the column is inserted...

    =AVERAGE(INDIRECT("A1:" & ADDRESS(1,COLUMN(E1)-1)))

    Try it and see!

    In the equation A1 refers to start of range, 1 refers to the row number that this is all happening in, and column(E1)-1 returns the column number for the cell just to the left of the equation cell.

    Good Luck!

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-04-03 11:14, kac918 wrote:
    Thanks, but the manager does not want anyone to have to change the formula...Currently, if you add in a column between D1 and E1, the formula does change to =average(a1:e1)(5 months), but I need to know if there is a way that when the new column is added in, the formula would automatically change to =average(b1:e1)(4 months), and the following month change to =average(c1:f1). Basically, I think I have to link the range (4 cells prior to the average) to the formula, not the absolute cell name. Does that make more sense? Thank you for any input given.
    Enter the formula...

    =AVERAGE(OFFSET(F1,,-4,,4))

    ...into cell F1.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com