Precedence Issue?

[Me]

For both Excel 2000 & 97.


I have the VBA function below: (Degrees to Radians)

Function DR(D)
DR = 3.14159265358979 * D / 180#
End Function


Inside of Excel (cell N4) I have the function:

=-_A^2-_B^2-2*_A*_C*TAN(DR(E4))^2/COS(DR(E4))+3*_B*_C*TAN(DR(E4))/COS(DR(E4))+_A*_C/COS(DR(E4))

Inside of Excel (cell O4) I have the function:

=-2*_A*_C*TAN(DR(E4))^2/COS(DR(E4))+3*_B*_C*TAN(DR(E4))/COS(DR(E4))+_A*_C/COS(DR(E4))-_A^2-_B^2

The only difference being the placement of the -_A^2-_B^2 terms. In
N4, the placement is at the beginning; whereas, in O4, the placement
is at the end. I would think (obviously I'm wrong) that they would
evaluate the same. Cell O4 gives the correct answer.

The three named cells are:

_A in cell B8 value = 24
_B in cell B9 value = 25
_C in cell B10 vallue = 40.23

Cell E4 has a value of 0.4.

Also, if I put a + in front of the -_A^2-_B^2 term in cell O4 like
this:

=-2*_A*_C*TAN(DR(E4))^2/COS(DR(E4))+3*_B*_C*TAN(DR(E4))/COS(DR(E4))+_A*_C/COS(DR(E4))+-_A^2-_B^2

The expression evaluates incorrectly with the same value as in cell
N4.

Can anyone help me by telling me which rule I'm breaking? I've been
trying to figure out this black box and I'm stuck. Thanks in advance
for any help.

 

[kkknie]

It would appear that negation takes place before exponentiation.

When I try -5^2 I get 25.
When I change to -(5^2) I get -25.

Apparently negation is higher on the pecking order than subraction,
which is why putting it at the end works.

Good thing to know (for me as well).

K

Edit: Just found in Excel Help:

Operator precedence

1. colon, single space or comma (reference operator)
2. – Negation (as in –1)
3. % Percent
4. ^ Exponentiation
5. * and / Multiplication and division
6. + and – Addition and subtraction
7. & Connects two strings of text (concatenation)
8. = < > <= >= <> Comparison

 

[JE McGimpsey]

The first rule you're breaking is using a UDF where a worksheet functin
is available. Use

RADIANS(E4)

instead

 

[A.W.J. Ales]

Rance,

The problem is that Excel (in cell N4) treats -_A^2 as (-_A) ^ 2 rather
than - (_A^2)

In cell O4 this is not the case since the minus sign in that formula is the
connection between the former part of the formula and -_A^2.
(The same is applying for the parts with -_B^2 as well. It is treated, as
you expected, as - (B^2)

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *