"SOLVER" function as in Excel

[robmio]

Good evening everyone. One question: is there a function on HP PRIME that can search for maxima, minima and roots, like the Excel “SOLVER”?
I'd like to implement my HP PRIME with the "Cox Regression" of survival analysis.
Thanks a lot, Roberto.

See: https://www.real-statistics.com/survival...on-solver/

[rawi]

Hi,
you can solve equations for unknowns, easiest with the solve app. But there is the no ready to use optimization procedure built in. I wrote such a procedure you can find here: https://www.hpmuseum.org/forum/thread-16426.html
Perhaps this helps.

[robmio]

(01-02-2022 03:59 AM)rawi Wrote:  Hi,
you can solve equations for unknowns, easiest with the solve app. But there is the no ready to use optimization procedure built in. I wrote such a procedure you can find here: https://www.hpmuseum.org/forum/thread-16426.html
Perhaps this helps.

Thanks for your invaluable help,

best regards, Roberto.

[robmio]

(01-02-2022 03:59 AM)rawi Wrote:  Hi,
you can solve equations for unknowns, easiest with the solve app. But there is the no ready to use optimization procedure built in. I wrote such a procedure you can find here: https://www.hpmuseum.org/forum/thread-16426.html
Perhaps this helps.

Sorry, why in "FUNCX" there is no ▶Q1, in the line before "RETURN",?

EXPORT FUNCX()
BEGIN
LOCAL Q1;
(L1(1)-2.)^2+(L1(2)-1)^2+(L1(3)-1.5)^2▶Q1;
Q1+L1(4)^2+(L1(5)+1)^2▶Q1;
Q1+2*((L1(1)-2)*(L1(2)-1))^2▶Q1;
Q1+0.5*((L1(2)-1)*(L1(5)+1))^2; <------ your version
RETURN Q1;
END;

EXPORT FUNCX()
BEGIN
LOCAL Q1;
(L1(1)-2.)^2+(L1(2)-1)^2+(L1(3)-1.5)^2▶Q1;
Q1+L1(4)^2+(L1(5)+1)^2▶Q1;
Q1+2*((L1(1)-2)*(L1(2)-1))^2▶Q1;
Q1+0.5*((L1(2)-1)*(L1(5)+1))^2▶Q1; <------ version of the question asked by me
RETURN Q1;
END;

Can you give another example with another function?

Thanks so much

[robmio]

(01-02-2022 03:59 AM)rawi Wrote:  Hi,
you can solve equations for unknowns, easiest with the solve app. But there is the no ready to use optimization procedure built in. I wrote such a procedure you can find here: https://www.hpmuseum.org/forum/thread-16426.html
Perhaps this helps.

One last question: if I have to interpolate a series of data: X = {7,2,6,4,5} Y = {6,2,4,9,3}, how can I utilize your program ?

[rawi]

Quote: Sorry, why in "FUNCX" there is no ▶Q1, in the line before "RETURN",?

You are totally right. This was an error by me I corrected in my post. I came to the right solution with the wrong function, so I did not realize it. But it works as well with the corrected function.

Quote:One last question: if I have to interpolate a series of data: X = {7,2,6,4,5} Y = {6,2,4,9,3}, how can I utilize your program ?

You can change it to fit data. Unfortunately I did not do that for the HP Prime but for the TI 89. You will find those programs here: https://www.ticalc.org/archives/files/fi...47622.html
and here: https://www.ticalc.org/archives/files/fi...47644.html

Perhaps this helps a little.

Thank you for showing me the error.

Best

rawi

[C.Ret]

(01-02-2022 07:44 AM)robmio Wrote:  One last question: if I have to interpolate a series of data: X = {7,2,6,4,5} Y = {6,2,4,9,3}, how can I utilize your program ?

Don't use a program ! there is plenty of way to achieve that on the HP Prime in a few minute without any program coding.

Did you ever notice that there is an outlier in your data ? Did you plot your set ?

1. Press the Apps button, finger scroll the list of applications and touch Statistics 2Var icon and start it.
2. In the C1 column enter your list of X values {7,2,6,4,5}
3. In the C2 column enter your list of Y values {6,2,4,9,3}
4. Press the Plot button to plot your set. Adjust zoom and center windows using your fingers.
5. Notice the outlier point #4 at (4,9) . Having a good extrapolation will be difficult
6. Touch the Menu \ Fit. The default fitting is the linear equation. Obviously not close enough !
7. Confirm bad fitting, have a look at the statistics:
   Press Num button and touch Stats menu twice to look at the Coefficient of determination R²= 0.073 (really bad)
