100% rounding

[Helen Wills]

Hi

I have an audit template where we have a set of scored criteria.
each one marked green = 5 points
amber = 3 points
red = 0 points

We add up all the the score, using aggregate(9,6,range)
We then calculate a %age grade using a simple divide total by max possible total formula

This works fine when all are marked green. It also works fine if the score is anything less than 99%. However, in the event that through the whole entire audit, just one single question is amber the percentage is 99.5%. The %age grade cell is displaying 100%. I can't have this being the case, as if one question is is scored as amber, it cannot be a 100% score !

I have no issues with the scores at anything other than 99% being rounded up. I just can't have 99% being rounded up... any ideas please???



hav

 

[Ron Coderre]

Maybe this?

I reference cell A1
• Use a number format of Percentage: Zero decimal places
• Apply this conditional formatting rule:
Rule: =AND(A1>0.99,A1<1)
Number Format: Percentage (2 decimal places)

If the A1 value is 0.99 or less....zero decimal places (0.841 would display as 84%)
If A1 is between 0.99 and 1, exclusive ...2 decimal places (0.996 would display as 99.6%)

Is that something you can work with?

 

[Helen Wills]

that has worked great! thank you! x

set just to show 1 decimal place for 99.5%. thanks so much