"SOLVER" function as in Excel

01012022, 08:17 PM
Post: #1




"SOLVER" function as in Excel
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.realstatistics.com/survival...onsolver/ 

01022022, 03:59 AM
Post: #2




RE: "SOLVER" function as in Excel
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/thread16426.html Perhaps this helps. 

01022022, 05:35 AM
Post: #3




RE: "SOLVER" function as in Excel
(01022022 03:59 AM)rawi Wrote: Hi, Thanks for your invaluable help, best regards, Roberto. 

01022022, 06:44 AM
Post: #4




RE: "SOLVER" function as in Excel
(01022022 03:59 AM)rawi Wrote: Hi, 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 

01022022, 07:44 AM
Post: #5




RE: "SOLVER" function as in Excel
(01022022 03:59 AM)rawi Wrote: Hi, 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 ? 

01022022, 08:21 AM
Post: #6




RE: "SOLVER" function as in Excel
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 

01022022, 11:35 AM
(This post was last modified: 01032022 05:56 AM by C.Ret.)
Post: #7




RE: "SOLVER" function as in Excel
(01022022 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 ?
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. 

01032022, 06:39 AM
Post: #8




RE: "SOLVER" function as in Excel
(01022022 11:35 AM)C.Ret Wrote:(01022022 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 ? 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. 

01032022, 06:23 PM
Post: #9




RE: "SOLVER" function as in Excel
Good evening again:
is it possible in the "spreadsheet" APP to use an "IF THEN ELSE" statement as in EXCEL? 

01042022, 08:47 AM
Post: #10




RE: "SOLVER" function as in Excel
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. 

01042022, 11:50 AM
(This post was last modified: 01042022 11:52 AM by Tim Wessman.)
Post: #11




RE: "SOLVER" function as in Excel
(01032022 06:23 PM)robmio Wrote: Good evening again: =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. TW Although I work for HP, the views and opinions I post here are my own. 

01042022, 01:18 PM
Post: #12




RE: "SOLVER" function as in Excel
(01042022 11:50 AM)Tim Wessman Wrote:(01032022 06:23 PM)robmio Wrote: Good evening again: 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)+(1J4)*LN(1K4))". 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 (realstatistics.com) https://www.realstatistics.com/logistic...lssolver/ 

01042022, 01:22 PM
Post: #13




RE: "SOLVER" function as in Excel
(01042022 01:18 PM)robmio Wrote:(01042022 11:50 AM)Tim Wessman Wrote: =IFTE(...) I forgot to attach the figures 

01042022, 08:22 PM
(This post was last modified: 01052022 06:46 AM by C.Ret.)
Post: #14




RE: "SOLVER" function as in Excel
(01032022 06:39 AM)robmio Wrote: Thank you very much C.Ret. You are welcome. i was not aware about what a Cox Regression was. By following the links you gave above, I found same documentation. (01032022 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 inscreen 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({ab.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.
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 ! 

« Next Oldest  Next Newest »

User(s) browsing this thread: 1 Guest(s)