8. Press ESC key to quit statistics view and press the Symb button to choose for a more appropriate fitting.
9. Select Type1 and touch Choose to select a more appropriate fitting model One. I may suggest is to used the exponential Y = b*exp(m*X).
10. Press Plot button to see the improvement on the graph. Much better. But point #4 at (4,9) is really too far .
    Now a quick look at the statistics show a better fit R²=0.222; Still not good enough.
11. Let try to get a more adapted response !
    Let apply weights at each point to try to get a more efficient "extrapolation".
12. Press Num button to enter weight in the thrid column : column C3 { 100,100,100,1,100 }
    I am 100% confident in all the points except for point #4 : 1% reliable only ?
13. Press Symb button to go back to fitting model and add C3 in the last box. the série S1 is definite by C1 (X value) C2 (Y value) C3 (Weights)
14. Press Plot button to see the effect of the weight's adjustment.
    Much better ! Now R²= 0.906. Don't know if this is good enough for your specific application.
    

To get the equation, press Symb touch Y = 1.32*exp(0.198*X) and press SHIFT COPY button. You can paste in the HOME menu to get a clear view of it.

The résidus can be display in the HOME view :
Press HOME button, press the Tool button and select App Functions, select (1)Statistics 2Var and (3)Resid
The résidus are { 0.795 , 0.125 , -0.267 , 6.17 (outlier) , -.474 )
The predicted value can be obtained the same way:
PREDY(4) display 2.92 (instead of the 9 of your initial inputs)
PREDY(1) returns 1.61
PREDY(9) returns 7.88


   


It is so easy to use the power of the HP Prime without any code programming.
of course, your skill in programming will made all this automatic or more enhance or sophisticated.
But, the basic feature of this calculator and the clear and crisp tactile display is a real advantage over other pockets.



Edited severeal times to attempt to correct a broken english.

[robmio]

(01-02-2022 11:35 AM)C.Ret Wrote:  

(01-02-2022 07:44 AM)robmio Wrote:  One last question: if I have to interpolate a series of data: X = {7,2,6,4,5} Y = {6,2,4,9,3}, how can I utilize your program ?

Don't use a program ! there is plenty of way to achieve that on the HP Prime in a few minute without any program coding.

Did you ever notice that there is an outlier in your data ? Did you plot your set ?

1. Press the Apps button, finger scroll the list of applications and touch Statistics 2Var icon and start it.
2. In the C1 column enter your list of X values {7,2,6,4,5}
3. In the C2 column enter your list of Y values {6,2,4,9,3}
4. Press the Plot button to plot your set. Adjust zoom and center windows using your fingers.
5. Notice the outlier point #4 at (4,9) . Having a good extrapolation will be difficult
6. Touch the Menu \ Fit. The default fitting is the linear equation. Obviously not close enough !
7. Confirm bad fitting, have a look at the statistics:
   Press Num button and touch Stats menu twice to look at the Coefficient of determination R²= 0.073 (really bad)
8. Press ESC key to quit statistics view and press the Symb button to choose for a more appropriate fitting.
9. Select Type1 and touch Choose to select a more appropriate fitting model One. I may suggest is to used the exponential Y = b*exp(m*X).
10. Press Plot button to see the improvement on the graph. Much better. But point #4 at (4,9) is really too far .
    Now a quick look at the statistics show a better fit R²=0.222; Still not good enough.
11. Let try to get a more adapted response !
    Let apply weights at each point to try to get a more efficient "extrapolation".
12. Press Num button to enter weight in the thrid column : column C3 { 100,100,100,1,100 }
    I am 100% confident in all the points except for point #4 : 1% reliable only ?
13. Press Symb button to go back to fitting model and add C3 in the last box. the série S1 is definite by C1 (X value) C2 (Y value) C3 (Weights)
14. Press Plot button to see the effect of the weight's adjustment.
    Much better ! Now R²= 0.906. Don't know if this is good enough for your specific application.
    

