Ask Your Question

Calc: Remove decimal places from values = 0, otherwise display decimals

asked 2020-05-29 07:07:40 +0200

rknox51 gravatar image


I have a conditional statement in one column that, if the value in a second column is TRUE, enters a value from a third column and enters 0 if the value in the second column is FALSE. The formula is: =IF(F6=1, B6, 0).

I'd like to format the values in column F to display 4 decimal places if they are non-zero and to display no decimal places if they are equal to 0. In other words, I'd like the outputs to be as follows:

0.9417 -> 0.9417
2 -> 2.0000
0 -> 0

Right now, I have the format code set to 0.#### but this isn't quite what I need. The outputs I'm getting look like this:

0.9417 -> 0.9417
2 -> 2
0 -> 0

The first and third values are correct, but the second is not. Is there a way to alter the cell format code such that decimals are always displayed if the number in it is non-zero?

Thank you in advance!

edit retag flag offensive close merge delete



Number Format Codes in Help: right at the start, it discusses the four sections of the format code.

Alternatively, you could use conditional formatting for 0 (bulkier for this case IMO).

Mike Kaganski gravatar imageMike Kaganski ( 2020-05-29 07:14:46 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-05-29 07:56:12 +0200

JohnSUN gravatar image

As @Mike Kaganski explained, the easiest and most effective way to solve your problem is in a custom number format.

For your example, the format code is 0.0000;-0.0000;0;@

Formar code.png

edit flag offensive delete link more


This is exactly what I needed, thank you!

rknox51 gravatar imagerknox51 ( 2020-05-29 18:28:11 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-05-29 07:07:40 +0200

Seen: 33 times

Last updated: May 29