Inaccurate calculations when one value references another cell with =$?$? input

[PIF]

I have searched this and I have tried everything suggested via what I have found, and I still can’t get Calc to give me an accurate =product for two columns that equate to $.10 x 6.

It should be $.60 but it keeps giving me $.59. This is just one error I’ve found thus far, there are others. The weird thing is, if I change the 6 number to anything 1 thru 4, it gives me the right calculation??? A similar cell is, in essence, $.11 x 6. The answer I get is $.68??? It is calculating from a =$?$? formula. When I simply replace the =$?$? formula with a straight currency number, it corrects the calculation.

I have tried:

=sum(E26*F26)

=product(E26*F26)

=product(E26,F26)

These will be constantly fluctuating formulas based on cost of product and how many servings I get out of each batch produced. So I really need to use the =$?$? formula (or something else) to pull that data.

[EarnestAl]

Currency format rounds the displayed number but calculates using the underlying number.

You could use the ROUND function when calculating the individual item cost from the bulk cost so that $0.10 really is that and not $0.995 or something. Sample file is better than screenshot

Or you could set Calc to use number as displayed. I don’t like this from a personal point of view, as it is introducing an error to correct an error of perception.

[ajlittoz]

This is a common issue with any spreadsheet program. Prices are entered using floating point numbers. These numbers are never accurate because they can’t represent exactly any value (only some particular values correspond to “native” members of the set).

The correct way of fixing the issue would be to make all your computations in integer arithmetic with the smallest accounting unit. This is usually cent but I have met cases where this unit is 1/1000 of the currency. Then you format the cell to give the illusion of your standard currency with its subdivision.

Of course, this puts an enormous burden on your spreadsheet design and you can qualify it of not being user-friendly.

Then, the common mitigation technique is to force rounding wherever you compute something, be it a simple addition though addition is not much sensitive to this kind of glitch. Review all your “computing” formulas to transform them into =ROUND(<your_computation>;2). “Copy” formulas can remain the same, e.g. =A5, because they don’t involve computation.

[koyotak]

I see in row [34] the values 0.13 × 8 is never 1.00, correctly 1.04. So try to set all green calculatet U$-values to standard to see all decimal digits. (sorry for my bad englisch).

[erAck]

See Frequently asked questions - Calc: Accuracy problem - The Document Foundation Wiki.
Use ROUND(...;2) with calculation results, as ajlittoz said.

Btw, using SUM() in =SUM(E26*F26) is superfluous, that sums only the one result of E26*F26, use =E26*F26 instead. Same for =PRODUCT(E26*F26).

[keme1]

About rounding in this kind of calculations:

• Always round to the penny every entry that signifies a transaction (bookkeeping entry, payment, or other figure signifying actual offset/transfer of funds.)
• Keep full precision for intermediate figures, and just be aware that the number shown is an approximation.

With a large count of small entities, you may well have a unit cost of less than half a penny. If you round on that step, the cost disappears. You don’t want that.