View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
joeu2004 joeu2004 is offline
external usenet poster
 
Posts: 2,059
Default Rounding Problem

On Dec 5, 10:45 am, kleivakat
wrote:
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 a
=ROUND(IF(M880,K88*(1+M88),0),2)+N88


Try changing that to

=ROUND(IF(M880,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.