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