• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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