Skip to main content
Best answer by Werner_E

Here is an approach which saves a lot of typing and eliminates all if-statements as well as the definition of the 16 functions. CD1..CD8 and CL1..CL8.

1.png

Nevertheless I guess that its not the if-statements and/or the local functions which are responsible for the slowness of those functions. The cause is rather the continuous evaluation of the solve block functions a.r. and a'.r by alpha.r.

I also guess that the solve blocks for a.r and a'.r are wrong as possibly a and a' should be solved for at the same time. As it is set up now, one of them will remain 0. Jinsuk, is this really what is needed that one of those two values is zero and just the other is solved for? In an earlier version you had a solve block I provided which solved for both variables a and a' simultaneously and you changed it now because you got negative values. But what you calculate now is something completely different - you have to know what you really need, so take care!

2.png

You define a lot of functions and you nest those functions in an awfully and very inefficient way. For example the function C.P.

C.P calls P,

P calls MO,

MO calls r and m,

m calls v.r, ct, c and r,

v.r calls r, a.r, and a'r,

ct calls Phi.r, CL and CD,

Phi.r calls a.r and a'r,

CL and CD both call alpha.r,

alpha.r calls beta and Phi.r

and Phi.r again calls a.r and a'.r.

Puhh!

To summarize: One single evaluation of C.P requires eight times the evaluation of one of the solve blocks (4 time a.r and four times a.r). The interpolating function r is called three time as is Phi.r. If you search for a.r and a'r in one single solve block and rewrite C.P in a more efficient way, you could cut calculation time at least to 1/8. But this would require some bigger changes in your sheet.

12 replies

L
lwestbrook5-Regular Member
5-Regular Member
January 5, 2015

Hi Jinsuk Lee,

I'm assuming that it is the plots that you would like to calculate faster? Without being able to verify that this works since I don't have access to the second Excel spreadsheet referenced in your worksheet, I would suggest changing consecutive "if" statements to "else if" statements. Since each line is an "if" statement, the program must go through each condition, even if the condition is satisfied in the first line. This can waste a lot of time. However, putting "else if" after the initial "if" statement will cause the program to stop evaluating the conditions once it hits a True value.

Hopefully that helps.

Take care,

Luke

R
RichardJ19-Tanzanite
19-Tanzanite
January 5, 2015

What you say is true, but an even better solution would be to replace all the local function assignments with a return statement, for example "return CL1(x,x,x,x)". The local function assignment is unnecessary overhead, and the return statement will force program exit as soon as a condition is satisfied.

S
StuartBruff23-Emerald V
23-Emerald V
January 5, 2015

Richard Jackson wrote:

What you say is true, but an even better solution would be to replace all the local function assignments with a return statement, for example "return CL1(x,x,x,x)". The local function assignment is unnecessary overhead, and the return statement will force program exit as soon as a condition is satisfied.

I did a quick check, just before leaving work a couple of hours ago, on a sort of M14 equivalent and reckon it ll probably double the speed of calling. I also tried putting the functions in an array and using match to locate the index of the appropriate function. Worked out about the same as immediate return.

I suspect that optimising the functions may be more fruitful. However, I don't have Prime so can't confirm this.

Oh, one other thing, Jinsuk, is that it can be a bit dangerous to select for equality on floats. 0.1 = 0.1 is an equality, but k:=0, 0.1 = 0.1*(k+1) is not. This is due to the unavoidable errors introduced by converting 0.1 into an IEEE floating point representation that is actually used for calculations. It's probably fine if you only ever explicitly compare typed in values, but it would be prudent to put an else clause in to trap any calculated cases that aren't quite equal ... or select on a small range around each target value.

Stuart

Terms & ConditionsAccessibility statement