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

MDuff

Well-known Member
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

Forecasting v1.xls
ABCD
1date_numericSumOfcallsofferedSumOfacdcallsAHT
201-Jan-056665302.6923077
302-Jan-05122119221.4453782
403-Jan-0519871966206.2894201
504-Jan-0520231996204.5761523
605-Jan-0521222015210.5052109
706-Jan-0519931949209.5489995
807-Jan-0519091877207.7565264
908-Jan-05150148197.3918919
1009-Jan-05108106271.5754717
1110-Jan-0522232187197.3886603
1211-Jan-0520892050207.3282927
1312-Jan-0521542089209.5222595
1413-Jan-0519601940198.5273196
1514-Jan-0519081851222.3662885
1615-Jan-05150147177.4965986
1716-Jan-05105105210.0952381
1817-Jan-0514511445197.5882353
1918-Jan-0523562302207.7836664
2019-Jan-0522442180203.2357798
2120-Jan-0521002069210.0183664
2221-Jan-0520271956212.0674847
2322-Jan-05117116216
2423-Jan-059191270.8351648
2524-Jan-0522282157201.1942513
2625-Jan-0522712159209.7253358
2726-Jan-0520472010220.4920398
2827-Jan-0520491988212.2022133
2928-Jan-0519431883217.9490175
3029-Jan-05118118190.3389831
 Baseline

RalphA

Well-known Member
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

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

Well-known Member
you guys ROCK

thanks so much!!!!!1