Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What's wrong with this formula?
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.7 7) -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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What's wrong with this formula?
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 On Sep 18, 11:11 am, "Alain Sienaert" wrote: 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.7 7) -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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What's wrong with this formula?
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. -- David Biddulph "Alain Sienaert" wrote in message ... 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.7 7) -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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What's wrong with this formula?
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 Biddulph" <groups [at] biddulph.org.uk wrote in message ... 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. -- David Biddulph "Alain Sienaert" wrote in message ... 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.7 7) -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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What's wrong with this formula?
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) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What's wrong with this formula?
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... "David Hilberg" wrote in message ups.com... 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) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What's wrong with this formula?
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 <snip 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 "Alain Sienaert" wrote in message ... 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.7 7) -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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What's wrong with this formula?
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 "Dana DeLouis" wrote in message ... 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 <snip 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 "Alain Sienaert" wrote in message ... 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.7 7) -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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What's wrong with this formula?
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 "Alain Sienaert" wrote in message ... 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 "Dana DeLouis" wrote in message ... 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 <snip 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 "Alain Sienaert" wrote in message ... 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.7 7) -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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
What's wrong with this formula?
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. -- Dana DeLouis <snip |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is wrong with this IF formula | Excel Discussion (Misc queries) | |||
Help please,what is wrong with this formula? | New Users to Excel | |||
What is wrong with this formula? | Excel Worksheet Functions | |||
What is wrong with this formula? | Excel Worksheet Functions | |||
What's wrong with this formula? | Excel Worksheet Functions |