Rounding Problem
 

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 a

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

Rounding Problem
 

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

They probably have, you just can't see them

Look he

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"kleivakat" wrote in message ...
|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 a
|
| =ROUND(IF(M880,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?
|
|
|

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.

Rounding Problem
 

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.


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

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?