Rounding Problem

[kleivakat]

I have several formulas that are contributing to a final price for an item.
The result of last formula is that 2 x $610.60 = $1,220.21, and I can't find
the error. There is one related cell that uses the ROUND function, and I
wonder if that is contributing to the error. The contents of the related
cells are:

=ROUND(IF(M88>0,K88*(1+M88),0),2)+N88

where M88 is a percentage mark-up (11.0% formatted as a percentage with 2
decimal points.)
where K88 is =SUM(I88:J88) - I88 and J88 are both numeric values
where N88 is a numeric value

I have a feeling that somewhere in one of my formulas there is a number
that's reading many more decimals that two, therefore 610.60 x 2 is larger
than 1,220.20. I checked all cells, and none have more than 2 decimals.

Any ideas where I'm getting the extra penny from?

 

[Niek Otten]

<I checked all cells, and none have more than 2 decimals>

They probably have, you just can't see them

Look here:

http://www.mcgimpsey.com/excel/pennyoff.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have several formulas that are contributing to a final price for an item.
| The result of last formula is that 2 x $610.60 = $1,220.21, and I can't find
| the error. There is one related cell that uses the ROUND function, and I
| wonder if that is contributing to the error. The contents of the related
| cells are:
|
| =ROUND(IF(M88>0,K88*(1+M88),0),2)+N88
|
| where M88 is a percentage mark-up (11.0% formatted as a percentage with 2
| decimal points.)
| where K88 is =SUM(I88:J88) - I88 and J88 are both numeric values
| where N88 is a numeric value
|
| I have a feeling that somewhere in one of my formulas there is a number
| that's reading many more decimals that two, therefore 610.60 x 2 is larger
| than 1,220.20. I checked all cells, and none have more than 2 decimals.
|
| Any ideas where I'm getting the extra penny from?
|
|
|

 

[joeu2004]

I have several formulas that are contributing to a final price for an item.
The result of last formula is that 2 x $610.60 = $1,220.21, and I can't find
the error.

I presume you mean 1221.21. (2*610.60 = 1221.20, not 1220.20.)

There is one related cell that uses the ROUND function, and I
wonder if that is contributing to the error. The contents of the related
cells are:
=ROUND(IF(M88>0,K88*(1+M88),0),2)+N88

Try changing that to

=ROUND(IF(M88>0,K88*(1+M88),0)+N88, 2)

I have a feeling that somewhere in one of my formulas there is a number
that's reading many more decimals that two, therefore 610.60 x 2 is larger
than 1,220.20. I checked all cells, and none have more than 2 decimals.

I presume you mean that all the cells __display__ only 2 decimals
places. But generally, what you see is __not__ what you have. The
underlying value might have many more decimal places. For example, if
you multiply 1220.50 by 0.25, the actual value is probably 305.125,
even if you display 305.13.

Any ideas where I'm getting the extra penny from?

Besides the above example, it could be anywhere. You might ameloriate
the problem by setting the Calculation option Precision As Displayed.
But that affects all (subsequent) calculation, and it that might have
unexpected consequences.
Moreover, it might not "fix" all problems.

If you be sure to round for all cell values to pennies, I suspect you
will not see any inconsistencies with your manual calculations based
on the displayed cell values.

But note that rounding values can result in other inconsistencies.
For example, if you round the result of PMT(), as you should, the last
payment of a long-term loan is usually different from the other
payments.

 

[kleivakat]

Thanks to both of you. I got it to work by adding a ROUND function to the
cell. That took care of my immediate problem.

If I change the calculation preferences in tools/options/calculations, will
that change them in all worksheets whenever they are opened, or only in newly
created worksheets. I'm hesitating to change it (although I thiink it will
be the best long-term solution and I can't think when I might run into a
problem by making the change) but don't want to mess up current spreadsheets
if they will change whey I re-open them.