Trim Decimals

[aarellano]

I have a spread sheet that I imported from an as400db. some of the fields have up to 9 decimals. When I form mat the fields it looks ok but is there a way to completly trim or reduce the number of decimals???

 

[AnotherHiggins]

do you want to do away with all the decimals?

So if you start with 1.2.3.4.5, you want to convert that to 12345?

If so, try
[tab]=Substitute(A1, ".", "")

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[GSCaupling]

Do you mean decimals or decimal places (e.g., 55.123456789)?

 

[aarellano]

sorry about that, I meant decimal places .809808080808080 to .80

 

[AnotherHiggins]

Ah, gotcha.

You can control how many are displayed by reformatting the cell, but that doesn't change the actual value.

To change the value,
[tab]=Round(A1, 2)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[combo]

If you need just to cut decimal places as in your example, the ROUNDDOWN function will do it.

combo

 

[MakeItSo]

Or
yourvalue= cdbl(Cint(yourvalue*100)/100)

;-)

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[GlennUK]

Or use the FLOOR function,

=FLOOR(.809808080808080,0.01)

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[GSCaupling]

Also be aware of the "Precision as Displayed" option available under Options / Advanced (Listed under "When calculating this workbook" in Excel 2007).

If you check this option, calculations will be based on the visible decimal places, not the actual figure that has nine decimal places.

 

[SkipVought]

If you want to actually truncate the value to 2 places...
[tt]
=INT(A1*10^2)/10^2
[/tt]
or to make it a bit more flexible, using Z1 as the cell for the number of places...
[tt]
=INT(A1*10^Z1)/10^Z1
[/tt]
then just change the value in Z1 to whatever number of places you want to see.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

oops, should have an ABSOLUTE reference...
[tt]
=INT(A1*10^$Z$1)/10^$Z$1
[/tt]



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

Actually there's a truncate function:

[tab]=Trunc(A1, 2)
will return 10.21 if A1 contains 10.21999999

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[aarellano]

Thank you all so very much. With all your help I was able to accomplish what I needed to do.

I really appreciate it.!!!!

 

[SkipVought]

There I go, making things more difficult than they need to be!

Go John!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}