cut off number in excel cell vs. rounding

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

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

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

=VALUE(FIXED(A1,2))

Copy down you range as needed.

HTH

Steve

Use the Trunc() function.

=Round(12.2156,2)
Returns 12.22

=Trunc(12.2156,2)
Returns 12.21
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
<[email protected]> wrote in message
news:[email protected]...
> 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
>

Round them down

=ROUNDDOWN(A1,2)

--
HTH

Bob Phillips

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

<[email protected]> wrote in message
news:[email protected]...
> 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
>

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?

"[email protected]" wrote:

> 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
>
>

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


HTH

Steve