Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? | | | |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with rounding? | Excel Discussion (Misc queries) | |||
Rounding Problem | New Users to Excel | |||
Rounding Problem | Excel Worksheet Functions | |||
Rounding Problem | Excel Discussion (Misc queries) | |||
Rounding off problem..! | Excel Worksheet Functions |