Conditional Formatting--with a twist
Posted on 2013-01-09
I have a request that I am not really sure how to approach. This is why I am turning to you.
I have a spreadsheet. In column A are dates in sequential order. 1-1-2012 thru 12-31-12. Column b is the Day of the week name. Column three sales numbers.
What I would like to do beginning at the fifth week, create an average of the prior 4 weeks of Mondays, Tuesday...etc. On the 5th Monday if the sales number is lower or higher by 10% change to yellow. Do same for each of the other days of the week. I need this to be dynamic so that next week it will be looking at weeks 2-5 for the average and looking at week 6. This would happen until the end of the date range. It might not always be a year at a time. It could be 6 months, 9 months, 2 years.
Any ideas, thoughts or help would be greatly appreciated as always.
Thanks in advance.