How do I use an array formula linking to today's date?

**samyoungs** · 11-06-2011, 12:19 AM

Hi
I'm trying to calculate moving averages linked to today's date using vlookup. Example data is below. I can get the 7 day Moving Average using the following cumbersome vlookup formula (=AVERAGE(VLOOKUP(F3,D29:E36,2,FALSE), VLOOKUP(F3-1,D29:E36,2,FALSE), VLOOKUP(F3-2,D29:E36,2,FALSE),VLOOKUP(F3-3,D29:E36,2,FALSE), VLOOKUP(F3-4,D29:E36,2,FALSE), VLOOKUP(F3-5,D29:E36,2,FALSE),VLOOKUP(F3-6,D29:E36,2,FALSE))

Not much good for the 30 day moving average formula though. Can I use an array formula to do this better?

Thanks for any help

Today 8/10/2011

Sales 7 Day Moving Average 30 Day Moving Average
8/09/2011 20 27.14285714
9/09/2011 0
10/09/2011 10
11/09/2011 60
12/09/2011 60
13/09/2011 10
14/09/2011 0
15/09/2011 0
16/09/2011 10
17/09/2011 60
18/09/2011 60
19/09/2011 20
20/09/2011 10
21/09/2011 60
22/09/2011 60
23/09/2011 10
24/09/2011 0
25/09/2011 0
26/09/2011 10
27/09/2011 60
28/09/2011 60
29/09/2011 50
30/09/2011 30
1/10/2011 20
2/10/2011 50
3/10/2011 0
4/10/2011 10
5/10/2011 60
6/10/2011 60
7/10/2011 10
8/10/2011 0

**JBeaucaire** · 11-06-2011, 01:19 AM

SUMPRODUCT() should be able to do it:

=SUMPRODUCT(--($A$1:$A$1000<=TODAY()), --($A$1:$A$1000>TODAY()-30), $B$1:$B$1000) / SUMPRODUCT(--($A$1:$A$1000<=TODAY()), --($A$1:$A$1000>TODAY()-30))

**DonkeyOte** · 11-06-2011, 02:45 AM

FWIW, I would advise against using volatiles like TODAY in conjunction with SUMPRODUCT.

If an Array / SUMPRODUCT is required I would suggest entering date of interest as a static value - use VBA to do this via Workbook Open even if preferred

If as implied by your data set it is:

1 - always sorted by date
2 - inclusive of every date
3 - one entry per date

or combinations thereof you should find you can use some fairly standard formulae to calculate your averages

Are you able to post up a more complete data set via sample file ?

Also, to confirm, XL2003 ?

**samyoungs** · 11-06-2011, 03:01 AM

thanks mate that works. Can't say I understand it though! Is the "--" symbol another way of representing "if"

**DonkeyOte** · 11-06-2011, 03:11 AM

-- is double unary and in this instance is used to coerce boolean (True/False) to integer equivalent (native XL 1/0 respectively)

For a good overview of SUMPRODUCT inclusive of coercion see SUMPRODUCT link in my sig. (Bob Phillips' white paper)