Results 1 to 9 of 9

Thread: summing Data

  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    I need to place a formula in N13, that will add the values 12 columns to the right of X13 and the X number of columns to the right based on the Value in cell N6 -1

    A new column is inserted each month where column X is, so I thought that the offset or Index function would be most appropriate, but not sure how to use this in this instance

    See sample data using sum formula

    Your assistance will be most appreciated
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I am not sure I understand? Could you be a little more specific. I think that the SUM with the OFFSET function based on 12 and the N6 value, will be what you need but I am not sure exactly what you are after to provide the formula.

    Steve

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by sdckapr View Post
    I am not sure I understand? Could you be a little more specific. I think that the SUM with the OFFSET function based on 12 and the N6 value, will be what you need but I am not sure exactly what you are after to provide the formula.

    Steve
    Hi Steve

    In this example I would like to sum start summing 11 columns to the right of X13 (not 12 columns as per my previous posting i.e. AI13.Al13 (i.e an additional 4 columns to the right of AI13-the additional columns to the right of AI13 is determined by the Value in N6)

    Each month an additional column will be inserted in Column X, so the formula will always sum 11 columns to right of X13

    Your assistance will be most appreciated

    Regards

    Howard

  4. #4
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by sdckapr View Post
    I am not sure I understand? Could you be a little more specific. I think that the SUM with the OFFSET function based on 12 and the N6 value, will be what you need but I am not sure exactly what you are after to provide the formula.

    Steve

    Hi Steve

    I have tried to give you a clearer explanation below, so ignore previous comments

    I need some help with a Formula

    The financial Period runs from Oct to Sept for each Year

    The current years data is contained in Columns R, Z, AA, & AB.

    Each Month a new column will be inserted where column Z is, and the data from column R (10/01-Which is Jan 2010) will then be copied into column Z). The new months data for eg Feb 10/02 will then be imported into column R

    I new formula that will add the previous periods data i.e the previous years data i.e these appear currently in columns AK:AN-these will change each time a new column is inserted each month

    The number of month Traded is depicted in N6

    Your assistance will most appreciated

    Regards

    Howard
    Attached Files Attached Files

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You seem to keep changing the offset. This formula seems to work:
    =SUM(OFFSET(X13,0,13,1,N6))

    It sums the range starting 0 rows and 13 columns away from X13 (= AK13). The size of the range is 1 row and the number of columns in N6 (=4 in your example). Thus it sums the range AK13:AN13.

    Is this what you are after?

    Steve

  6. #6
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,164
    Thanks
    47
    Thanked 976 Times in 906 Posts
    I think this is what you want. (Kudos to Steve for the idea.)
    =SUM(OFFSET(AI13,0,0,1,N6))

    cheers, Paul

  7. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Paul

    Thanks for the help.

    This formula works perfectly.

    What does the 1 before N6 represent in the formula =SUM(OFFSET(AI13,0,0,1,N6)) ?

    Regards

    Howard

  8. #8
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,164
    Thanks
    47
    Thanked 976 Times in 906 Posts
    How it works
    The range is calculated using AI13 as the starting point.
    Offset 0 colums to begin the range.
    Offset 0 rows to begin the range.
    Range height (number of rows) = 1
    Range length (number of columns) = N6

    cheers, Paul

  9. #9
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Paul

    Thanks for the explanation

    Regards

    Howard

Posting Permissions

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