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

cut off number in excel cell vs. rounding
 

Say your values are in A:A. Insert a column to the right. Select your
range of values. Go to DataText 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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=562206

cut off number in excel cell vs. rounding
 

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 !
---------------------------------------------------------------------------
wrote in message
ups.com...
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

cut off number in excel cell vs. rounding
 

Round them down

=ROUNDDOWN(A1,2)

--
HTH

Bob Phillips

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

wrote in message
ups.com...
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

cut off number in excel cell vs. rounding
 

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?

" 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

cut off number in excel cell vs. rounding
 

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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=562206