Oct 15 2017 02:31 PM
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 displays 2 decimal places (.75), Excel stills retains the remaining decimal places to the right (.__01, which corrupts my next calculation.
Is there a way to limit the accuracy of a returned number for a single cell or a range of cells without changing the accuracy of the entire worksheet.
Oct 15 2017 08:05 PM
Jim,,
could you show us your formula?
Oct 19 2017 11:30 AM - edited Oct 19 2017 11:47 AM
Hi Jim,
You can use the TRUNC( )function with the required format in the required cell or cells.
Example:
if you are using =SUM(A1:A2) to get the sum of the Cell A1:A2
You can add the TRUNC( ) function with the following format to limit it to 2 numbers after the decimal without roundup the number.
=TRUNC(SUM(A1:A2),2)
Oct 20 2017 03:19 AM
IMHO, TRUNC does exactly the same as ROUNDDOWN if use with same number of digits
Oct 20 2017 09:25 AM
Mar 03 2021 05:08 AM
@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
Mar 03 2021 06:00 AM
If to display you may use
="DELTA H: " & TEXT( SQRT(((C764-C29)^2)+((D764-D29)^2)), "#.###")
Mar 03 2021 06:28 AM
@Sergei Baklan 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
Mar 03 2021 07:02 AM
That could be 0.### as string for the format. You may google for custom number formats for more details.
Sep 28 2021 03:56 PM
@Sergei Baklan, @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)