# Thread: Excel 2007 Conditiona Formatting Formula help

1. ## 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

2. How exactly do the grades affect the calculation?

3. 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

4. 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. Many thanks Zedy, I will test you suggestion late in the week.

Thank you

#### Posting Permissions

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