help - too many decimal places...

[RTE]

Formula result, then copy>paste special/values returned 3.40660841070453

I need to change cell data to 3.40.

Format > decimals (2) does not change data to what I need.

Any ideas on how to reduce the data to only 2 decimal places?

Thanks.

 

[repairman615]

Try the formula with the
round(original formula here,2) function

 

[RTE]

Thanks - but any ideas if I only have access to the data not the formula?

 

[repairman615]

Is it possible to use a formula?

I see that using:

Format>Number>(2) decimal place

rounds the number to 3.41

I know of one way to get 3.40=3.4 from 3.40660841070453

=ROUNDDOWN(3.40660841070453,2)

I am not sure it is possible through excel options or formats...
Hope that Helps

 

[indiantrix]

If you want to have the cell contain, in this example, 3.40 then use the following formula on the data: =TRUNC(A1,2) where A1 is the cell containing 3.40660841070453. On the other hand, if you want to round the number to two decimal places, then use this formula:
=ROUND(A1,2) which will yield 3.41. Hope this can help you.
Larry.

 

[mjrofra]

maybe you can select your data an run a macro like this one (remenber to make a copy of your data before you run the macro).

Sub test()
    With Selection
        .Value = Evaluate("trunc(" & .Address & ",2)")
        .NumberFormat = "#.00"
    End With
End Sub

 

[mjrofra]

Thanks - but any ideas if I only have access to the data not the formula?

try the macro i suggest :D o use the TRUNC formula in another column and then copy - paste the result as values.

 

[diannesdesigns]

try the macro i suggest :D o use the TRUNC formula in another column and then copy - paste the result as values.

How about if you have a range of values and you want them truncated?

(57,604.11000)
(1,380.36720)
31,564.93000
23,006.12000
1,380.36720
3,033.06000

to

(57,604.11)
(1,380.37)
31,564.93
23,006.12
1,380.37
3,033.06
0.00

????any suggestions

 

[repairman615]

Look like you could give this a shot to do that:

Format the cells to two decimal places.

right click/format cells

under number tab select number and you will see a decimal place selection. note this does standard rounding as per your example.

 

[diannesdesigns]

I was after the value to 2 decimal places also...

I saw this post here>http://www.mrexcel.com/forum/showpost.php?p=1753168&postcount=4

but was hoping for a Macro like the one in this thread...