
×InformationFix Windows 10 Update Issues
Resolve Windows 10 related issues for your HP computers or printers by HP Windows 10 Support Center

×InformationFix Windows 10 Update Issues
Resolve Windows 10 related issues for your HP computers or printers by HP Windows 10 Support Center
 HP Community
 >
 Other Products
 >
 Calculators
 >
 How to use the HP Prime Solve app in the Spreadsheet app? ...
 Mark Topic as New
 Mark Topic as Read
 Float this Topic for Current User
 Bookmark
 Subscribe
 Mute
 Printer Friendly Page
Create an account on the HP Community to personalize your profile and ask a question
Solved!
How to use the HP Prime Solve app in the Spreadsheet app? Ref: Excel add in 'Solver'
01282020 03:19 PM
MS Excel has an add in called 'Solver'. In it you are asked for an objective cell, a value to shoot for (like 0.000001), and another cell called 'By changing variable cell:'. It also asks for a solving method such as 'GRG Nonlinear'. My question is: Does the HP Prime spreadsheet have anything like this? Maybe using the Prime's Solver app within its spreadsheet? If so, could you please tell me how to use it? I am trying to write a spreadsheet to calculate the tension in a catenary cable which requires iterating a value using a computer type solver. Thank you for any help.  Jimpilot1
Solved! Go to Solution.
01282020 11:48 PM
Hello,
Use the CAS fsolve function.
For example, I set, A1 as =fsolve(x²2*x1=B1,x) and a numerical value in B1.
Note, since this A1 is a CAS formula, when you enter it in the spreadsheet, you need to click on the "cas" menu item BEFORE pressing the enter key, else you will get an error.
With Prime, no need for addins! it's build in...
Cyrille
01292020 11:51 AM
Thank you, Cyrille. I tried the fsolve function and it seemed to work. Actually it did work as soon as I entered the CAS formula. That is, I got the right answer for the input data I had entered. However, when I entered different input data, the answer remained the same as for the old input data. Is there a way to force a recalculation for the spreadsheet? Again, thank you very much for your help.  Jim
01302020 12:05 AM
hello,
What formula did you use?
You got an answer as soon as you entered the formula because an empty cell returns 0...
But normally, all the cell recalculate when you change a cell, so it should have changed, unless the result for the different value you entered is the same (or the first characters look the same) as the result for 0.
Cyrille
01302020 09:02 AM
Hi Cyrille  The formula is: =fsolve((D3+D4)/2(B2/12+X)/X=D2,X) The formula is in cell D5 and shows {} which I guess is the calculator's way of saying that the formula will be calculated in CAS. The iteration of the formula should drive the value towards zero while driving the value in cell D2 towards a value used to solve the catenary problem. If you would like, I would be happy to send you the complete spreadsheet. It only consists of 3 columns and 7rows.  Jim
01302020 10:08 AM
Hi again  To save time I'll show my complete spreadsheet here:
Catenary Cable:
A1: Hor. Dist. (Ft) B1: 5.00 D1: =B1/2
A2: Ver. Dist. (In) B2: 0.5 D2: 75.42 (Number generated by fsolve function)
A3: Cable Wt. (lbs/Ft) B3: 0.029 D3: =EXP(D1/D2)
A4: Cable Length (Ft.) B4: =(D6D7)*D2 D4: =1/D3
A5: Cable Tension (lbs) B5: =( B2/12+D2)*B3*32.174 D5: =fsolve((D3+D4)/2(B2/12+X)/X=D2,X)
D6: =EXP(D1/D2)
D7: =1/D6
Cells B1, B2, and B3 are the input data cells. Cells B4 and B5 are the output data cells. I hope that the spacing
and format won't be messed up when I post this! Thank you so much, Cyrille, for helping me.  Jim
01312020 12:23 AM
Hello,
I think that you are misunderstanding the fsolve line...
it is trying to find X so that the expression (D3+D4)... is equal to D2. It will NOT change the value of D2...
Here is what I have, and the value displayed in D5 changes when I change B1, B2 or B3...
I am not understanding the working of your system... Is this an iterative thing where you take the previous result of the fsolve and copy it to D2 and restart?
In this case, you would need to change the formula in D5 to
=D2:=fsolve(((D3+D4)/2)(((B2/12)+X)/X)=D2,X)
so that the result of the fsolve does get stored in D2... But then you need to force the spreadsheet to recalculate the whole sheet again and again...
The spreadsheet will recalculate anytime somethign is changed by the user (the value of a cell).. Do this by manually entering a value in any cell repetitively..
Alternatively, you can force the spreadsheet to recalculate every time it displays (on every key or touch press)... To do this, turn caching OFF. This is an option in the "format" menu of the cell 0,0 (the one with the HP logo).
I did do these changes, but the spreadsheet does not seem to converge toward a solution (it does converge, but the result is not a solution to the problem as far as I can tell).. maybe something to debug... Good luck! and I hope that this helped!
Cyrille
01312020 08:38 PM
Hi Cyrille  It seems that we are getting closer. I changed your D5 formula to:
=D2:=fsolve(((D3+D4)/2)(((B2/12+D2)/D2=.000001,D2) I turned caching NO and can now force the spreadsheet to recalculate. However, with each iteration the value in cell D5 just grows. It should be going towards zero  or at least towards 0.000001. I'll post another Catenary spreadsheet than I have written in which the user guess values for cell D2. If you play with this 'manual' spreadsheet you will see what I am trying to do with fsolve. (Automate the spreadsheet so that the user will not have to input guesses.) Thanks for your help!  Jim
01312020 09:52 PM
Hi again  Here is my 'manual' Catenary Cable' spreadsheet:
Catenary Cable:
A1: Hor. Dist. (Ft) B1: 5.00 D1: =B1/2
A2: Ver. Dist. (In) B2: 0.5 D2: =EXP(D1/B4)
A3: Cable Wt. (lbs/Ft) B3: 0.029 D3: =1/D2
A4: GUESS > B4: 75.01 D4: =EXP(D1/B4)
A5: Make 0 By Guessing > B5: =(D2+ D3)/2(B2/12+B4)/B4 D5: =1/D4
A6: Cable Length (Ft.) B6: =(D4D5)*B4
A7: Cable Tension (lbs) B7: =( B2/12+B4)*B3*32.174
Cells B1, B2, and B3 are the input data cells. Cell B4 is any guess within reason. Cell B5 goes towards 0 the nearer cell B4 gets to cell B5 solution. User narrows down the guesses by looking at the sign (+/) in cell B5. Cells B6 and B7 are the output data cells. I fill (format) cell B5 with yellow so as to not accidentally put my guesses in a wrong cell.
I use this spreadsheet when designing control cables runs for my homebuilt experimental airplanes. My calculations would go a lot faster if we can automate the guess work. Thank you again, Cyrille, for your help.  Jim
Didn't find what you were looking for? Ask the community