Results 1 to 5 of 5
  1. #1
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Excel 2007 Conditiona Formatting Formula help

    The attached data in the first rows is correctly formatted to coampare actual times to target times.

    The data under this now has a new column Grade, and depending on the grade then the target times will be increased for the conditional formating.

    I am having problems working out how to obtain the conditional formating based on the grade code that means one of the formula parameters becomed the target value divided by a factor.

    All ideas welcomed
    Attached Files Attached Files
    Paul Coyle
    Approach love and cooking with reckless abandon

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    How exactly do the grades affect the calculation?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,824
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi

    If you want to scale the target times according to Grade, then you can make use of the CHOOSE function.

    For example, if the Target times for a Grade 1 is same as original target times, use a scale of 100%.
    If the Target times for a Grade 2 are 90% of the original target times, use a scale 0f 90%.
    If the Target times for a Grade 3 are 75% of the original target times, use a scale 0f 75%.

    We could place these scales for the 3 Grades into named cells e.g. factor1, factor2 and factor3
    We can then use the scale as follows:
    =CHOOSE(B17,factor1,factor2,factor3)
    ..where cell B17 holds the Grade value (i.e. 1, 2 or 3)

    We could of course 'hard code' scale factors like this:
    =CHOOSE(B17,1.0,0.9,0.75)
    ..but if we use named cells, it will be easier to adjust.

    If you wanted to allow 50% more 'time' for say, Grade 3, then you would use a factor of 150% etc.

    see attached file.

    zeddy
    Attached Files Attached Files

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,824
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi

    ..by the way, it doesn't make sense to apply conditional formats to the 'Average' time values (since there is no equivalent 'target average time').
    ..so I removed that formatting in the Grade Reporting block.
    Note: The CHOOSE function described in previous post was used in the other conditional formatting formulas.

    zeddy

  5. #5
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts
    Many thanks Zedy, I will test you suggestion late in the week.

    Thank you
    Paul Coyle
    Approach love and cooking with reckless abandon

Posting Permissions

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