Speaking of Excel, here’s a fun little experiment into the nature of binary numbers and rounding errors.
Start with some number and add a fraction like =A1+(1/3) to it. In the cell below, add that same fraction to the previous one. Copy this formula downwards and watch the numbers grow. After about 50 rows, you’ll have a number that looks like something specific, such as 71, but it isn’t exactly. There’s a sneaky rounding error hidden in there. The actual number is very close to the one displayed, but not exactly what you think it is.
If you’re using IF statements or XLOOKUP with numbers like this, you’ll run into some perplexing errors. If I recall correctly, you can even test the number with =A50=71, which will return TRUE but the xlookup still fails. It’s been a while since I tested this one, but I remember it being really weird in all sorts of unexpected ways. It’s weekend, so I’m not touching my work computer today.
You just need to know that a long series of fractions causes weird binary rounding errors to happen behind the scenes. Adding a series of whole numbers and neat decimal numbers was perfectly ok though.
Also, trying to explain this to some coworkers won’t be worth the effort.
Speaking of Excel, here’s a fun little experiment into the nature of binary numbers and rounding errors.
Start with some number and add a fraction like =A1+(1/3) to it. In the cell below, add that same fraction to the previous one. Copy this formula downwards and watch the numbers grow. After about 50 rows, you’ll have a number that looks like something specific, such as 71, but it isn’t exactly. There’s a sneaky rounding error hidden in there. The actual number is very close to the one displayed, but not exactly what you think it is.
If you’re using IF statements or XLOOKUP with numbers like this, you’ll run into some perplexing errors. If I recall correctly, you can even test the number with =A50=71, which will return TRUE but the xlookup still fails. It’s been a while since I tested this one, but I remember it being really weird in all sorts of unexpected ways. It’s weekend, so I’m not touching my work computer today.
You just need to know that a long series of fractions causes weird binary rounding errors to happen behind the scenes. Adding a series of whole numbers and neat decimal numbers was perfectly ok though.
Also, trying to explain this to some coworkers won’t be worth the effort.