Hi - Dave here.
Happy Friday!
Have you ever had a formula like =A1=B1 return FALSE, even though the numbers in A1 and B1 are the same?
This issue can be caused by a "floating-point error", and it can trip up formulas, checks, and even conditional formatting rules.
You can see an example below, where we subtract the numbers in column C from those in column B, then compare to expected results in column F.
Most of the formulas return FALSE, even though we would expect the results to be TRUE. Why? This is an example of floating-point errors in Excel causing trouble:
[
Download the workbook and read the full explanation]
Floating-point errors happen because Excel uses binary to store numbers, which can't represent some decimal values exactly. This can cause tiny problems that mess up your worksheets, wasting time and making you feel crazy. Luckily, they are easy to manage. In this week’s article, I break it all down:
Click the link above to read the article and download the workbook examples.
Excel formulas
We maintain a list of over 1000 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
The Exceljet newsletter is free and sent weekly on Fridays. Each week, I take a detailed look at how to solve a specific problem in Excel. You can sign up on our home page.