"if you don't explicitly tell Excel which output format to use" - Actually, this is NOT about the output format. It is about storage. In most cases if you pick an output format with fewer decimal digits then rounding will end up pushing things back to the value you originally entered, but not always - and the number stored is still the "incorrect" number due to limits of 64-bit floating point binary values.

@manassehkatz The explicit declaration of an output format will limit the cases of "textual output doesn't look like textual input" into those areas, where a 64bit floatingpoint number is inexact by so much as to be visible in textual representation. These are not areas, where Excel is normally used.

@YoYoYonnY While Excel does not need to change the representation (which I did not say it would), it definitly does need to find a good internal representation. Since Excel is not a tool for scientific use, where such rounding errors have grave consequences, the choice of a 64bit floating point is often quite a good and practical one.

I'm pretty sure what accountants want is a decimal data type. Some programming languages have such a data type, and they are not nearly as slow as you claim.

@EugenRieck: actually, it's the other way around. Generally you don't care much about this kind of rounding errors in science, where experimental data is not that precise anyhow, while you'd want "perfect" calculations with decimals in accounting, regardless of the magnitude of the number.

@MatteoItalia Where was anyone talking about Experimental data? If you do e.g. any mechanical simulation, you want really good precision.

@kasperd Yep, you could store decimal numbers with fixed precision as a multiple of ten, i.e. $1.23 becomes 123. In this way, most operations stay the same (adding $1.23 + $4.56 = $5.79 is the same as adding 123 + 456 = 579, then adding the dot and dollar back when displaying). Floating point numbers do something like this too, though, however, they use multiples of two instead of ten. If I were to guess I would think most accounting software just uses floating point numbers, and rounds to the nearest decimal when displaying.

@kasperd Accountants don't care at all about decimal types. What they want, is that numbers with a max of ca. 11 digits before and 2 after the decimal point compute as they expect, and fast at that.

@EugenRieck Floating point doesn't do that but a decimal data type does.

@kasperd We will have to agree to disagree. I recommend you approach Microsoft to tell them they are doing it wrong.

@EugenRieck I found it much easier to just use another software vendor.

@kasperd - Which spreadsheet program have you found, that uses decimal types? That would be a very helpfull input!

@EugenRieck I don't use spreadsheets and I don't use Microsoft software either. I have however developed business software which needs to manipulate financial data, and I sure used decimal types for most of it. (For some code I even went as far as using a fraction datatype to guarantee complete accuracy of intermediate results.) A lot of data exchange happens in decimal datatypes (even if the application you are communicating with use some other representation internally).

@kasperd, Ah I understand - so you say your answer to a data representation question in Excel is "don't use Excel and don't use spreadsheets at all". That explains it.

@EugenRieck: double gives you good precision - pretty much every scientific calculation works perfectly fine with ~17 decimal digits of precision. Accounting data however needs infinite precision and no unexpected binary rounding.

@MatteoItalia But before you said, that the rounding errors of a normal (single) float don't matter - so which one is it now?

@EugenRieck That's not exactly how it was intended to be interpreted. What I say is if you are working with any sort of accounting/financial data you should probably be using a decimal data type. I think that's what the decimal data type was invented for. And I cannot give recommendations when it comes to spreadsheets because I happen to not be using those myself. Floating point is for when you need fast calculations and don't mind rounding errors. Fraction is for when you need exact representation of rational numbers. And decimal is an in-between covering the needs of financial applications.

@EugenRieck: I said that for scientific calculation this kind of rounding error is generally irrelevant, because there's plenty of precision (relative to the magnitude) anyhow, the quantities being manipulated are generally less precise to begin with, and the output is generally a smooth function of the input. Accounting data instead manipulates either discrete quantities, or decimal numbers that are expected to be summed exactly, down to the last digit. Sum 0.1 ten times, you'll get a result that is off by 1E-16; a scientist will say "whatever", an accountant will horrify.

@MatteoItalia - I have yet to see an accountant, who is horrified by an error of 1E-16. This is where I end this discussion as worthless.

It should be noted that it is impossible to have infinite decimal precision. At best if your calculation results in a rational number you could get an exact ratio as a result, but when converting it to decimal the best you're going to be able to do is pick an arbitrary fixed number of digits to round to.

A 64bit floating point number just uses a "fancy" representation that has something like 15-16 decimal digits of precision, except it stores the decimals in base-2 which doesn't always convert nicely to base-10. However the 15-16 digit precision is still in effect even in these not-so-nice cases.

Accountants are the main reason data types like C#'s Decimal came into existence. It's the scientists than can live with rounding errors. Science tends to be much more forgiving than law.

@Agent_L I beg to differ - I have personally run simulations, where a precision was required, that would be equivalent to less than 1/1000000 th of a cent of all money (real and virtual) in the world.