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,,,,CHOOS E($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

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

"snax500" wrote:

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,,,,CHOOS E($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

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

"snax500" wrote ..
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,1 1,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,1 9,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,1 1,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
--
Rgds
Max
xl 97
--
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
---

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


Max wrote:
"snax500" wrote ..
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:
=3DHLOOKUP(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)


=3DSUMPRODUCT(--(MOD(COLUMN(OFFSET($A$3,,,,CHOOSE($A$1,1,3,5,7,9,1 1,13,15,1=
7,1

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

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,1 9,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)=
=3D1
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:


=3DSUMPRODUCT(--(MOD(COLUMN(OFFSET($A$3,,,,CHOOSE($A$1,1,3,5,7,9,1 1,13,15,1=
7,1

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

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

SUMPRODUCT({1,0,1},{100,200,300})
will then evaluate the product of the 2 arrays as:
=3D 1x100 + 0x200 + 1x300
=3D 100 + 0 + 300
=3D 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:
=3D 1x100 + 0x200 + 1x300 + 0x400 + 1x500
=3D 100 + 0 + 300 + 0 + 500
=3D 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
--
Rgds
Max
xl 97
--
GMT+8, 1=B0 22' N 103=B0 45' E
xdemechanik <atyahoo<dotcom
---

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

You're welcome !
--
Rgds
Max
xl 97
--
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
---