dynamic year to date formula

[snax500]

I previously asked for help with this:

In Excel2000, I would like to create a dynamic year-to-date formula. I
have the following data starting in row 3 and going from column A to
column X:


Jan Actuals Jan Budget Feb Actuals Feb Budget
100 200 300 400


I would like to use cell A1 as an input cell for the month ( ex. Feb =
2). I would like the formula to lookup the month and then tell me how
much Feb YTD Actuals are ( in this example = 400 ). All I want to
change each month is the input cell in A1 and have the formula
automatically give me the results.


Someone responded with this formula:

=SUMPRODUCT(--(MOD(COLUMN(OFFSET($A$2,,,,CHOOSE($A$10,1,3,5,7,9,11,13,15,17,19,21,23))),2)=1),OFFSET($A$2,,,,CHOOSE($A$10,1,3,5,7,9,11,13,15,17,19,21,23)))

Can someone plaes explain this formula. It works perfectly but I don't
understand it.

Thanks

 

[Frank Kabel]

Hi
maybe easier to understand
=HLOOKUP(TEXT(--("2004-" & $A$2 & "-1"),"MMM") & " Actuals"),A2:X3,2,0)

 

[snax500]

I am not sure about this one as well. How is this lookup formula going
to add up year to date amounts.

 

[Max]

I am not sure about this one as well. How is this lookup formula going
to add up year to date amounts.

Think Frank might have mis-understood your original post

What he gave* was a lookup to the Actuals cell value in row3
for the month # input in A1,
while what you wanted was to sum the YTD Actuals
for the month # input in A1 ..

*formula below, corrected to refer to cell A1,
and with the extra closing parens (typo) after " Actuals" removed:
=HLOOKUP(TEXT(--("2004-" & $A$1 & "-1"),"MMM") & " Actuals",A2:X3,2,0)

Going back to the original formula which worked for you,
viz. the expression below,
with some slight corrections made to the cell references:
(think there were some "typos" in the formula as posted)

=SUMPRODUCT(--(MOD(COLUMN(OFFSET($A$3,,,,CHOOSE($A$1,1,3,5,7,9,11,13,15,17,1
9,21,23))),2)=1),OFFSET($A$3,,,,CHOOSE($A$1,1,3,5,7,9,11,13,15,17,19,21,23))
)

Here's one attempt to explain what happens / how it works ..

If A1 contains say: 2 (for Feb)

CHOOSE($A$1,1,3,5,7,9,11,13,15,17,19,21,23)
will evaluate to: 3, i.e. it'll pick the 2nd number in the series:
1,3,5,7...23
The series is designed to suit your layout of: Actuals, Budget, Actuals,
Budget ...
in the cols A:X, where all the Actuals are in "odd" numbered columns

OFFSET($A$3,,,,CHOOSE($A$1,1,3,5,7,9,11,13,15,17,19,21,23))
will then return an array of width 3 from the "anchor" reference cell $A$3
on the same row, i.e. it'll return the array: {100,200,300}

The CHOOSE(...) is used as a width param within the OFFSET(...) above,
re OFFSET's syntax: OFFSET(reference,rows, cols,height,width)

COLUMN(OFFSET($A$3,,,,CHOOSE($A$1,1,3,5,7,9,11,13,15,17,19,21,23)))
will return a horizontal array: {1,2,3}

MOD(COLUMN(OFFSET($A$3,,,,CHOOSE($A$1,1,3,5,7,9,11,13,15,17,19,21,23))),2)=1
will return an array: {TRUE,FALSE,TRUE}

The double negatives "--" in: --(MOD(...))
will coerce the {TRUE,FALSE,TRUE} to become: {1,0,1}

So the expression:

=SUMPRODUCT(--(MOD(COLUMN(OFFSET($A$3,,,,CHOOSE($A$1,1,3,5,7,9,11,13,15,17,1
9,21,23))),2)=1),OFFSET($A$3,,,,CHOOSE($A$1,1,3,5,7,9,11,13,15,17,19,21,23))
)

will ultimately resolve to 2 arrays within SUMPRODUCT, i.e. to:
=SUMPRODUCT({1,0,1},{100,200,300})

SUMPRODUCT({1,0,1},{100,200,300})
will then evaluate the product of the 2 arrays as:
= 1x100 + 0x200 + 1x300
= 100 + 0 + 300
= 400 (i.e. the YTD fig for Feb Actuals)