To get the equation, press Symb touch Y = 1.32*exp(0.198*X) and press SHIFT COPY button. You can paste in the HOME menu to get a clear view of it.

The résidus can be display in the HOME view :
Press HOME button, press the Tool button and select App Functions, select (1)Statistics 2Var and (3)Resid
The résidus are { 0.795 , 0.125 , -0.267 , 6.17 (outlier) , -.474 )
The predicted value can be obtained the same way:
PREDY(4) display 2.92 (instead of the 9 of your initial inputs)
PREDY(1) returns 1.61
PREDY(9) returns 7.88





It is so easy to use the power of the HP Prime without any code programming.
of course, your skill in programming will made all this automatic or more enhance or sophisticated.
But, the basic feature of this calculator and the clear and crisp tactile display is a real advantage over other pockets.



Edited severeal times to attempt to correct a broken english.

Thank you very much C.Ret.
You made it clear to me about the use of weights, and this satisfied me.
However, I would need an iterative program for calculating Cox's (multiple) regression in the survival analysis. The HP PRIME calculator is very powerful and gives, for example, the possibility to program a "classic" multiple regression, thanks to the powerful matrix calculation and the LSQ command. The situation becomes more complex when the calculation of the Cox regression "demands" an iterative numerical approach.
The HP PRIME calculator gives you the ability to solve optimization problems with the "simplex_reduce" command, but it doesn't give you the ability (at least I think) to calculate Cox regression coefficients. Since I am not an expert programmer, I have searched the solution of my problem on the web, and a "partial" answer to my problem was given to me by "rawi".

I cordially greet you, Roberto.

[robmio]

Good evening again:

is it possible in the "spreadsheet" APP to use an "IF THEN ELSE" statement as in EXCEL?

[rawi]

Quote:is it possible in the "spreadsheet" APP to use an "IF THEN ELSE" statement as in EXCEL?

I did not find a direct way, but a somewhat indirect.
Suppose you want to compare the number in cell A1 with the number in cell A2 and you want to have the number 50 in cell B1 if A1 > A2, else 20.
Then you type in a cell not A1, A2 or B1 (e.g. A3): =A1>A2
If A1>A2 is true you will get 1 in A3, else 0.
So you have to put in cell B1: =A3*30+20 to get the result.

[Tim Wessman]

(01-03-2022 06:23 PM)robmio Wrote:  Good evening again:

is it possible in the "spreadsheet" APP to use an "IF THEN ELSE" statement as in EXCEL?

=IFTE(...)

???


You can directly use a lot of spreadsheets out there directly on prime unless they start getting into some of the more advanced stuff.

[robmio]

(01-04-2022 11:50 AM)Tim Wessman Wrote:  

(01-03-2022 06:23 PM)robmio Wrote:  Good evening again:

is it possible in the "spreadsheet" APP to use an "IF THEN ELSE" statement as in EXCEL?

=IFTE(...)

???


You can directly use a lot of spreadsheets out there directly on prime unless they start getting into some of the more advanced stuff.

Dear Tim Wessman, is it possible to solve the following problem with the “spreadsheet” APP?
Figure 1: cell K4 contains the formula "1/(1+EXP(-$O$5-$O$6*I4))" and cell L4 contains the formula "(B4+C4)*(J4*LN(K4)+(1-J4)*LN(1-K4))". Column O5:O6 contains the coefficients of a logistic regression. Box L14 contains the formula “SUM(L4:L13)”. Is it possible, by utilising spreadsheets APP of HP PRIME calculator, to ask to calculate the coefficients "O5" and "O6" in order to maximize the value in cell "L14"? For example, with EXCEL I use "SOLVER" (see figure 2) to get figure 3: if you pay attention, the coefficients "B" in cells O5 and O6 have changed from 0 and 0 to 4.476711 and -0.00721, as well as the value of cell "L14", from -526.792 to -370.546.

Thanks a lot, Roberto.

Reference: Logistic Regression via Solver | Real Statistics Using Excel (real-statistics.com)
https://www.real-statistics.com/logistic...ls-solver/

[robmio]

(01-04-2022 01:18 PM)robmio Wrote:  

(01-04-2022 11:50 AM)Tim Wessman Wrote:  =IFTE(...)

???


