+ Reply to Thread
Results 1 to 3 of 3

Formula to identify variance

  1. #1
    Registered User
    Join Date
    04-26-2013
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2007 & Excel for Mac
    Posts
    7

    Formula to identify variance

    I have a report where I am looking at weekly volumes. The ask for his report is to provide some sort of visual identification to highlight when one weeks volume may seem abnormally higher or lower than the previous week. I've attached an example with 13 weeks of data. If you look at the first week it's at 497 while the rest of the weeks are pretty much around 700. I would want a way to highlight that cell as a callout, maybe in yellow.

    Is there a formula that I could apply that compares week over week and determines if it is plus/minus a certain percentage of the cell next to it that it would highlight it? I know that is pretty vague but I want to leave it high level to start.

    Also, there would be about 3,000 rows of data that I would have to apply this to.

    Thanks in advanced guys! If you need me to clarify more please let me know.

    Thanks,
    Bob
    Attached Images Attached Images

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,705

    Re: Formula to identify variance

    The way to do this is with Conditional Formatting. You would use the option to "Use a formula to determine which cells to format" and then use a formula like this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and replace x with the percentage you want to check for. You would apply this rule from cell B2 to the lower right corner of your range, like Z3000.

    If you have no experience at all with Conditional Formatting then feel free to ask for more details on the detailed steps.

    It's always better to attach a file. I could just update your file for you if you do so.
    Last edited by 6StringJazzer; 03-07-2018 at 09:22 PM. Reason: Added /A2 in the formula
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    04-26-2013
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2007 & Excel for Mac
    Posts
    7

    Re: Formula to identify variance

    Jeff,

    Thanks a lot for the quick response. I figured it was something to do with Conditional Formatting I just wasn't very sure on the formula. What you wrote made sense. Thank you for offering to work on the file, unfortunately it is confidential based on the client related data.

    I will try your solution tomorrow at work and will report back either way.

    Thanks again and have a great night!

    Bob

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 08-01-2016, 09:07 PM
  2. toggle between variance in units and % variance
    By ammartino44 in forum Excel General
    Replies: 3
    Last Post: 06-03-2014, 07:21 PM
  3. [SOLVED] Find the date of a variance amount over a limit and count the days since the variance.
    By avidcat in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 06-21-2013, 12:00 PM
  4. Price Variance and Dollar Variance for multiple stores on certain products
    By hutchgeo4 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-22-2013, 05:49 PM
  5. [SOLVED] Compare Data from Two different tabs and to identify variance items
    By Excel Dumbo in forum Excel General
    Replies: 4
    Last Post: 10-16-2012, 10:58 PM
  6. [SOLVED] Pivot Tables - Variance and Variance %
    By PJS in forum Excel General
    Replies: 2
    Last Post: 01-17-2006, 11:15 PM
  7. [SOLVED] Pivot Tables - Variance and % Variance fields
    By CraigS in forum Excel General
    Replies: 5
    Last Post: 01-05-2005, 09:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1