100% rounding

Helen Malone

New Member
Joined
Nov 14, 2014
Messages
15
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

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,307
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?
 
Last edited:

Helen Malone

New Member
Joined
Nov 14, 2014
Messages
15
that has worked great! thank you! x

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

Forum statistics

Threads
1,082,438
Messages
5,365,531
Members
400,837
Latest member
ELMST616

Some videos you may like

This Week's Hot Topics

Top