You can directly use a lot of spreadsheets out there directly on prime unless they start getting into some of the more advanced stuff.

Dear Tim Wessman, is it possible to solve the following problem with the “spreadsheet” APP?
Figure 1: cell K4 contains the formula "1/(1+EXP(-$O$5-$O$6*I4))" and cell L4 contains the formula "(B4+C4)*(J4*LN(K4)+(1-J4)*LN(1-K4))". Column O5:O6 contains the coefficients of a logistic regression. Box L14 contains the formula “SUM(L4:L13)”. Is it possible, by utilising spreadsheets APP of HP PRIME calculator, to ask to calculate the coefficients "O5" and "O6" in order to maximize the value in cell "L14"? For example, with EXCEL I use "SOLVER" (see figure 2) to get figure 3: if you pay attention, the coefficients "B" in cells O5 and O6 have changed from 0 and 0 to 4.476711 and -0.00721, as well as the value of cell "L14", from -526.792 to -370.546.

Thanks a lot, Roberto.

Reference: Logistic Regression via Solver | Real Statistics Using Excel (real-statistics.com)
https://www.real-statistics.com/logistic...ls-solver/

I forgot to attach the figures

[C.Ret]

(01-03-2022 06:39 AM)robmio Wrote:  Thank you very much C.Ret.
[...]
I cordially greet you, Roberto.

You are welcome.

i was not aware about what a Cox Regression was. By following the links you gave above, I found same documentation.

(01-03-2022 06:23 PM)robmio Wrote:  Is it possible in the "spreadsheet" APP to use an "IF THEN ELSE" statement as in EXCEL?

In the HP Prime Spreadsheets, you don't have to use IF/THEN/ELSE statements. A better approach is to use parameters.

Concerning the example you give in the screen captures from Microsoft Excel, it's easy to make it directly in the HP Prime spreadsheet application using the REGS() function.

The trick is to have the REMS and the p(E) column in one block, since the REGS() instruction use source data (called INPUT_RANGE in the in-screen Help) pairwise.

For the Cox Regression, the best REGS() model is to use the mode 7 as second parameters.
One complication here is that the model is of the form \( y=\frac{L}{1+\alpha.exp({-\beta.x})} \) and you are looking forward for the form \( y=\frac{L}{1+ exp({-a-b.x})} \).

In the following capture, cells in blue are intermediate computations to achieve the fitting through the REGS() function.

   

Your parameters a and b (displayed in red) are deduced indirectly from the results \( L \),\( \alpha \) and \( \beta \) of the REGS() regression.

1. Column REMS (Independent variable x for REGS)
   A1:A10 = User Input Data REMS
2. Column probability p(E) (Dependent variable y for REGS)
   B1 = C1/(C1+D1) and so on in range B1:B10
3. Column Survivors
   C1:C10 = User Input Data Survivor counts
4. Column Death
   D1:D10 = User Input Data Death count
5. Column REG computed fit y^ (blue)
   E1 = $H$2/(1+$H$4*EXP(-$H$6*$A1) and so on in range E1:E10
6. Column p (obtain by correction of REGS's fit)
   F1 = B1/$H$2 due to $H$2 being L instead of unity. And so on in range F1:F10
7. Column LL
   G1 = (C1+D1)*(B1*LN(F1)+(1-B1)*LN(1-F1)) and so on in range G1:G10
   

Summations in row 12.

Other special cells:
H2 = REGS($A$1:$B$10,7,"L") returns L parameter of fit.
H4 = REGS($A$1:$B$10,7,"a") returns \( \alpha \) parameter of fit.
H6 = REGS($A$1:$B$10,7,"b") returns \( \beta \) parameter of fit.

Column 'Odds' remove to spare space.

I am not sure about conversion formulae from \( \alpha \) & \( \beta \) into \( a \) and \( b \). I have to check this more deeply; I am afraid this is only a coincidence.

\( a \approx 302 \times \alpha \) and \( b \approx 1.512 \times \beta \)

In the spreadsheet:
H9 = H4*D12
H11 = H6*D12

If someone in the forum have an explanation. I am a bit confuse here. Perhaps a property of the logistic curves ? We may investigate a bit further there !

Nevertheless, I am please to share all this in the hope this use of the REGS() function may helps anyone to found a simple and practical path to an efficient solution !