Articles on all things data...

There is a bug in Excel…

 November 16

by Andrew Richards

Excel gets maths wrong sometimes…

Do you use Excel regularly? And do you trust that if it says something’s true, then it’s probably true? Perhaps not in the area of global politics, or issues around faith and ethics, but you can rely on it when it comes to maths, right?

We’re not talking high-level, professor of mathematics stuff, either…

Take a look at this video, where we demonstrate the issue

We're talking about basic maths, that you could get right in your head. The sort of maths that allows you to check that you got your change right when you go into a shop.

It's quite well known that there's a rounding problem in the way CPUs handle fractions. If you have one-third of your cake, that would be about 0.3333333333 of the cake. And if three people each eat one third, there's nothing left. But if three people each eat 0.3333333333 of the cake, that doesn't, in decimal terms, add up to a whole cake - there'll be a few crumbs left. And the "floating point" calculation issue in processors is along the same lines.

But this issue isn't limited to tiny, 30th decimal place errors - because you'll often compare two results, and expect to get a simple yes or no when you ask "Are these the same".

This means any IF test that relies on the two values being the same will return the wrong answer. And then anything that happens as a result of that IF test will also be wrong.

Have you come across this in your own spreadsheets? What solution did you use to get around it?

We'd love to hear your thoughts in the comments below...

share this

Related Posts

Using RAG statuses with dates in Microsoft Excel

Advanced Excel conditional formatting video tutorial (part 2)

Advanced Excel conditional formatting video tutorial

Sign up now to be the first to hear about new articles!