Conditional formatting remove decimal on percent when round number

**veeejay** · 02-06-2019, 03:00 PM

Is there a formula I can use to remove the decimals when using percentages if the number is round?

I'm looking to have 2 decimals, but to remove them if the number is round.

Ex.

1 = 100%
0.75 = 75%
0.5 = 50%

But

0.165 = 16.5%
0.8454 = 85.54%

How can I achieve this?

Thanks

**leigh3980** · 02-06-2019, 03:24 PM

Hi Veeejay,
I am not sure I quite understand what you are after but if you are trying to get just 1 number after the decimal this would work, assuming the number you are trying to round is in A1:
=ROUND(A1,1)
thanks
Leigh

**MrShorty** · 02-06-2019, 03:25 PM

Condition formula number-MOD(number,0.01)=number should be able to detect when your number is an integer percent (unless floating point errors become a problem).

**Roel Jongman** · 02-06-2019, 03:32 PM

Use as CF formula

Please Login or Register  to view this content.

and set format to % with 0 decimals

applied range should start at A1 for this formula

**veeejay** · 02-06-2019, 03:55 PM

But this will remove all decimals.
I'm still looking to get up to two decimals, only when they exists and not x.00%

The reasoning is that with large number set the .00% makes it really hard to read.

EDIT: Sorry it was doing a weird thing but I got it to work!
Thanks

**Roel Jongman** · 02-06-2019, 04:26 PM

Eventhough the main question is solved. I put some experiments with several floating format options in the attached sheet. Just thought it might be usefull to share the experiment.

in col B there is 4 CF rules set up to show no, 1, 2 or 3 decimals, not that the order of CF is crucial for it to work properly.

in Col D there is format mask, 0,???% this will align percentages on decimal and show 0-3 decimals depending on outcome, the whole percentages look strange due to decimal separator being visable also the reading with spaces can take some getting used to

in Col F there is a format mask 0,###% this will also show 0-3 decimals depending on outcome but, it has the same problem as the above, the decimal separator stays visible on whole numbers

in Col H I is the same format as col F but put in a CF for the whole numbers.

(be aware that my system is setup to use comma as decimal separator, for some countries you may need to setup your own custom formats to use point as decimal separator instead)