+ Reply to Thread
Results 1 to 3 of 3

Conditional formatting to test for variance

Hybrid View

  1. #1
    Registered User
    Join Date
    03-19-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    46

    Conditional formatting to test for variance

    Hi,

    I'm trying to test for variance using conditional formatting. Example attached.

    The problem I'm having is that excel highlights any variance, whereas I don't care about really small variances. In row 3 for example, as long as the numbers would round to 500, I don't care. The numbers are variable and will change on a monthly basis. It might be 500 this month, but 300 next month, and I'm having trouble writing a formula that will limit the highlighting to situations where the rounded total would be different.

    Thanks.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Conditional formatting to test for variance

    You could use SPC techniques.
    > Calculate the standard deviations for all values in a row (preferably more than 15)
    > Multiply the standard deviation by 3 for 3 sigma
    > Calculate upper and lower control limits by adding and subtracting the 3 sigma limit from the average
    > Highlight anything not within the 3 sigma range
    Elegant Simplicity............. Not Always

  3. #3
    Registered User
    Join Date
    03-19-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    46

    Re: Conditional formatting to test for variance

    There's only 3 values in each row, so st.dev won't have much impact.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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