Sum with offset

Topic

New Reply

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

Reply | Quote

Replies

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

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?

Reply | Quote

Apologies:

It should be :
if AL16, 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.

Reply | Quote

Could you please describe EXACTLY, UNAMBIGUOUSLY and CLEARLY what you want? “Pick the values” is very vague.

Reply | Quote

Could you please describe EXACTLY, UNAMBIGUOUSLY and CLEARLY what you want? “Pick the values” is very vague.

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.

Reply | Quote

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))

Reply | Quote

Exactly as required.

Thank you very much Hans.

Reply | Quote