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

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.  

Thanks in advance.

spudmcc
0
spudmcc
Asked:
spudmcc
  • 5
  • 2
1 Solution
 
Patrick MatthewsCommented:
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
 
Patrick MatthewsCommented:
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
 
barry houdiniCommented:
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Patrick MatthewsCommented:
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
 
spudmccAuthor 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
 
Patrick MatthewsCommented:
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
 
Patrick MatthewsCommented:
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
 
spudmccAuthor 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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

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