• Status: Solved
• Priority: Medium
• Security: Public
• Views: 234

# Conditional Formatting--with a twist

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
spudmcc
• 5
• 2
1 Solution

Commented:
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

Commented:
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

Commented:
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

Commented:
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 Commented:
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

Commented:
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

Commented:
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 Commented:
FANTASTIC!

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

Andy (spudmcc)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Featured Post

• 5
• 2
Tackle projects and never again get stuck behind a technical roadblock.