What's wrong with this formula?

[Alain Sienaert]

Hi,

As the title already suggests we are having problems implementing the
function below.
Have tried it in Excel 2002 and Excel 2003 but always receive an error ("The
formula you typed contains an error) when pasting the formula into a cell.
Pasting the same formula in Mathematica does work and it also works in Open
Office 2.02.


-0.0922863908980922 *
( -(Power(Power(sin(exp(Power(sin(ln(2.53)),A2))),0.77) -cos(sin(cos(cos(exp(sin(Power(exp(4.71
-2.53),2))))))) -2.6 -2.6 *Power(ln(3.07 +sin(4.81)),2)
+Power(sin(exp(Power(sin(ln(2.53)),A2))),0.77)
+Power(sin(exp(Power(sin(ln(2.53)),A2))),0.77)
*Power(exp(sin(4.03 -A2)),2),cos(B2 +A2 / -(A2))))) + -0.0412415105277979


Thanks for looking into this annoying problem,
Alain

 

[Pete_UK]

On the bottom line you have:

.... + -0.0412415105277979

It looks as if you may be suffering from spurious line-breaks which
occasionally happens with long formulae in the newsgroups and you get
hyphens inserted in the formula. Could be worth checking out wherever
you have minus in the formula.

Hope this helps.

Pete

 

[David Biddulph]

What's wrong with your formula is that you have exceeded Excel's limits on
depth of nesting of functions.

I suggest that you look at where you can simplify it.
There are simple things, such as the fact that (B2 +A2 / -(A2)) on the last
line is the same as (B2 -1)
You've got lots of constant terms that could be evaluated separately (in a
separate cell, if you like) and the results included.
cos(sin(cos(cos(exp(sin(Power(exp(4.71 -2.53),2))))))) is one lengthy (and
deeply nested) expression which turns out to be a constant (and which I
struggle to believe can be a physically significant expression), and which
in turn is added to further constant terms.
Other expressions which occur frequently in the formula, such as
Power(sin(exp(Power(sin(ln(2.53)),A2))),0.77)
could again be put in a separate cell & the result called up where
required.

By putting
=Power(sin(exp(Power(sin(ln(2.53)),A2))),0.77) in A3 and
=cos(sin(cos(cos(exp(sin(Power(exp(4.71 -2.53),2))))))) in A4,
the expression simplifies to =
-0.0922863908980922 * ( -(POWER(A3 - A4 -2.6 -2.6 * POWER(LN(3.07
+SIN(4.81)),2) + A3 + A3 *
POWER(EXP(SIN(4.03 -A2)),2),COS(B2 -1)))) + -0.0412415105277979
which does fall within Excel's nesting limit spec

I'd be fascinated if you could tell me what the formula actually does for
you.

 

[Alain Sienaert]

David,

Thanks for your answer and I don't mind sharing what we are using these
formula's for...

We have developed an application that uses a technology called Genetic
Programming.
The basic idea is that you submit a dataset to the application and that this
application tries to find relationships in the submitted data.
e.g. If you submit the following (very simple) dataset to the application
then the application will tell you that Y can be calculated using the
following formula: Y=(X1*X1)+(X1*X2)

X1 X2 Y
1 1 2
1 2 3
1 3 4
1 4 5
1 5 6
2 1 6
2 2 8
2 3 10
2 4 12
2 5 14
3 1 12
3 2 15
3 3 18
3 4 21
3 5 24
4 1 20
4 2 24
4 3 28
4 4 32
4 5 36
5 1 30
5 2 35
5 3 40
5 4 45
5 5 50

As you might have guessed the average dataset and therefore the resulting
formula is much more complex than the above example.
One of the reasons that we use Mathematica is that it is easy to simplify
formula's but the average user does not have Mathematica ;-)

Cheers,
Alain

 

[David Hilberg]

What an interesting project!

Just for the record:

"In Excel 2007, a formula can contain up to 64 levels of nesting, but
in earlier versions of Excel, the maximum levels of nesting is only
7." [Microsoft Office Online]

- David Hilberg (currently using xl2003)

 

[Alain Sienaert]

fyi,
Alain

http://en.wikipedia.org/wiki/Genetic_programming
http://en.wikipedia.org/wiki/John_R._Koza
http://www.geneticprogramming.com/Tutorial/index.html
http://www.genetic-programming.com/johnkoza.html

and of course more to find using your favorite search engine...

 

[Dana DeLouis]

