Rounding in Excel

[Al Charbonneau]

I have a table that has numbers "hard coded" back to another spreadsheet in
the workbook. Excel says that the numbers (961+73-60=974) in the table.
However, entered separately without decimals, Excel says (961+73-60=975). I
am concerned that wehn people read my table, they will see that the numbers
don't add up. Is there any way short of re-entering whole numbers to solve
this problem.


Thanks in advance.

Al Charbonneau

 

[Bernard Liengme]

Bit confusing; we need more info
What formula are you using?
What at eh numbers on the other sheet?
You say "However, entered separately without decimals, Excel says
(961+73-60=975)"
This is clearly wrong (1+3-0 = 4)
best wishes

 

[JoeU2004]

Is there any way short of re-entering whole numbers to solve
this problem.

Taking some things for granted, I would say a qualified "yes". But the
consequences could be dire.

First, make a copy of the xls file as back-up. The following suggestion
might cause undesireable changes that are irreversible.

Then be sure the format of the cells is a numeric format other than General,
for example Number with zero decimal places.

Finally, set the option "Precision as displayed" (PAD). In Excel 2003, that
is under Tools > Options > Calculation.

Normally, I do not like to use PAD. It permanently changes any constants in
non-General-formatted cells. Moreover, the option affects all worksheets in
the xls file. The consequences can have pervasive and unanticiplated
effects on all calculations.

But you asked for "any way short of re-entering" data. That is the only way
I know that meets your criterion.

IMHO, the better solution is to modify some formulas to use the ROUND
function. This is a surgical solution that is less likely to have untoward
consequences.

Excel says that the numbers (961+73-60=974) in the table.
However, entered separately without decimals, Excel says (961+73-60=975).

I presume you have that backwards. Obviously, when performing the
arithmetic "separately without decimals", the result is 974. I presume the
Excel result is 975. As you seem to understand, the problem would be
because the underlying values have decimal fractions, but Excel is
displaying rounded values; for example, 961.4, 73.4 and 59.5.


----- original message -----