Forum Discussion
Solved
Excel is not calculating correctly
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?
- 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.
7 Replies
Guy60 , yes, rounding is the only way to avoid such kind of errors.
- 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.Guy60 , what do you mean exactly under using of brackets?
