Post Reply 
"SOLVER" function as in Excel
01-04-2022, 08:22 PM (This post was last modified: 01-05-2022 06:46 AM by C.Ret.)
Post: #14
RE: "SOLVER" function as in Excel
(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 ! Smile
Find all posts by this user
Quote this message in a reply
Post Reply 


Messages In This Thread
"SOLVER" function as in Excel - robmio - 01-01-2022, 08:17 PM
RE: "SOLVER" function as in Excel - rawi - 01-02-2022, 03:59 AM
RE: "SOLVER" function as in Excel - robmio - 01-02-2022, 05:35 AM
RE: "SOLVER" function as in Excel - robmio - 01-02-2022, 06:44 AM
RE: "SOLVER" function as in Excel - robmio - 01-02-2022, 07:44 AM
RE: "SOLVER" function as in Excel - C.Ret - 01-02-2022, 11:35 AM
RE: "SOLVER" function as in Excel - robmio - 01-03-2022, 06:39 AM
RE: "SOLVER" function as in Excel - rawi - 01-02-2022, 08:21 AM
RE: "SOLVER" function as in Excel - robmio - 01-03-2022, 06:23 PM
RE: "SOLVER" function as in Excel - robmio - 01-04-2022, 01:18 PM
RE: "SOLVER" function as in Excel - robmio - 01-04-2022, 01:22 PM
RE: "SOLVER" function as in Excel - C.Ret - 01-04-2022 08:22 PM
RE: "SOLVER" function as in Excel - rawi - 01-04-2022, 08:47 AM



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