display 14.0% as 14% and 14.1% as 14.1% (remove insignificant decimals)

[6diegodiego9]

How can I compose the number formatting to display percentages with one decimal except if it's 0?
14.0% -> 14%
14.1% -> 14.1%

(I need to show them like that in a chart)

[Glenn Kennedy]

Are these calculated values, or entered values? Formatting entered values as General does what you want. We need more context. Please see yellow banner (top).

[6diegodiego9]

General shows 14.0% as 1400 :-(

They are calculated values

[menem]

If you don't mind to use condition format.

Data in D3

use this condition for 14% (format string : 0%)
=ROUND(MOD(D3*100,1),6)=0

and this condition for 14.1% (format string : 0.#%)
=Round(MOD(D3*100,1),6)>0

Regards.

[Glenn Kennedy]

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.

[Roel Jongman]

you can use conditional formatting for this, since there is no example file I had to guess a bit on how you want it rounded.

First step is to set your celformatting to show one decimal. which is the normal I assumed
then make a conditional format (Cf) rule to show no decimals if it is a round number.

I used the mod() function and round function to do that. you may need to play with the exact condition or use a roundup or rounddown function instead to get to the exact result you wnat.
this formula wil round 0,04999 down to 0 and 0,05000 and up to 0,1

=ROUND(MOD(B4*100;1);1)<0,05

see example file for my formula in the CF

[6diegodiego9]

you can use conditional formatting for this, since there is no example file I had to guess a bit on how you want it rounded.
[...]
see example file for my formula in the CF

Thanks Roel!
Conditional formatting was an interesting solution indeed but as I need the values to be displayed in a chart, it didn't help:

Attachment 778541

[Glenn Kennedy]

Can we have a sample FILE... not a picture of one?

[6diegodiego9]

Can we have a sample FILE... not a picture of one?

Sure. That's the same file already posted by Roal, with a default chart.

Attachments feature don't work:
Immagine.png

[Roel Jongman]

your attachment did not load..

But the next best thing would then be to use text function for use in the chart. see my new example/

I Used the formula of the CF in a cell combined with a text function.\

you need a separate column to create the text labels for the x-axis.

[6diegodiego9]

Thanks Roel!
Your solution converting it to text with formula
=TEXT(B2;IF(ROUND(MOD(B2*100;1);1)<0,05;"0%";"0,0%"))
is not ideal but likely the only available.
However it's not working correctly for 13,97%, as it's still displayed as 14,0%

[Glenn Kennedy]

1. You did not read the instructions fully. There is NO mention of the attachments button in the explanation... because it does not work!!!

2 Try this (format as General):

=100*ROUND(E2,3)

[6diegodiego9]

1. You did not read the instructions fully. There is NO mention of the attachments button in the explanation... because it does not work!!!

Ok I now saw it. Thanks. I hope to not forget it.

2 Try this (format as General):
=100*ROUND(E2,3)

It misses the "%"

[Glenn Kennedy]

Format as General %

[6diegodiego9]

Format as General %

It works! Thanks so much!!!