Average last 14 Days

[lex_m]

I have data starting in B8 down, will be added to all the time, so with the sample data below how can I get and average for the last 14 days for column D, E and F.
Note this needs to be the last 14 days and not the last 14 rows. So in this case it would be 127 78 71

Thanks
Excel 2003

Excel 2003
	B	C	D	E	F
7	Date	Time	Systolic	Diastolic	Pulse
8	01/01/16	6:45 PM	140	81	73
9	01/02/16	6:38 AM	126	77	66
10	01/02/16	2:44 PM	125	72	77
11	01/02/16	6:41 PM	125	76	73
12	01/03/16	8:02 AM	138	82	64
13	01/03/16	6:41 PM	146	81	74
14	01/04/16	6:47 AM	138	82	61
15	01/04/16	4:53 PM	145	84	83
16	01/05/16	7:06 AM	140	83	73
17	01/05/16	10:32 PM	133	76	75
18	01/06/16	7:06 AM	140	85	65
19	01/06/16	9:05 PM	123	71	80
20	01/07/16	7:59 AM	138	84	70
21	01/08/16	7:08 AM	132	80	65
22	01/08/16	7:12 PM	112	67	86
23	01/09/16	6:37 AM	119	82	69
24	01/09/16	1:10 PM	125	76	84
25	01/10/16	7:11 AM	135	66	80
26	01/11/16	7:46 AM	131	83	65
27	01/12/16	8:17 AM	146	86	65
28	01/12/16	12:32 PM	123	77	65
29	01/13/16	7:15 AM	130	81	69
30	01/14/16	8:34 AM	121	81	70
31	01/15/16	7:26 AM	125	75	68
32	01/15/16	7:22 PM	135	79	65
33	01/16/16	7:15 AM	126	84	64
34	01/17/16	8:33 AM	117	77	68
35	01/17/16	4:05 PM	131	70	81
36	01/18/16	7:54 AM	126	76	67
37	01/19/16	9:28 AM	121	80	62
38	01/19/16	11:32 AM	107	71	72
39	01/20/16	8:36 AM	138	82	72
Sheet1

 

[Jonmo1]

Which version of Excel are you using?

Excel 2007+, try

=AVERAGEIF($B$8:$B$39,">="&TODAY()-14,D$8:D$39)

 

[lex_m]

Excel 2003

 

[Jonmo1]

In that case, you'll need an array formula entered with CTRL + SHIFT + ENTER

=AVERAGE(IF($B$8:$B$39>=TODAY()-14,D$8:D$39))

 

[lex_m]

Thanks that did it!

 

[Jonmo1]

You're welcome.

Also, this may seem more cumbersome, but it may actually be more efficient (not an array formula)

=SUMIF($B$8:$B$39,">="&TODAY()-14,D$8:D$39)/COUNTIF($B$8:$B$39,">="&TODAY()-14)

 

[lex_m]

Thanks for the other option