What's wrong with this formula?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alain Sienaert - 18 Sep 2007 11:11 GMT

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

Reply to this Message

Pete_UK - 18 Sep 2007 11:19 GMT

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

> Hi,
>
[quoted text clipped - 14 lines]
> Thanks for looking into this annoying problem,
> Alain

Reply to this Message

David Biddulph - 18 Sep 2007 13:25 GMT

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.

Signature

David Biddulph

> Hi,
>
[quoted text clipped - 15 lines]
> Thanks for looking into this annoying problem,
> Alain

Reply to this Message

Alain Sienaert - 18 Sep 2007 19:21 GMT

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

> What's wrong with your formula is that you have exceeded Excel's limits on
> depth of nesting of functions.
[quoted text clipped - 43 lines]
>> Thanks for looking into this annoying problem,
>> Alain

Reply to this Message

David Hilberg - 18 Sep 2007 20:03 GMT

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)

Reply to this Message

Alain Sienaert - 18 Sep 2007 20:17 GMT

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

> What an interesting project!
>
[quoted text clipped - 5 lines]
>
> - David Hilberg (currently using xl2003)

Reply to this Message

Dana DeLouis - 18 Sep 2007 23:27 GMT

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

:>~

Signature

Dana DeLouis

<snip>

>> By putting
>> =Power(sin(exp(Power(sin(ln(2.53)),A2))),0.77) in A3 and
[quoted text clipped - 27 lines]
>>> Thanks for looking into this annoying problem,
>>> Alain

Reply to this Message

Alain Sienaert - 19 Sep 2007 07:34 GMT

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(
>
[quoted text clipped - 35 lines]
>>>> Thanks for looking into this annoying problem,
>>>> Alain

Reply to this Message

Dana DeLouis - 19 Sep 2007 18:09 GMT

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

Signature

HTH :>)
Dana DeLouis

> As I mentioned in my previous post these formula's are generated by our
> internally developed application.
[quoted text clipped - 58 lines]
>>>>> Thanks for looking into this annoying problem,
>>>>> Alain

Reply to this Message

Dana DeLouis - 19 Sep 2007 20:36 GMT

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

Signature

Dana DeLouis

<snip>

Reply to this Message