Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unable to find values of SQRT(0)
I am attempting to find a value using a series of repeditive formulas. I am
getting an #NUM error, which I know is wrong. I am attempting to calculate: ---------------------- A B 8.2 =SQRT(3*A-24.6) ---------------------- Now, I know that 3*8.2=24.6. I know that 24.6-24.6=0. I know that the SQRT(0)=0, and Excel knows this as well. I have looked back at the step-by-step troubleshooting in Excel, and for some reason it takes (3*8.2-24.6) to be approximatly -3e^-15 or somthing in that range. It thinks that it is a negative number, and won't find the SQRT of it, but when I do it in step by steps it does it fine. It just can't manage it in one go. Why can't I do this and how do I fix it? Thanks, I hope this is in the right place. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unable to find values of SQRT(0)
Hi!
It's a rounding issue caused by the SQRT function. Try reversing the equation and you'll get the opposite result of -3.5E-15 =SQRT(24.6-3*8.2) If you highlight this portion in the formula bar: 3*8.2-24.6, and then press F9, you get a result of 0. But, as you've discovered, if you use the formula auditing tools and evaluate the formula, 3*8.2-24.6, evaluates to -3.5E-15. So, try this: =SQRT(ROUND(3*8.2,1)-24.6) Maybe Jerry Lewis will see this post and explain it for us. Biff "big_ears" wrote in message ... I am attempting to find a value using a series of repeditive formulas. I am getting an #NUM error, which I know is wrong. I am attempting to calculate: ---------------------- A B 8.2 =SQRT(3*A-24.6) ---------------------- Now, I know that 3*8.2=24.6. I know that 24.6-24.6=0. I know that the SQRT(0)=0, and Excel knows this as well. I have looked back at the step-by-step troubleshooting in Excel, and for some reason it takes (3*8.2-24.6) to be approximatly -3e^-15 or somthing in that range. It thinks that it is a negative number, and won't find the SQRT of it, but when I do it in step by steps it does it fine. It just can't manage it in one go. Why can't I do this and how do I fix it? Thanks, I hope this is in the right place. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unable to find values of SQRT(0)
You are correct that =(3*8.2-24.6) returns -3.6E-15 and that SQRT correctly
refuses to take the square root of a negative number. Therefore your question has nothing to do with SQRT. As to why =(3*8.2-24.6) returns -3.6E-15; almost all computer software (including Excel) does binary math. In binary, most terminating decimal fractions (including .2 and .6) are nonterminating binary fractions that can only be approximated. The math is exactly right, but when you do math with approximate inputs, it should be no surprise when the output is only approximate. That is why Arvis rounding recommendation is appropriate. To intuitively see what is happening, imagine a hypothetical decimal computer that carries 4 significant figures. Then 3*(1/3) - 1 = 3*0.3333 - 1 = 0.9999 - 1 = -0.0001 The decimal value for the binary approximation to 8.2 is slightly less than 8.2, but the decimal value for the binary approximation to 24.6 is slightly more than 24.6 ... You can use the D2D function at http://groups.google.com/group/micro...9b29bf88db6ef9 to see the exact decimal values of these approximations, or you can use the fact that 8.199951171875 and 24.5999755859375 can be exactly represented in IEEE double precision (used by Excel and almost all other software) so that =8.2-8.199951171875 and =24.6-24.5999755859375 can show that the approximations are in the direction that I indicated Jerry "big_ears" wrote: I am attempting to find a value using a series of repeditive formulas. I am getting an #NUM error, which I know is wrong. I am attempting to calculate: ---------------------- A B 8.2 =SQRT(3*A-24.6) ---------------------- Now, I know that 3*8.2=24.6. I know that 24.6-24.6=0. I know that the SQRT(0)=0, and Excel knows this as well. I have looked back at the step-by-step troubleshooting in Excel, and for some reason it takes (3*8.2-24.6) to be approximatly -3e^-15 or somthing in that range. It thinks that it is a negative number, and won't find the SQRT of it, but when I do it in step by steps it does it fine. It just can't manage it in one go. Why can't I do this and how do I fix it? Thanks, I hope this is in the right place. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unable to find values of SQRT(0)
=3*8.2-24.6
and =(3*8.2-24.6) return different results. This is an "optimization" that MS introduced in Excel 97 http://support.microsoft.com/kb/7811...22120121120120 When the very last operation calculates the difference between two numbers that are equal to 15 decimal places, then Excel arbitrarily zeros the result on the assumption that any nonzero result is residue from binary approximations. When you wrap the expression in parentheses or in a function call, then the difference is no longer the last operation, so the fuzz factor is not applied. IMHO this inconsistency causes more questions than it avoids. Sorry for neglecting you in my other post. You too correctly suggested rounding. Jerry "Biff" wrote: .... If you highlight this portion in the formula bar: 3*8.2-24.6, and then press F9, you get a result of 0. But, as you've discovered, if you use the formula auditing tools and evaluate the formula, 3*8.2-24.6, evaluates to -3.5E-15. .... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Match function with duplicate values in an array | Excel Worksheet Functions | |||
Sum values in multiple sheets using Lookup to find a text match | Excel Worksheet Functions | |||
error message - unable to save external link values | Excel Discussion (Misc queries) | |||
Can you Find and Replace values with in a formula? | Excel Discussion (Misc queries) | |||
How do I find the two lowest values in a range? | Excel Worksheet Functions |