Forum Discussion

Copper Contributor

Oct 15, 2017

Limiting number of decimal places in a returned formula

I have an Ex 2010 formula that returns 4 decimal places (.7501). I need to use only the first 2 decimal places (.75) for another calculation. I have tried roundup, and although the cell only display...

Copper Contributor

Mar 03, 2021

Jihad Al-Jarady I have a similar problem where I have a square root formula that has text and the answer in the cell. I would like to limit the decimal places in the final answer to 3 places.

This is my formula:

="DELTA H: "&SQRT(((C764-C29)^2)+((D764-D29)^2))

For example, the answer comes out as:

DELTA H: 0.0564003545403856

And I would much rather have the answer display as:

DELTA H: 0.056

Diamond Contributor

Mar 03, 2021

If to display you may use

="DELTA H: " & TEXT( SQRT(((C764-C29)^2)+((D764-D29)^2)), "#.###")

ddraeger_f-w
Copper Contributor
Sep 28, 2021
SergeiBaklan, @Victor207 , I came here looking for an answer to a similar issue using ="text"& [formula]. This solution worked, unless the result was an even number. In that case the cell came back with an extra "." at the end. So I found that instead of forcing the formula into a TEXT string with "#.###" to control the decimal places, a better solution could be to force the formula to a FIXED value with 3 decimals.
="DELTA H: " & FIXED( SQRT(((C764-C29)^2)+((D764-D29)^2)),3)

Or make it an integer with no decimals:
="DELTA H: " & FIXED( SQRT(((C764-C29)^2)+((D764-D29)^2)),-1)
Victor207
Copper Contributor
Mar 03, 2021
SergeiBaklan Awesome! It worked! One more question... when I inserted that formula into the cell it is not showing the leading 0 in the final result. For example the cell shows:
DELTA H: .056

Is it possible to show as:
DELTA H: 0.056
- SergeiBaklan
  Diamond Contributor
  Mar 03, 2021
  Victor207
  
  That could be 0.### as string for the format. You may google for custom number formats for more details.

Resources