Solved

Conditional Formatting--with a twist

Posted on 2013-01-09
178 Views
Hi Experts

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.

spudmcc
0
Question by:spudmcc
• 5
• 2

LVL 92

Expert Comment

ID: 38761168
Assuming:

1) A2:A367 have dates for 1 Jan 2012 through 31 Dec 2012
2) B2:B367 indicate weekday for that date
3) C2:C367 indicate sales for that day
4) You are using Excel 2007 or later

then do the following:

1) Select A30:C367
2) Create a formula-based Conditional Formatting rule using this formula:
=ABS((\$C30-AVERAGEIF(\$B2:\$B29,\$B30,\$C2:\$C29))/AVERAGEIF(\$B2:\$B29,\$B30,\$C2:\$C29))>0.1
3) Select the formatting you want to apply with that rule
0

LVL 92

Expert Comment

ID: 38761175
If you are using Excel 2003, the CF formula would be:

=ABS((\$C30-SUMIF(\$B2:\$B29,\$B30,\$C2:\$C29)/COUNTIF(\$B2:\$B29,\$B30))/(SUMIF(\$B2:\$B29,\$B30,\$C2:\$C29)/COUNTIF(\$B2:\$B29,\$B30)))>0.1
0

LVL 50

Expert Comment

ID: 38761253
That looks good Patrick....although you can simplify formula a little, i.e.

=ABS(\$C30/AVERAGEIF(\$B2:\$B29,\$B30,\$C2:\$C29)-1)>0.1

Note: weekdays in column B need to be text values not formatted dates

regards, barry
0

LVL 92

Expert Comment

ID: 38761505
Nice work on shortening that formula :)

And yes, I should have made clear that I am assuming Col B is not simply date values using a number format to display the weekday.

In my testing, I used text values, but it would have worked just aswell if I had used, say, weekday index numbers such as are provided by the WEEKDAY() function.
0

Author Comment

ID: 38767741
I am so sorry for not getting right back but got called into another project.  My apologies!

I've attached an example of the data.  I am not sure that I am making myself clear in what I need.  I've applied your formula to the conditional formatting rule and I just am not sure what it is doing.  It is highlighting lots of things.

I need only those cells that are plus or minus 10% difference from the average of the four prior weeks (same day of the week only).  This applies to the entire year.  I don't want it to look at a "static" period of time but a rolling period of time.  Example:  first set of 5 week period.  Week 5 will look at prior 4 weeks (same day of week) and highlight cell if plus OR minus 10%.  The 6th week will be looking at weeks 2-5, the 7th at 3-6.  Always looking back at the prior 4 weeks---but only at the same day of the week.

Maybe I just don't have it setup correctly.  I truly appreciate any help you can offer me.

Thanks

spudmcc (Andy)
EXAMPLE.xlsx
0

LVL 92

Accepted Solution

Patrick Matthews earned 500 total points
ID: 38767831
Andy,

If you are always looking at the prior four instances of the same weekday, then you cannot apply the CF to the whole worksheet.  That is why I recommended you start on Row 30.

Anyway, the attached worksheet clears your old rules, and does the following:

1) Applied a rule to G30:J183 using this formula

=ABS(G30/AVERAGEIF(\$B2:\$B29,\$B30,G2:G29)-1)>0.1

2) Applied yellow formatting for it

Note that I used barry's somewhat simpler formula.  Mine was correct, but not as compact as it could have been.

Q-27990413.xlsx
0

LVL 92

Expert Comment

ID: 38767841
As to why it works...

1) The AVERAGEIF expression takes the average in the preceeding rows of G where the corresponding rows in B (the weekday) are the same as the current row

2) G30 divided by that expresses G30 as a percent of that average

3) Subtracting one shows the difference in percent terms

4) ABS gets the absolute value, and we then compare that to 0.1 (or 10%)

Note that the use of a mix of absolute references (denoted by \$) and relative references is absolutely essential here.
0

Author Closing Comment

ID: 38768178
FANTASTIC!

This is exactly what we were trying to accomplish!  Thanks so much!

Andy (spudmcc)
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.