So, if A1 contains, say: 3, it'll finally resolve to:
SUMPRODUCT({1,0,1,0,1},{100,200,300,400,500})
[ assuming the value for Mar Actulas in E3 is 500 ]

which returns:
= 1x100 + 0x200 + 1x300 + 0x400 + 1x500
= 100 + 0 + 300 + 0 + 500
= 900 (i.e. the YTD fig for Mar Actuals)

The zeros in the array ({1,0,1,0,1} will ensure that
the alternating Budget values are excluded in the summation

 

[snax500]

thanks so much for the in-depth explanation. It is exactly what I was
looking for.

I am not sure about this one as well. How is this lookup formula going
to add up year to date amounts.

Think Frank might have mis-understood your original post

What he gave* was a lookup to the Actuals cell value in row3
for the month # input in A1,
while what you wanted was to sum the YTD Actuals
for the month # input in A1 ..

*formula below, corrected to refer to cell A1,
and with the extra closing parens (typo) after " Actuals" removed:
=HLOOKUP(TEXT(--("2004-" & $A$1 & "-1"),"MMM") & " Actuals",A2:X3,2,0)

Going back to the original formula which worked for you,
viz. the expression below,
with some slight corrections made to the cell references:
(think there were some "typos" in the formula as posted)

=SUMPRODUCT(--(MOD(COLUMN(OFFSET($A$3,,,,CHOOSE($A$1,1,3,5,7,9,11,13,15,17,19,21,23))),2)=1),OFFSET($A$3,,,,CHOOSE($A$1,1,3,5,7,9,11,13,15,17,19,21,23))
)

Here's one attempt to explain what happens / how it works ..

If A1 contains say: 2 (for Feb)

CHOOSE($A$1,1,3,5,7,9,11,13,15,17,19,21,23)
will evaluate to: 3, i.e. it'll pick the 2nd number in the series:
1,3,5,7...23
The series is designed to suit your layout of: Actuals, Budget, Actuals,
Budget ...
in the cols A:X, where all the Actuals are in "odd" numbered columns

OFFSET($A$3,,,,CHOOSE($A$1,1,3,5,7,9,11,13,15,17,19,21,23))
will then return an array of width 3 from the "anchor" reference cell $A$3
on the same row, i.e. it'll return the array: {100,200,300}

The CHOOSE(...) is used as a width param within the OFFSET(...) above,
re OFFSET's syntax: OFFSET(reference,rows, cols,height,width)

COLUMN(OFFSET($A$3,,,,CHOOSE($A$1,1,3,5,7,9,11,13,15,17,19,21,23)))
will return a horizontal array: {1,2,3}

MOD(COLUMN(OFFSET($A$3,,,,CHOOSE($A$1,1,3,5,7,9,11,13,15,17,19,21,23))),2)=1
will return an array: {TRUE,FALSE,TRUE}

The double negatives "--" in: --(MOD(...))
will coerce the {TRUE,FALSE,TRUE} to become: {1,0,1}

So the expression:

=SUMPRODUCT(--(MOD(COLUMN(OFFSET($A$3,,,,CHOOSE($A$1,1,3,5,7,9,11,13,15,17,19,21,23))),2)=1),OFFSET($A$3,,,,CHOOSE($A$1,1,3,5,7,9,11,13,15,17,19,21,23))
)

will ultimately resolve to 2 arrays within SUMPRODUCT, i.e. to:
=SUMPRODUCT({1,0,1},{100,200,300})

SUMPRODUCT({1,0,1},{100,200,300})
will then evaluate the product of the 2 arrays as:
= 1x100 + 0x200 + 1x300
= 100 + 0 + 300
= 400 (i.e. the YTD fig for Feb Actuals)

So, if A1 contains, say: 3, it'll finally resolve to:
SUMPRODUCT({1,0,1,0,1},{100,200,300,400,500})
[ assuming the value for Mar Actulas in E3 is 500 ]

which returns:
= 1x100 + 0x200 + 1x300 + 0x400 + 1x500
= 100 + 0 + 300 + 0 + 500
= 900 (i.e. the YTD fig for Mar Actuals)

The zeros in the array ({1,0,1,0,1} will ensure that
the alternating Budget values are excluded in the summation

 

[Max]

thanks so much for the in-depth explanation.
It is exactly what I was looking for ..

You're welcome !