I’m trying to increment a number by .00018 but after 50 or so iterations it gets increased by .000180000001. so that after a couple of hundred iterations it is off by .000000000005. I need each iteration because it will be used by another program, and occasionally the answer will have to be adjusted for unforeseeable reasons (might need to be .000175 sometimes. I would like to keep it in decimal form for ease of coping and pasting, does anyone know why this happens and how to fix it? My formula I repeat is A1+.00018, A2+.00018…
Why? Because 0.000018 can’t be exactly represented in a binary system. See Floating-Point Expressions Do Not Compare as Equal or Why are my numbers adding up wrong?
That is why you are advised when making a list of sequential numbers to drag down or fill series. Not to do =A1+1.
If A1 contains the value 1, then in A2 enter 1.000018, in A3 enter 1.000036. Select A2 & A3 and drag down. On row 2001 I see 1.036000000000000 for drag fill, while for adding incrementally I get 1.03600000000015
Interesting. Didn’t know that. Great answer.
… and even drag-fill, although using a better algorithm of adding a multiple of increment to the initial value, and also a “smart” algorithm of finding the increment itself by rounding the difference of two starting values (having own floating-point error from negation), has its own errors (see e.g. tdf#141970). So it’s even better to use Fill Series, which enters the increment directly, and doesn’t need to calculate it from initial values.
Well that explains it, sadly can’t do a drag down because I need the values to update frequently. Thank you.
Then you could use something like =$A$1+0.00018*(ROW()-1)
… I need the values to update frequently ,
The proper way to handle repeating rounding errors so they don’t accumulate, is to round each “transaction point” or incremental step. You need to round to a sensible number of decimals. The error happens at the “resolution limit” of Calc’s numerical precision, which is somewhere around the 15th to 17th significant decimal digit. If you know that you will never need more than 6 decimals, rounding at that point makes sense. If your numbers may accumulate beyond 9 digits (billions), the sixth decimal will approach the resolution limit anyway, where rounding errors at 6th decimal cannot be reliably eliminated.
A few scenarios:
- Entered increments will never have more than 7 decimals:
=ROUND(<your calculation>;7) - You need to allow as many decimals as possible without accumulating rounding errors, and your calculation result will never exceed 10
=ROUND(<your calculation>;14)
If this is not sufficient for your needs, a spreadsheet is not the right tool. Use software with higher precision.