sin(exp(Power(sin(ln(

Just a side question. In your conversion from Mathematica to Excel, how did
"Power" keep the proper Capitalization , and the remaining functions went to
lower case?
I'm always adjusting the logic to my MmaToExcel[ ] and MmaToVBA[ ] funtions.
:>~

--
Dana DeLouis

 

[Alain Sienaert]

As I mentioned in my previous post these formula's are generated by our
internally developed application.
This application uses postfix notation (e.g. x2 x1 + x1 /) while generating
these formula's and only the results are converted into an Excel or
Mathematica format.
In other words we have implemented 2 functions, one that does the postfix to
Excel conversion and one that handles the postfix to Mathematica conversion.
I cannot publish the code since it is proprietary code but I guess it
shouldn't be that hard to find similar routines on the internet.

This is the Mathematica equivalent of the Excel formula in my original post.

-0.0922863908980922 * ( -(Power[Cos[x2 +x1
/ -(x1)],Power[0.77,Sin[Exp[Power[x1,Sin[Log[2.53]]]]]] -Cos[Sin[Cos[Cos[Exp[Sin[Power[Exp[4.71
-2.53],2]]]]]]] -2.6 -2.6 *Power[Log[3.07 +Sin[4.81]],2]
+Power[0.77,Sin[Exp[Power[x1,Sin[Log[2.53]]]]]]
+Power[0.77,Sin[Exp[Power[x1,Sin[Log[2.53]]]]]]
*Power[Exp[Sin[4.03 -x1]],2]])) + -0.0412415105277979

Alain

sin(exp(Power(sin(ln(

Just a side question. In your conversion from Mathematica to Excel, how
did "Power" keep the proper Capitalization , and the remaining functions
went to lower case?
I'm always adjusting the logic to my MmaToExcel[ ] and MmaToVBA[ ]
funtions. :>~

 

[Dana DeLouis]

Have tried it in Excel 2002 and Excel 2003 but always receive an error

Hi. Just for feedback, I do not receive an error in Excel 2007 as David
mentioned.

Just a note:

Cos[x2 +x1 /-(x1)]

It appears that your program has placed a HOLD on the equation, otherwise
the x1's would have cancelled out (like David mentioned). You may want to
use "ReleaseHold".

Just an additional observation in addition to David's.
Here's a small section of the equation:

equ = 0.77^Sin[E^x1^Sin[Log[2.53]]];

This is highly oscillatory. Are you sure this is correct?
If we look at the symbolic limit, say at infinity, we get a feel for what's
happening. Even the Limit returns an Interval object. (@ Machine Precision)

Limit[equ, x1 -> Infinity]
Interval[{0.77, 1.2987012987012994}]

I'm not sure how useful or accurate this can be.
Again, just some feedback. :>) Good luck.

(I've seen some interesting code that generates all the Combinations /
Permutations of a list of mathematical operators, and applies it to the
Heads of Permutations of variables. Very interesting, and if avoids the
confusing PostFix notation.)

--
HTH :>)
Dana DeLouis

As I mentioned in my previous post these formula's are generated by our
internally developed application.
This application uses postfix notation (e.g. x2 x1 + x1 /) while
generating these formula's and only the results are converted into an
Excel or Mathematica format.
In other words we have implemented 2 functions, one that does the postfix
to Excel conversion and one that handles the postfix to Mathematica
conversion.
I cannot publish the code since it is proprietary code but I guess it
shouldn't be that hard to find similar routines on the internet.

This is the Mathematica equivalent of the Excel formula in my original
post.

-0.0922863908980922 * ( -(Power[Cos[x2 +x1
/ -(x1)],Power[0.77,Sin[Exp[Power[x1,Sin[Log[2.53]]]]]] -Cos[Sin[Cos[Cos[Exp[Sin[Power[Exp[4.71
-2.53],2]]]]]]] -2.6 -2.6 *Power[Log[3.07 +Sin[4.81]],2]
+Power[0.77,Sin[Exp[Power[x1,Sin[Log[2.53]]]]]]
+Power[0.77,Sin[Exp[Power[x1,Sin[Log[2.53]]]]]]
*Power[Exp[Sin[4.03 -x1]],2]])) + -0.0412415105277979

Alain

sin(exp(Power(sin(ln(

Just a side question. In your conversion from Mathematica to Excel, how
did "Power" keep the proper Capitalization , and the remaining functions
went to lower case?
I'm always adjusting the logic to my MmaToExcel[ ] and MmaToVBA[ ]
funtions. :>~

--
Dana DeLouis

By putting
=Power(sin(exp(Power(sin(ln(2.53)),A2))),0.77) in A3 and
=cos(sin(cos(cos(exp(sin(Power(exp(4.71 -2.53),2))))))) in A4,
the expression simplifies to =
-0.0922863908980922 * ( -(POWER(A3 - A4 -2.6 -2.6 * POWER(LN(3.07
+SIN(4.81)),2) + A3 + A3 *
POWER(EXP(SIN(4.03 -A2)),2),COS(B2 -1)))) + -0.0412415105277979
which does fall within Excel's nesting limit spec

I'd be fascinated if you could tell me what the formula actually does
for you.
--
David Biddulph

Hi,

As the title already suggests we are having problems implementing the
function below.
Have tried it in Excel 2002 and Excel 2003 but always receive an error
("The formula you typed contains an error) when pasting the formula
into a cell.
Pasting the same formula in Mathematica does work and it also works in
Open Office 2.02.


-0.0922863908980922 *
( -(Power(Power(sin(exp(Power(sin(ln(2.53)),A2))),0.77) -cos(sin(cos(cos(exp(sin(Power(exp(4.71
-2.53),2))))))) -2.6 -2.6 *Power(ln(3.07 +sin(4.81)),2)
+Power(sin(exp(Power(sin(ln(2.53)),A2))),0.77)
+Power(sin(exp(Power(sin(ln(2.53)),A2))),0.77)
*Power(exp(sin(4.03 -A2)),2),cos(B2 +A2 / -(A2)))))
+ -0.0412415105277979


Thanks for looking into this annoying problem,
Alain

 

[Dana DeLouis]

Just an additional observation in addition to David's.

Here's a small section of the equation:

equ = 0.77^Sin[E^x1^Sin[Log[2.53]]];

This is highly oscillatory. Are you sure this is correct?

If you are wondering if the function does oscillate...
If we shift the output down a little, say by 1, the output crosses the
x-axis 21 times as X1 varies only between 0 and 6.

IntervalBisection[equ, x1, Interval[{0, 6.}],0.0001]//Length

21

And jumps to 176 as the variable x1 varies between 0 and 10.