cut off number in excel cell vs. rounding

[marius.coleraine]

Dear Reader,

I have got following simple problem that seems to be hard to solve:

I have an AS 400 system calculation that adds $ amounts on the
condition that all decimal places bigger than 2 are cut off. Finally
the total of all these numbers is calculated.

Now these numbers are read out into excel but with their full length of
decimal places which is often more than 2 decimal places. If I round
these numbers the total as well as each number differs from the total
cell number I get when every decimal place bigger than 2 is cut off.

The only way to fix that so far is deleting the decimal places of each
number manually. That took me hours.

Isn't there any trick to shorten this "waste of time"?

Thanks

 

[SteveG]

Say your values are in A:A. Insert a column to the right. Select you
range of values. Go to Data>Text to Columns. Select Fixed Width i
step one. In Step two insert a break after your second decimal place.
Click Next and Finish. Delete column B where your extra decimal plac
values are.

If you'd rather use a formula you could use,

=VALUE(FIXED(A1,2))

Copy down you range as needed.

HTH

Stev

 

[Ragdyer]

Use the Trunc() function.

=Round(12.2156,2)
Returns 12.22

=Trunc(12.2156,2)
Returns 12.21

 

[Bob Phillips]

Round them down

=ROUNDDOWN(A1,2)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Can you give us a sample of the actual data? Do the numbers have a variety
of decimals and a variety of digits before the decimal?

 

[SteveG]

Disregard my formula. FIXED still rounds up. TRUNC or ROUNDDOWN woul
be what you are looking for. Sorry for the erroneous post. The Tex
to Columns solution would work as well.


HTH

Stev