Solved

Conditional Formatting--with a twist

Posted on 2013-01-09
8
157 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
  • 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
 
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now