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 ...
- 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.
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.
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.
