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