+ Reply to Thread
Results 1 to 5 of 5

Threshold formula

  1. #1
    Registered User
    Join Date
    01-02-2012
    Location
    Georgia
    MS-Off Ver
    Excel
    Posts
    36

    Threshold formula

    I am trying to create a formula or conditional format for my pivot table that shows the trend from the previous month using the arrow up, down and dash icon. if the % change exceeds a specified threshold the arrow would be up. If the % doesn't change from the previous month then it would be a dash and if the % change is lesser than the previous month the arrow would be down. My threshold base will be the May volume. Each month after will follow the threshold from the previous month to verify the trend.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Threshold formula

    Can you provide some clarification of what you expect the outcome to look like? I'm not quite clear on what exact numbers you want to use as the threshold. For instance, should B4 have a down error because 85.17% is less than 90.10%? Is that what you mean by percent change? Or should B4 have a down error because the % change from June to July was -4.93%, which is less than the May to June % change of +6.8%. In other words, If group 1's May-June-July values were 80%-85%-90%, would June and July get up arrows or dashes?

    This seems like it can be done with conditional formatting, but some threshold clarification would help.

  3. #3
    Registered User
    Join Date
    01-02-2012
    Location
    Georgia
    MS-Off Ver
    Excel
    Posts
    36

    Re: Threshold formula

    If group 1 June - July values are greater than May values it would be a up arrow. If June -July values are less than may values it would be a down arrow. And if it is the same or within goal (threshold table) then it would be a dash.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Threshold formula

    I dont think you can use arrows and other icons when referring to other cells, you would need to use "Use Formula" - which does not allow the use of icons
    Perhaps just use Green, no color and Red?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Threshold formula

    As Ford mentioned, icon sets aren't nearly as flexible as conditional formatting with colors. The most manageable options are typically to use Red/Yellow/Green fill shadings on your existing columns or to create a second column for each group with a formula that determines your values and then apply icon sets to that new column. In the attachment, I've created a helper column (C) for Group 1 that uses the formula:

    =$B2-$B$2

    ...filled down to compare every Group 1 total to May. You can then use CF icon sets to transform those values into icons. This method requires a few extra columns, but it gives you much greater control over your threshold comparisons. See attached:
    Attached Files Attached Files

+ 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: 11-30-2016, 08:02 PM
  2. Formula help for bonus calculation with minimum threshold?
    By wisey11 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-07-2015, 09:56 AM
  3. Help required - formula to check minimum and maximum threshold
    By ismailm in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-18-2014, 08:33 AM
  4. Threshold formula creates circular error
    By jmow in forum Excel General
    Replies: 4
    Last Post: 06-13-2012, 02:00 PM
  5. Tax Threshold formula
    By Raj Singh in forum Excel General
    Replies: 2
    Last Post: 08-17-2011, 12:14 AM
  6. threshold formula
    By Hidai in forum Excel General
    Replies: 11
    Last Post: 04-12-2011, 04:30 PM
  7. Help with a threshold formula please
    By teeach in forum Excel General
    Replies: 2
    Last Post: 01-27-2010, 09:18 AM

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