Forum Discussion
Error in Excel formula
Hello,
I suspect an error in an Excel formula when I use a "when" clause. I get two different results when I calculate two numbers
first in a "when" clause (=WENN(H1="";B1-G1;"X")) - here I get a strange result (2,77556E-17)
second in a standalone Calculation (=B2-G2) - here i get the correct result (0)
Has anybody an idea what's going wrong?
here you can see the table:
| 3 | 0,207 | 0,069 | 3 | 0,207 | 2,77556E-17 | |||
| 3 | 0,207 | 0,069 | 3 | 0,207 | 0 |
The issue you are facing is likely due to the limited precision of floating-point numbers in Excel, leading to a very small rounding error. It is a common occurrence in calculations that involve floating-point numbers.
To handle such situations and avoid displaying these small rounding errors, you can use the ROUND function to round the result to the desired number of decimal places.
Here is an example:
In cell G1, modify your formula as follows:
In English -> =IF(H1="", ROUND(B1-G1, 10), "X")
in German -> =WENN(H1=""; RUNDEN(B1-G1; 10); "X")
This will round the result of B1-G1 to 10 decimal places. Adjust the number of decimal places according to your specific needs. The ROUND function helps to mitigate the impact of floating-point precision issues and provides a more accurate result in many cases.
Keep in mind that the presence of such small rounding errors does not necessarily affect the accuracy of your calculations, but it might affect the display of results. Rounding to an appropriate number of decimal places can help in making the displayed results more meaningful. AI was partially deployed to support the text.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
3 Replies
- addendum: the error only occurs when I calculate the number in row "G" from the rows "D" and "F" (0,069*3) before - nevertheles if I calculate "D1*F1" or i calculate the numbers (0,069*3). When I enter the number "0.207" directly into the "G" column, the result is correct
The issue you are facing is likely due to the limited precision of floating-point numbers in Excel, leading to a very small rounding error. It is a common occurrence in calculations that involve floating-point numbers.
To handle such situations and avoid displaying these small rounding errors, you can use the ROUND function to round the result to the desired number of decimal places.
Here is an example:
In cell G1, modify your formula as follows:
In English -> =IF(H1="", ROUND(B1-G1, 10), "X")
in German -> =WENN(H1=""; RUNDEN(B1-G1; 10); "X")
This will round the result of B1-G1 to 10 decimal places. Adjust the number of decimal places according to your specific needs. The ROUND function helps to mitigate the impact of floating-point precision issues and provides a more accurate result in many cases.
Keep in mind that the presence of such small rounding errors does not necessarily affect the accuracy of your calculations, but it might affect the display of results. Rounding to an appropriate number of decimal places can help in making the displayed results more meaningful. AI was partially deployed to support the text.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
