Excel is not calculating correctly

[Guy60]

A very simple spreadsheet

All cells are set to Number format with 2 decimal points
Column A
Row1 1.60

Row2  2.80

Row3 SUM(A1:A2) = 4.40

Row4 (type) 4.40

Row5 =IF(A3-A4=0,0,"Error") = 0.00

Same as above but set the A1 cell to 91.60 and cell A4 to 94.40
A5 now says "Error"

There is nothing else in this workbook or on this worksheet.

I am using Excel 2021 desktop version.
There are no text values anywhere on the worksheet
There are no circular values on the worksheet.

I tried using =A1+A2 in cell A3 instead of SUM() the result is the same.
Why is the formula in A5 evaluating as FALSE when a bigger number is used? Is this a calculation flaw or am I missing something?

[#2 · Accepted solution]

Without giving you a really long explanation, suffice it to say that any time you wish to compare numbers where one (or more) is calculated, you will want to wrap the calculated number in a round formula. So =round(sum(a1:a2),2)=4.40 should evaluate to true.

The theoretical issue is one of how floating-point numbers (a number with values following the dot) are approximations rather than a strict value. Depending on how the final number is created, the final few digits of the same exact number could be different. You can search the web for floating-point math errors in Excel and find a variety of info.

Have you ever noticed how 0 can be displayed as "-" "0" and "-0"? When the digit is shown instead of the dash, that is because there are some floating-point rounding issues which prevent the calculation from equaling exactly 0.

[#3]

@Guy60 

Please check Floating-point arithmetic may give inaccurate result in Excel - Microsoft 365 Apps | Microsoft Learn

[#4]

@joelb95 

Thank you for the information.

BTW I did use the brackets in my actual worksheet.  I should have show that here.

[#5]

@SergeiBaklan 

Thank you I read the article and I will try to modify my formula to use Round().

[#6]

@Guy60 , yes, rounding is the only way to avoid such kind of errors.

[#7]

@Guy60 , what do you mean exactly under using of brackets?

[#8]

Have you ever seen someone actually store currency as value * 100 to make it an int and then dividing by 100 whenever it is presented? I thought about it for about 30 seconds and then decided I was crazy.