Spread sheet not adding right (rounding?)

[fatmommacat]

My spread sheet is not adding right Im wondering if it is a rounding problem. I need help but Im not too user friendly. =)

Example... my columns are $1.89 and below is $1.89 it will add it to $3.77 instead of $3.78

Im working on another report with a whole string of numbers added and still am a penny short.

If I right click on the cell thats wrong and go to format>it is set on currency> -$1234.00

Any ideas? Thanks in advance

[FJCC]

This can certainly be a result of rounding. I think it is very unlikely that Calc is miscalculating simple arithmetic, but you never know. There is no way to tell without seeing the actual numbers. If you can post the actual data, someone could take a look. Alternatively, you could increase the number of digits shown by the cell format to see if rounding is the source of your concern.

[kingfisher]

I don't get that result. 1.89 + 1.89 = 3.78. Tap the F11 key and check that the cells have default formatting.

I notice that you are using an old version of the software.

[jrkrideau]

I don't get that result. 1.89 + 1.89 = 3.78. Tap the F11 key and check that the cells have default formatting.

I notice that you are using an old version of the software.

Yes it sounds like the 1.89 is a formatted value not the actual one.

[Richarda44]

Rounding problems ony affect me when division sums make the units into fractions. To avoid this I always round the individual formulae before additions.

[kingfisher]

fatmommacat, we need to see the actual contents of the two cells you mention first, as well as know the format you are using for those cells.

The actual values appear in the input line; what displays in the cell is not necessarily the same.

[Dr4gonFly]

I apologize if raising forums from the dead is against the rules. It seems easier than creating the same thread topic again. I read through the entire thread and the question never really seems to be answered.

I'm experiencing the exact same issue using Calc today. The calculations are off by a penny. This is not a big deal for my situation but, none the less, it's something I would like to see fixed or at least understand why it's happening.

I'm adding, subtracting, and multiplying dollars and cents with a max of 2 decimal places for the cents. Division is never used in my spreadsheet.

One example of addition in my spreadsheet is $xxx.67 + $xxx.10 = $xxx.78. So the calculation is off by 1 penny using addition. It goes up.
Another example of addition in my spreadsheet is $xxx.68 + $xxx.73 = $xxx.40. Off by a penny again but this time it's loss of a penny.

2 comments about other posts in this thread.

"I don't get that result. 1.89 + 1.89 = 3.78." You don't get that result? Okay, I get exactly that result by just doing it in my head.

"The actual values appear in the input line; what displays in the cell is not necessarily the same." Okay, when I click the actual cell that performs my additions it does show the cell's row letter and column number in the "=" text box above the spreadsheet but it never shows the actual values apart from what it shows, literally, in the actual cell. So, how can I see the "hidden" true values of cells?

Thanks for reading!

[robleyd]

This is all to do with how computers store numbers and is not specific to Calc or OpenOffice. There is endless discussion of it on the Internet so I'll give you some links:

A search on this forum for rounding errors
Stack Overflow - https://stackoverflow.com/questions/950 ... al#9508558 which has some further links worth reading

[RusselB]

I'm adding, subtracting, and multiplying dollars and cents with a max of 2 decimal places for the cents. Division is never used in my spreadsheet.

Multiplication of two numbers that contain two decimals can return an answer with more than two decimals.
Eg: 1.25 x 2.37 = 1.7125
If the display is set for two decimals, you will see 1.25 in one cell, 2.37 in a second, and 1.71 in the third (product) cell.
If adding that 1.7125 to other numbers, it is is 1.7125 that is added, not the 1.71 that you see.

Thus, even though you state that division is never used, it is possible to get numbers that have more than two decimals, when using multiplication.
If you are adding or subtracting amounts that have more than two decimals, you can (obviously, imo) get a sum (when adding) or difference (when subtracting) that has more than two decimals.

[keme]

Another issue is that even plain entered numbers may be subject to rounding. No multiplication or division required. With two decimals, Calc can represent 3 real fractions exactly: 0.25, 0.5 and 0.75. All other fractions using two decimals will be stored as an approximate value. This approximation will be correct to 14 significant decimal digits, so it will not have any impact unless you have many significant digits and a long row of numbers.

Details are available through the links robleyd posted. Thought I'd mention it here anyway for completeness, since most other aspects of rounding issues have been mentioned.

25 years ago or so, a colleague of mine created a spreadsheet for recording time at work with flexible working hours (like you'd use for keeping track of overtime/comp.time). It worked fine, but after using it for nearly 5 years - probably between 2000 and 4000 time entries summarised - he found that the calculation was suddenly one minute off. That may have been my first real-world encounter with computer rounding errors.

[RusselB]

@keme I have to disagree with you regarding the number of real fractions that can be exactly displayed with two decimal points.
Any fraction that would have a divisor of 100 should (not saying does) be displayed correctly when using two decimal points.

[keme]

@keme I have to disagree with you regarding the number of real fractions that can be exactly displayed with two decimal points.
Any fraction that would have a divisor of 100 should (not saying does) be displayed correctly when using two decimal points.

Quite right! All such fractions are displayed exactly and correctly as entered. This is the external, visual representation.

My point was that what you see is not always exactly what you have. The reported issue is not about visual misrepresentation of manually entered data. It is about errors in calculations.

My comment was about how numbers are stored, the internal binary representation used for further calculations. Because of the high precision of numbers, this usually does not cause discernible error unless many calculations are performed in succession. With a single addition, the result will come out fine. Nevertheless, such a result should still be rounded to a sensible number of decimals before using it in further calculations, to prevent the accumulation of rounding errors.

The case of timesheets that I mentioned was a running sum. All input was a full time reference with date and time of day. Spreadsheet time data of this kind, with dates in our time and precision down to one minute, require 11 decimal digits of precision. With that, unhandled rounding errors might have introduced visible error after 500 operations or so if all entries were subject to rounding error and all errors went in the same direction ("worst case").