SOLVED

Excel is not calculating correctly

Copper Contributor

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?

 

7 Replies
best response confirmed by mathetes (Silver Contributor)
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.

@joelb95 

Thank you for the information.

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

@Sergei Baklan 

 

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

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

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

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.
1 best response

Accepted Solutions
best response confirmed by mathetes (Silver Contributor)
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.

View solution in original post