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

MDuff

Well-known Member
Joined
Dec 29, 2002
Messages
529
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
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
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
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
Joined
May 28, 2002
Messages
11,007
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.
 

Forum statistics

Threads
1,089,419
Messages
5,408,116
Members
403,185
Latest member
sp646

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top