There is a bug in Excel…

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…

By | 2018-11-16T10:50:19+00:00 November 16th, 2018|Microsoft Excel, Microsoft Excel tips|0 Comments

About the Author:

Andrew Richards is the Managing Director of TheIT Service. His background in training goes back to 2000, and he was involved in IT networking and support beyond that. Now he spends his time living and breathing the GDPR (fun!) and building databases. When not doing these things, he can be found attempting to train his various sheep and chickens.

Leave A Comment

Get in touch now!
Close X

Get in touch today!

Call us now on 020 3397 1333, or pop your details here and we'll get back to you.

Your Name *

Your Email *

Your Phone *

Your Company *

How can we help?