I have a formula system to predict values based on a mathematical model and then calculate a square sum of the error with empirical data. I'm using the solver to minimize the error but the whole process is quite slow. I'm seeking insight on how to improve calculation speed.
Defined named functions: (sheet name is '100DN')
P_Th: (The slow one)
=VSTACK(INDEX('100DN'!P_AU;1);SCAN(INDEX('100DN'!P_AU;1);INDEX('100DN'!T_AU;SEQUENCE(COUNT('100DN'!T_AU)-1;;2))-INDEX('100DN'!T_AU;SEQUENCE(COUNT('100DN'!T_AU)-1));LAMBDA(p;dt;EXP(-dt/'100DN'!$M$9)*(p-INDEX('100DN'!P_AU;1))+(1-EXP(-dt/'100DN'!$M$9))*'100DN'!$M$5*('100DN'!$M$2)+INDEX('100DN'!P_AU;1))))
formulas in Worksheet
$M$10: =SUMSQ(P_TH-P_AU)
Solver Parameters
Solving method GCG Nonlinear, defaults settings
Target Minimize $M$10
by changing values in $M$5;$M$9
$M$5: >=.00001, initial value set near to solution
$M$9: >=.00001, initial value set near to solution
Make unconstraint variable non-negative checkbox activated
Notes on sample & Data Sample
P_AU and T_AU are precalculated
$M$2 is constant at -0.1
T_AU :
0,004
0,054
0,104
0,154
0,204
0,254
0,304
0,354
0,404
0,454
0,504
0,554
0,604
0,654
0,704
0,753
0,803
0,853
0,904
0,955
1,004
1,054
1,104
1,154
1,203
1,254
1,305
1,355
1,404
1,454
1,504
1,553
1,603
1,654
1,704
1,755
1,805
1,854
1,904
1,954
2,004
2,055
2,104
2,154
2,204
2,253
2,303
2,354
2,404
2,455
2,505
2,555
2,604
2,654
2,704
2,754
2,805
2,854
2,904
2,954
3,004
3,053
3,104
3,154
3,204
3,254
3,304
3,354
3,404
3,454
3,504
3,554
3,604
3,654
3,704
3,754
3,804
3,854
3,904
3,954
4,004
4,054
4,104
4,154
4,203
4,254
4,304
4,355
4,404
4,454
4,504
4,553
4,604
4,654
4,705
4,754
4,804
4,854
4,904
4,953
5,004
5,054
5,104
5,154
5,204
5,254
5,303
5,354
5,404
5,454
5,504
5,554
5,604
5,653
5,703
5,754
5,805
5,854
5,904
5,953
6,003
6,053
6,104
6,154
6,204
6,255
6,304
6,354
6,404
6,454
6,504
6,555
6,605
6,655
6,704
6,754
6,804
6,854
6,904
6,954
7,004166667
7,0545
7,104166667
7,153833333
7,203833333
7,254
7,3045
7,3545
7,404333333
7,453666667
7,503833333
7,553666667
7,603666667
7,654166667
7,704333333
7,754166667
7,803833333
7,853833333
7,9035
7,953666667
8,004
8,054166667
8,1045
8,154166667
8,203833333
8,253666667
8,3035
8,3535
8,404666667
P_AU :
81,453
77,080
71,801
67,594
63,615
60,098
56,518
53,552
50,886
47,887
45,411
43,620
41,766
40,003
38,200
36,120
35,205
33,085
32,093
30,318
29,280
28,276
26,927
26,233
25,230
24,279
23,537
22,557
21,605
21,165
20,196
19,283
18,607
17,972
17,821
17,002
16,595
15,751
15,387
14,966
13,990
13,236
13,376
12,879
12,838
11,859
11,685
11,199
10,919
10,648
10,542
9,876
9,284
9,550
8,334
8,292
7,902
7,400
7,483
7,527
7,434
6,846
6,703
6,106
5,941
5,453
5,389
4,942
5,171
4,869
4,232
4,691
3,914
4,086
3,673
3,489
3,729
2,549
2,823
2,878
2,747
1,969
1,932
2,168
2,077
1,680
1,654
1,483
1,249
0,704
1,122
0,550
0,395
0,588
0,467
0,452
0,068
0,613
0,085
-0,141
-0,492
-0,046
-0,671
-0,689
-0,867
-0,804
-0,685
-0,819
-0,724
-1,369
-1,349
-1,092
-1,433
-1,359
-1,380
-2,062
-1,655
-1,788
-1,941
-1,618
-1,867
-2,295
-2,130
-1,879
-2,138
-2,352
-2,207
-2,091
-2,306
-2,630
-2,195
-2,666
-2,763
-2,592
-2,454
-2,727
-2,793
-2,341
-2,520
-2,859
-2,72969
-2,777090167
-3,021991
-3,033841167
-2,681499833
-3,221071833
-3,349842167
-3,192631667
-3,355372333
-2,923240667
-3,365642167
-2,985651
-3,3490525
-2,8821605
-3,200531667
-3,155501667
-3,472292833
-3,622393333
-3,970784333
-3,574993
-3,311132
-3,488092667
-3,573413
-3,428842333
-3,755903833
-2,751020167
-1,678196333
-0,8755535
-0,515312167
Unused defined names (precalculated, for reference)
DataSplit:
=LAMBDA(RowNum;ColName;FILTER(INDIRECT('100DN'!$L$16 & "[[" & ColName & "]]");(INDIRECT('100DN'!$L$16 & "[[Temps ]]")>INDEX(INDIRECT('100DN'!$L$17);RowNum;3-'100DN'!$L$3))*(INDIRECT('100DN'!$L$16 & "[[Temps ]]")<(INDEX(INDIRECT('100DN'!$L$17);RowNum;3-'100DN'!$L$3)+INDEX(INDIRECT('100DN'!$L$17);8;3-'100DN'!$L$3)))))
P:
=LAMBDA(Col;'100DN'!DataSplit(Col;"Pressi"))
Tm:
=LAMBDA(Col;'100DN'!DataSplit(Col;"Temps ")-INDEX(INDIRECT('100DN'!$L$17);Col;3-'100DN'!$L$3))
P_AU:
=('100DN'!P(1)+'100DN'!P(2)+'100DN'!P(3)+'100DN'!P(4)+'100DN'!P(5)+'100DN'!P(6))/6
T_AU:
=('100DN'!Tm(1)+'100DN'!Tm(2)+'100DN'!Tm(3)+'100DN'!Tm(4)+'100DN'!Tm(5)+'100DN'!Tm(6))/6
$L$16 is the name of the table where the full dataset is located and
$L$17 is the name of the table where the limits are located for the FILTER function
Notes
P_TH is defined as a spilled array with VStack because P_AU and T_AU have variable length and to be used in a dynamic graph.
Current solving time is nearly 20 minutes. I know that not using a LAMBDA function within a SCAN function is faster, i.e. using a recursive function with relative references dragged along cells, but it does not support the variable lengths of P_AU and T_AU
P_AUandT_AU"?P_AUandT_AUspilled array must be set in a single cell, spilling downwards. For instance, the cellA$1 = T_AUand$B$1 = P_AUThen, the standard formula can be put in cell$C$1 = $B$1and the recursive formula into$C$2with a relative reference toA1,A2,B1,B2andC1, then dragged down to a fixed length matching currentT_AUandP_AUlength, let's say$C$100. But ifT_AUandP_AUchange length, the span of the formulas from$C$2through$C$100would not update to reflect changes.