Solved

Conditional Formatting--with a twist

Posted on 2013-01-09
8
194 Views
Last Modified: 2013-01-11
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
Comment
Question by:spudmcc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
8 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
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

by:Patrick Matthews
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

by:barry houdini
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 92

Expert Comment

by:Patrick Matthews
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

by:spudmcc
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

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

by:Patrick Matthews
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

by:spudmcc
ID: 38768178
FANTASTIC!

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


Andy (spudmcc)
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question