Results 1 to 7 of 7

Thread: Sum with offset

  1. #1
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Having problem in sum the values using offset. Pl have a look in attachment.

    if AL1>=6, then

    1. AJ4 should be sum of column B to C
    2. AK4 should be sum of col.O:T
    3. AL4 should pick the values from B onwards
    4. AM4 should pick the values from O4 onwards

    if AL1<6, then

    1. AJ4 should be sum of col. V to AA
    2. AK4 should be sum of col. AC:AH
    3. AL4 should pick the values from V4 onwards
    4. AM4 should pick the values from AC4 onwards


    TIA
    Attached Files Attached Files
    Regards
    Prasad

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='prasad' post='798302' date='15-Oct-2009 23:57']Having problem in sum the values using offset. Pl have a look in attachment.

    if AL1>=6, then

    1. AJ4 should be sum of column B to C[/quote]

    It appears that you may be over-complicating this just a bit. And, I'm not real sure I understand the question.
    Using your current data and assuming that AL1 is 6, what result would you have expected for cell AJ4?
    You've stated that AJ4 should be the sum of column B to C.

    B to C is not much of a range! Do you want all the numbers in columns B and C summed, or just B4:C4?
    A little clarification would be helpful and perhaps the expected results for AJ4 thru AM4.
    On your current sheet, is the result in AK4 what you expected it to be?

    - Ricky

  3. #3
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Apologies:

    It should be :
    if AL1<=6, then

    1. AJ4 should be sum of column B4 to G4
    2. AK4 should be sum of col.O4:T4
    3. AL4 should pick the values from B4 onwards
    4. AM4 should pick the values from O4 onwards

    if AL1>6, then

    1. AJ4 should be sum of col. V4 to AA4
    2. AK4 should be sum of col. AC4:AH4
    3. AL4 should pick the values from V4 onwards
    4. AM4 should pick the values from AC4 onwards


    Pl go through the attachement once again & see the results if AL1>6.
    Regards
    Prasad

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Could you please describe EXACTLY, UNAMBIGUOUSLY and CLEARLY what you want? "Pick the values" is very vague.

  5. #5
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='798324' date='16-Oct-2009 14:56']Could you please describe EXACTLY, UNAMBIGUOUSLY and CLEARLY what you want? "Pick the values" is very vague.[/quote]

    DATA:
    AL1 represents the number of months i.e. 1 for April,2 for May...12 for March.

    B4 to B4 represent the monthwise budget from April to Sept. & V4 to AA4 represent the budget from Oct. to March.

    O4 to T4 represent the Actuals from April to Sept. & AC4 to AH4 represent the Actuals from Oct. to March.

    RESULTS REQUIRED:

    AJ4 is sum of budget from April to the month represent the AL1. For April to Sep., it should sum the value from B4 to G4. From Oct onwards, the values should be sum of B4:G4 + V4:AA4. For example, if AL1 is 1, AJ4 should be 1.00, being the budget of April. If AL1 is 2, AJ4 should be sum of B4:C4 i.e. 3.00. If AL1 is 7, AJ4 should be 31.00 i.e. sum of B4:G4 +V4.

    Similarly, AK4 is sum of Actuals from April to the months represents the AL1. From April to Sep., it should be sum of O4:T4. From Oct. onwards, the values should be sum of O4:T4 + AC4:AH4.

    AL4 represent the budget of that particular month.

    AM4 represent the Actual for the month.

    Hope this will help to understand what I am trying to achieve.
    Regards
    Prasad

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Your original description didn't even come near this!

    If AL1 is greater than 6, you must subtract 6 from it to calculate the offsets.

    In AJ4:
    =IF($AL$1<=6,SUM(OFFSET($B4,0,0,1,$AL$1)),SUM($B4: $G4,OFFSET($V4,0,0,1,$AL$1-6)))

    In AK4:
    =IF($AL$1<=6,SUM(OFFSET($O4,0,0,1,$AL$1)),SUM($O4: $T4,OFFSET($AC4,0,0,1,$AL$1-6)))

    In AL4:
    =IF($AL$1<=6,OFFSET($A4,0,$AL$1),OFFSET($U4,0,$AL$ 1-6))

    In AM4:
    =IF($AL$1<=6,OFFSET($N4,0,$AL$1),OFFSET($AB4,0,$AL $1-6))

  7. #7
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Exactly as required.

    Thank you very much Hans.
    Regards
    Prasad

Posting Permissions

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