lookup values by date

Hi,

See attached.

I've added formulae for the 3 months before and after. I'll leave you to replicate the formula for the +/- 6 months & +/- 12 months.

You need to decide whether the C column dates and the prior and succeeding cut off dates are inclusive or exclusive.

Hi,

You can't just copy the formula over since if you look the reference to column C is not absolute. Maybe it should have been but that's life! Similarly the reference to JD2:BD2 should have been $JD2:$BD2.

And yes you'll need to change the -3 & 2 in the EOMONTH part to -6 +5, and -12 +11

Hi,

The EOMONTH(() function is also new in 2007 and not present in Excel 2003. If you can live with a definition of 6 months being 183 days then in F2

Formula:

=SUMPRODUCT(($J2:$BD2)*($J$1:$BD$1>=$C2-183)*($J$1:$BD$1<$C2))

and for G2

Formula:

SUMPRODUCT(($J2:$BD2)*($J$1:$BD$1>=$C2)*($J$1:$BD$1<$C2+183))

Similar for the 3 month and 12 month formulae. If the 6 month needs to work with calendar months then a more complicated function will be necessary. Note that using 183 days like this will not always give you the same result as using calendar months since depending on the month span, 183 may not co-incide with 6 months before or after.

Originally Posted by djapigo

Sorry, try this...

D2: =SUMPRODUCT(($J2:$BD2)*($J$1:$BD$1>=DATE(YEAR($C2),MONTH($C2)-3,DAY($C2)))*($J$1:$BD$1<$C2))

Just a thought, but isn't one of the difficulties with this that if the test date is in the Jan-March period, the function MONTH(C2)-3 will result in zero or minus. That was one of my reasons for thinking that the pragmatic approach of using 183 days is simpler.

Originally Posted by djapigo

Hi Richard,

I wasn't sure either, but I took a shot and it seems the DATE function can read negative months and just remove it from years... go figure...

Mmm. Interesting. Must admit didn't test that proposition, mainly because I've always had in my mind that a deduction of a negative month couldn't wind back the year as well.

....you live and learn as they say.