cancel
Showing results for
Did you mean:
Helper V

## Zeros not appearing after decimal

I have a couple of cells that display the results of some calculations. I wanted 2 numbers to display after the decimal. It works as long as the number is not zero. Whenever there is a zero for the last number it doesnt display.

I have tried using: (Formula is just my calculations like B4-C5)

Text(Formula, ,"00.0000")*100&"%"

Text(Round(Formula,4)*100&"%")

Text(Trunc(Formula,0))

No matter what I do, I get ##.## as long as that last digit isn't zero.

2.8 should be 2.80

If I do "0.00000" I get 2.798

If I do "0.0000" I get 2.8

How do  I get it to display that last zero?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

See here

start with ##, but after the decimal use 0s to retain insignificant 0's

``Text(100/200, "##.00")``

this example returns .50, where ##.## returns .5. In examples above you multiply after formatting, this should be done before liek so

``````Text(
//original formula
Sum(100/20000)
//multiply
*100,
//format
"##.00")``````

multiplying after formatting was discarding the formatting

_____________________________________________________________________________________
Like my answer? - Hit that Thumbs Up. Resolved the Issue? - Hit Accept as Solution.
This helps others find solutions to future issues!
2 REPLIES 2
Super User

See here

start with ##, but after the decimal use 0s to retain insignificant 0's

``Text(100/200, "##.00")``

this example returns .50, where ##.## returns .5. In examples above you multiply after formatting, this should be done before liek so

``````Text(
//original formula
Sum(100/20000)
//multiply
*100,
//format
"##.00")``````

multiplying after formatting was discarding the formatting

_____________________________________________________________________________________
Like my answer? - Hit that Thumbs Up. Resolved the Issue? - Hit Accept as Solution.
This helps others find solutions to future issues!
Helper V

@TheRobRush multiplying after formatting was discarding the formatting

Brilliant!! That was it all along. Thank you so much!!

Announcements