Check if data is 10% above or below rolling 3 weeks average

[MDuff]

I have my data listed in the table below

What I am looking for is (at least in my mind) a little complicated

What i need is that if the data on a rolling 3 weeks average of each weekday is above or below "10%" then it "flags" it this is from the data in B SumOfcallsoffered ONLY

I would need this to be rolling form the start date of the data to the end date and this data set will be increasing as time goes by. and checking every weekday on this criteria moving forward down the data. It is fine if we use the 1st 3 weeks as a "base" then continue for the rest of the data set

So it would need to average the last 3 Mondays in the list then check if the 4th Monday is +/- 10% and if so "flag it with and X or a word then repeat for the next 3 weeks etc

I hope this is clear and if you need any other information please let me know

PS it can be in Formulas UDFs or VBA

Thanks in advance

Forecasting v1.xls
	A	B	C	D
1	date_numeric	SumOfcallsoffered	SumOfacdcalls	AHT
2	01-Jan-05	66	65	302.6923077
3	02-Jan-05	122	119	221.4453782
4	03-Jan-05	1987	1966	206.2894201
5	04-Jan-05	2023	1996	204.5761523
6	05-Jan-05	2122	2015	210.5052109
7	06-Jan-05	1993	1949	209.5489995
8	07-Jan-05	1909	1877	207.7565264
9	08-Jan-05	150	148	197.3918919
10	09-Jan-05	108	106	271.5754717
11	10-Jan-05	2223	2187	197.3886603
12	11-Jan-05	2089	2050	207.3282927
13	12-Jan-05	2154	2089	209.5222595
14	13-Jan-05	1960	1940	198.5273196
15	14-Jan-05	1908	1851	222.3662885
16	15-Jan-05	150	147	177.4965986
17	16-Jan-05	105	105	210.0952381
18	17-Jan-05	1451	1445	197.5882353
19	18-Jan-05	2356	2302	207.7836664
20	19-Jan-05	2244	2180	203.2357798
21	20-Jan-05	2100	2069	210.0183664
22	21-Jan-05	2027	1956	212.0674847
23	22-Jan-05	117	116	216
24	23-Jan-05	91	91	270.8351648
25	24-Jan-05	2228	2157	201.1942513
26	25-Jan-05	2271	2159	209.7253358
27	26-Jan-05	2047	2010	220.4920398
28	27-Jan-05	2049	1988	212.2022133
29	28-Jan-05	1943	1883	217.9490175
30	29-Jan-05	118	118	190.3389831
Baseline

 

[RalphA]

I entered this formula,

=OR(B23>(INDIRECT("B"&ROW()-21)+INDIRECT("B"&ROW()-14)+INDIRECT("B"&ROW()-7))/3*1.1,(B23<(INDIRECT("B"&ROW()-21)+INDIRECT("B"&ROW()-14)+INDIRECT("B"&ROW()-7))/3*0.8))

in an empty column (I used column G), in the range, G23:G30, coverting 22-Jan through 29-Jan. Only cell G25 showed TRUE, all the others showed FALSE. I clicked on B23, Format, Conditional formatting..., and entered the formula, =G23, clicked on Format, Patterns, and chose a medium-blue color, then OK, OK. That did it, only cell B23 has a blue background.

I would like o say that I tried the complee formula for the Conditional formatting, and, somehow, couldn't get it to work!

 

[tusharm]

The below assumes you have one row for each date.

In F1 enter the threshold of interest, 10% in your example.

In E23 enter the formula =AVERAGE(N(OFFSET(B23,{-7,-14,-21},0,1,1)))

In F23 enter the formula =IF(B23>(1+$F$1)*E23,"Above",IF(B23<(1-$F$1)*E23,"Below",""))

Copy E23:F23 as far down column E as you have data.

 

[MDuff]

you guys ROCK

thanks so much!!!!!1