ACOS(1) & ACOS(-1) Error

[Me]

I have a function (say F) from the law of cosines that should evaluate
between [-1,1].

so from the LOC -> C^2 = A^2 + B^2 - 2ABcos(Theta)
solve for Theta -> Theta = ACOS( -(C^2-A^2-B^2)/(2AB) )
My function F -> F = -(C^2-A^2-B^2)/(2AB)

A, B, and C are pseudo-randomly generated values and it appears that F
has some precision problems that after evaluation the values can fall
outside the bounds by a little bit on either side. I've seen F be
1.00000024 and I've seen negative values of F off by about the same
amount. It appears this is the best precision I can get with the how
I'm generating these values, not that it matters much 'cause the weird
part comes later.

I understand that the ACOS function must be [-1,1] so I use an IF to
make a correction before evaluating the ACOS. I'm not to worried
about the error so I have a cell do this:

=IF( F > 1, 1, IF( F < -1, -1, F) )

The value in this cell appears to give a value of 1 and -1 to 30
decimal places when looking through the Format Cell->Numbers->Number
option. Now, here's the wierd part: If I type in a cell =ACOS(1) I
get 0. If I type in a cell =ACOS(-1) I get Pi. But if I reference
the cell with the IF statement above (which appears to have values
between 1 or -1) I end up with a #NUM! error every time F was outside
-1 and 1.

Anyone have any ideas??

 

[Ron Rosenfeld]

The value in this cell appears to give a value of 1 and -1 to 30
decimal places when looking through the Format Cell->Numbers->Number
option. Now, here's the wierd part: If I type in a cell =ACOS(1) I
get 0. If I type in a cell =ACOS(-1) I get Pi. But if I reference
the cell with the IF statement above (which appears to have values
between 1 or -1) I end up with a #NUM! error every time F was outside
-1 and 1.

I cannot reproduce your problem with a named cell = F and placing values
outside of the range -1:1 in that cell.

I also used a different formula to restrain F : =MAX(-1,MIN(F,1)) and also
obtained reliable results.


--ron

 

[Ron Rosenfeld]

The value in this cell appears to give a value of 1 and -1 to 30
decimal places when looking through the Format Cell->Numbers->Number
option. Now, here's the wierd part: If I type in a cell =ACOS(1) I
get 0. If I type in a cell =ACOS(-1) I get Pi. But if I reference
the cell with the IF statement above (which appears to have values
between 1 or -1) I end up with a #NUM! error every time F was outside
-1 and 1.

Sorry, hit <enter> too soon.

Have you tried the Trace Error function of Excel?


--ron

 

[Me]

Ron,

Thanks for your help. The MAX(-1,MIN(F,1)) appears to do the trick.
I don't understand why if evaluates different than IF( F > 1, 1, IF( F
< -1, -1, F) ) -- I'll just add another tick to my Microsoft box and
be happy in the fact there's a workaround. BTW, I did try the error
tracing earlier and everything pointed back to the ACOS call. I
assume that somehow a value outside of [-1,1] was being passed to it.
If anyone knows the difference between the workings of MAX, MIN, and
IF I'd be interested in knowing. Thanks again Ron.

 

[Ron Rosenfeld]

The MAX(-1,MIN(F,1)) appears to do the trick.
I don't understand why if evaluates different than IF( F > 1, 1, IF( F
< -1, -1, F) )

I'm glad it worked but I don't understand why the IF(F>... does not.

Did you try highlighting the formula in the formula bar and hitting <F9> to see
how it evaluated?


--ron