LibreOffice Calc can't even add decimals to 2dp accurately

edit

A spreadsheet is often used for finances

Correct

and for people with much larger sums than me

Correct

yet it can't add accurately even small amounts

Wrong.

It can accurately add some kinds of small values, namely integers and any fraction which can be reduced to powers of two. This is because the internal data types use purely binary positional digits, which can only represent powers of two. This is perfectly analogous to the decimal system, which can accurately represent only fractions which can be broken down to powers of 2 and 5. For financial transactions, it is customary to impose the two decimals constraint, which means that only 4 fractions can be represented exactly in binary form (0.0, 0.25, 0.5, 0.75). All the 96 other allowed decimal fractions suffer from some tiny rounding error when stored in a spreadsheet.

You say that you understand the principle of floats, so I won't elaborate further. A double is just a higher precision float, so the same principle applies.

why use a datatype which isn't good for the job

The datatype is not the problem. Spreadsheets select data type automatically. They are simple tools for the average user. To work efficiently, the most efficient data type has been chosen. Any spreadsheet user using such a tool for important work should know the tool's limitations and use the means provided to avoid error. Requiring users to predefine data types based on the nature of their data would be asking for trouble. Automatic selection between numerical data types would be asking for even more trouble.

is there way to force a better data type

Yes, but not in any spreadsheet application that I know of. Several databases and programming languages support some variety of "decimal" data type, which will represent decimal fractions exactly as typed. Whether this is "better" is a matter of discussion. When you use data types not handled by CPU internals, there will be significant tradeoffs. Calculations will be slower (at a factor from 3 to 100, depending on platform and precision), and you need more bits to have numbers handling the same range and precision.

I have really got to start incorporating ROUND into things?

Yeah! You got this.

The proper way in a binary context to deal with "artificial fractions" imposed by our number systems is to use rounding functions. They will not eliminate rounding errors, but can negate the effect of accumulated rounding errors. For decimal rounding, like in financial transactions which usually are "to the penny", ROUND(x;2) should be present far more frequently than is the case.

Likewise if you use spreadsheet time values for worker timesheets, MROUND(x;1/1440) will strip off accumulated errors and keep times "to the minute". For astronomical and other angle measures, MROUND(x;1/3600) will keep angles "to the arc second".

Start rounding your results to a sensible precision. Begin right now. Your life will be so much better ... (more)