Unable to find values of SQRT(0)

[Guest]

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.

 

[Biff]

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

 

[Guest]

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 Arvi’s 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/microsoft.public.excel/msg/2b9b29bf88db6ef9
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

 

[Guest]

=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/78113?#XSLTH3196121122120121120120
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

:

....

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.

....