Discussion Re: Limiting number of decimal places in a returned formula in Excel
https://techcommunity.microsoft.com/t5/excel/limiting-number-of-decimal-places-in-a-returned-formula/m-p/118628#M2522
<P>IMHO, TRUNC does exactly the same as ROUNDDOWN if use with same number of digits</P>Fri, 20 Oct 2017 10:19:57 GMTSergei Baklan2017-10-20T10:19:57ZLimiting number of decimal places in a returned formula
https://techcommunity.microsoft.com/t5/excel/limiting-number-of-decimal-places-in-a-returned-formula/m-p/116730#M2448
<P>I have an Ex 2010 formula that returns 4 decimal places (.7501). I need to use <STRONG>only</STRONG> the first 2 decimal places (.75) for another calculation.</P><P>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.</P><P>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.</P><P> </P><P> </P>Sun, 15 Oct 2017 21:31:06 GMThttps://techcommunity.microsoft.com/t5/excel/limiting-number-of-decimal-places-in-a-returned-formula/m-p/116730#M2448Jim Higbee2017-10-15T21:31:06ZRe: Limiting number of decimal places in a returned formula
https://techcommunity.microsoft.com/t5/excel/limiting-number-of-decimal-places-in-a-returned-formula/m-p/116756#M2449
<P>Jim,,</P><P> </P><P>could you show us your formula?</P><P> </P>Mon, 16 Oct 2017 03:05:23 GMThttps://techcommunity.microsoft.com/t5/excel/limiting-number-of-decimal-places-in-a-returned-formula/m-p/116756#M2449Detlef Lewin2017-10-16T03:05:23ZRe: Limiting number of decimal places in a returned formula
https://techcommunity.microsoft.com/t5/excel/limiting-number-of-decimal-places-in-a-returned-formula/m-p/118487#M2517
<P>Hi Jim,<BR />You can use the <SPAN>TRUNC( )</SPAN>function with the required format in the required cell or cells.<BR />Example:<BR />if you are using =SUM(A1:A2) to get the sum of the Cell A1:A2</P><P>You can add the <SPAN>TRUNC</SPAN>( ) function with the following format to limit it to 2 numbers after the decimal without roundup the number.<BR />=TRUNC(<STRONG>SUM(A1:A2)</STRONG>,2)</P>Thu, 19 Oct 2017 18:47:41 GMThttps://techcommunity.microsoft.com/t5/excel/limiting-number-of-decimal-places-in-a-returned-formula/m-p/118487#M2517Jihad Al-Jarady2017-10-19T18:47:41ZRe: Limiting number of decimal places in a returned formula
https://techcommunity.microsoft.com/t5/excel/limiting-number-of-decimal-places-in-a-returned-formula/m-p/118628#M2522
<P>IMHO, TRUNC does exactly the same as ROUNDDOWN if use with same number of digits</P>Fri, 20 Oct 2017 10:19:57 GMThttps://techcommunity.microsoft.com/t5/excel/limiting-number-of-decimal-places-in-a-returned-formula/m-p/118628#M2522Sergei Baklan2017-10-20T10:19:57ZRe: Limiting number of decimal places in a returned formula
https://techcommunity.microsoft.com/t5/excel/limiting-number-of-decimal-places-in-a-returned-formula/m-p/118714#M2523
Sergei,<BR />You are absolutely right.Fri, 20 Oct 2017 16:25:00 GMThttps://techcommunity.microsoft.com/t5/excel/limiting-number-of-decimal-places-in-a-returned-formula/m-p/118714#M2523Jihad Al-Jarady2017-10-20T16:25:00ZRe: Limiting number of decimal places in a returned formula
https://techcommunity.microsoft.com/t5/excel/limiting-number-of-decimal-places-in-a-returned-formula/m-p/2181610#M91498
<P><LI-USER uid="87534"></LI-USER> 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.</P><P> </P><P>This is my formula:</P><P>="DELTA H: "&SQRT(((C764-C29)^2)+((D764-D29)^2))</P><P> </P><P>For example, the answer comes out as:</P><P>DELTA H: 0.0564003545403856</P><P> </P><P>And I would much rather have the answer display as:</P><P>DELTA H: 0.056</P><P> </P>Wed, 03 Mar 2021 13:08:56 GMThttps://techcommunity.microsoft.com/t5/excel/limiting-number-of-decimal-places-in-a-returned-formula/m-p/2181610#M91498Victor2072021-03-03T13:08:56ZRe: Limiting number of decimal places in a returned formula
https://techcommunity.microsoft.com/t5/excel/limiting-number-of-decimal-places-in-a-returned-formula/m-p/2181708#M91506
<P><LI-USER uid="984990"></LI-USER> </P>
<P>If to display you may use</P>
<LI-CODE lang="excel">="DELTA H: " & TEXT( SQRT(((C764-C29)^2)+((D764-D29)^2)), "#.###")</LI-CODE>Wed, 03 Mar 2021 14:00:19 GMThttps://techcommunity.microsoft.com/t5/excel/limiting-number-of-decimal-places-in-a-returned-formula/m-p/2181708#M91506Sergei Baklan2021-03-03T14:00:19ZRe: Limiting number of decimal places in a returned formula
https://techcommunity.microsoft.com/t5/excel/limiting-number-of-decimal-places-in-a-returned-formula/m-p/2181750#M91509
<P><LI-USER uid="521"></LI-USER> 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:</P><P>DELTA H: .056</P><P> </P><P>Is it possible to show as:</P><P>DELTA H: 0.056</P><P> </P>Wed, 03 Mar 2021 14:28:01 GMThttps://techcommunity.microsoft.com/t5/excel/limiting-number-of-decimal-places-in-a-returned-formula/m-p/2181750#M91509Victor2072021-03-03T14:28:01ZRe: Limiting number of decimal places in a returned formula
https://techcommunity.microsoft.com/t5/excel/limiting-number-of-decimal-places-in-a-returned-formula/m-p/2181850#M91514
<P><LI-USER uid="984990"></LI-USER> </P>
<P>That could be <STRONG>0.###</STRONG> as string for the format. You may google for custom number formats for more details.</P>Wed, 03 Mar 2021 15:02:51 GMThttps://techcommunity.microsoft.com/t5/excel/limiting-number-of-decimal-places-in-a-returned-formula/m-p/2181850#M91514Sergei Baklan2021-03-03T15:02:51ZRe: Limiting number of decimal places in a returned formula
https://techcommunity.microsoft.com/t5/excel/limiting-number-of-decimal-places-in-a-returned-formula/m-p/2793555#M115809
<P><LI-USER uid="521"></LI-USER>, <A href="https://techcommunity.microsoft.com/t5/user/viewprofilepage/user-id/984990" target="_blank">@Victor207</A><SPAN> , 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. </SPAN> </P><P>="DELTA H: " & FIXED( SQRT(((C764-C29)^2)+((D764-D29)^2)),3)</P><P> </P><P>Or make it an integer with no decimals:</P><P>="DELTA H: " & FIXED( SQRT(((C764-C29)^2)+((D764-D29)^2)),-1)</P>Tue, 28 Sep 2021 22:56:39 GMThttps://techcommunity.microsoft.com/t5/excel/limiting-number-of-decimal-places-in-a-returned-formula/m-p/2793555#M115809ddraeger_f-w2021-09-28T22:56:39Z