Limiting number of decimal places in a returned formula

%3CLINGO-SUB%20id%3D%22lingo-sub-116730%22%20slang%3D%22en-US%22%3ELimiting%20number%20of%20decimal%20places%20in%20a%20returned%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-116730%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20an%20Ex%202010%20formula%20that%20returns%204%20decimal%20places%20(.7501).%20I%20need%20to%20use%20%3CSTRONG%3Eonly%3C%2FSTRONG%3E%20the%20first%202%20decimal%20places%20(.75)%20for%20another%20calculation.%3C%2FP%3E%3CP%3EI%20have%20tried%20roundup%2C%20and%20although%20the%20cell%20only%20displays%202%20decimal%20places%20(.75)%2C%20Excel%20stills%20retains%20the%20remaining%20decimal%20places%20to%20the%20right%20(.__01%2C%20which%20corrupts%20my%20next%20calculation.%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20limit%20the%20accuracy%20of%20a%20returned%20number%20for%20a%20single%20cell%20or%20a%20range%20of%20cells%20without%20changing%20the%20accuracy%20of%20the%20entire%20worksheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-116730%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-118714%22%20slang%3D%22en-US%22%3ERe%3A%20Limiting%20number%20of%20decimal%20places%20in%20a%20returned%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-118714%22%20slang%3D%22en-US%22%3ESergei%2C%3CBR%20%2F%3EYou%20are%20absolutely%20right.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-118628%22%20slang%3D%22en-US%22%3ERe%3A%20Limiting%20number%20of%20decimal%20places%20in%20a%20returned%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-118628%22%20slang%3D%22en-US%22%3E%3CP%3EIMHO%2C%20TRUNC%20does%20exactly%20the%20same%20as%20ROUNDDOWN%20if%20use%20with%20same%20number%20of%20digits%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-118487%22%20slang%3D%22en-US%22%3ERe%3A%20Limiting%20number%20of%20decimal%20places%20in%20a%20returned%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-118487%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jim%2C%3CBR%20%2F%3EYou%20can%20use%20the%20%3CSPAN%3ETRUNC(%20)%3C%2FSPAN%3Efunction%20with%20the%20required%20format%20in%20the%20required%20cell%20or%20cells.%3CBR%20%2F%3EExample%3A%3CBR%20%2F%3Eif%20you%20are%20using%26nbsp%3B%20%3DSUM(A1%3AA2)%20to%20get%20the%20sum%20of%20the%20Cell%20A1%3AA2%3C%2FP%3E%3CP%3EYou%20can%20add%20the%20%3CSPAN%3ETRUNC%3C%2FSPAN%3E(%20)%20function%20with%20the%20following%20format%20to%20limit%20it%20to%202%20numbers%20after%20the%20decimal%20without%20roundup%20the%20number.%3CBR%20%2F%3E%3DTRUNC(%3CSTRONG%3ESUM(A1%3AA2)%3C%2FSTRONG%3E%2C2)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-116756%22%20slang%3D%22en-US%22%3ERe%3A%20Limiting%20number%20of%20decimal%20places%20in%20a%20returned%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-116756%22%20slang%3D%22en-US%22%3E%3CP%3EJim%2C%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ecould%20you%20show%20us%20your%20formula%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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.

 

 

8 Replies

Jim,,

 

could you show us your formula?

 

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)

IMHO, TRUNC does exactly the same as ROUNDDOWN if use with same number of digits

Sergei,
You are absolutely right.

@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

 

@Victor207 

If to display you may use

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

@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

 

@Victor207 

That could be 0.### as string for the format. You may google for custom number formats for more details.