Post Reply 
How to set Spreadsheet formula from string?
04-16-2016, 02:18 AM
Post: #1
How to set Spreadsheet formula from string?
Hi all,

Another spreadsheet question. I'd like to be able to assign the formula for a spreadsheet cell where the desired formula is in string form. For example, if the string is "=SUM(D1:D10)" and the target cell is D11, I can't just assign the string to Cell(11,4,1). Various attempts to use eval and expr have at best evaluated the expression and stored the result as a value.

I guess what I'm looking for is a function that will convert a string object to a function object. The reason for my interest can be found in the post "RTable: Load and Save CSV Files" here in the HP Prime Software Library forum.

Thanks,
~Mark

Oh yeah, a shout-out to Diego for his Flex-PCB. I'm trying to restore my first HP-41 - it's a great follow-up to the WP 34 project a little earlier!

Remember kids, "In a democracy, you get the government you deserve."
Find all posts by this user
Quote this message in a reply
04-16-2016, 07:56 AM
Post: #2
RE: How to set Spreadsheet formula from string?
Bonjour

Je ne sais pas si cela peut vous être utile, mais si vous écrivez :

I do not know if this can be useful , but if you write :

Code:

EXPR("D11:=QUOTE(SUM(D3:D10))")

Vous retrouverez bien la formule SUM(D3:D10) dans D11
Testé dans Home mais dans un programme.

You will find out the formula SUM(D3:D10) in D11
Tested in Home but in a program.

Sorry for my english
Find all posts by this user
Quote this message in a reply
04-16-2016, 09:30 AM
Post: #3
RE: How to set Spreadsheet formula from string?
If you type the string without "=" then EXPR will evaluate it. See the example:

   

Hope this helps.

-Paul
Find all posts by this user
Quote this message in a reply
04-17-2016, 12:26 AM
Post: #4
RE: How to set Spreadsheet formula from string?
Bonjour Tyann,

The QUOTE function was the key to the puzzle I had. Things are now working. I have found that formulas in lists will often be evaluated when the list is acted upon, even when the list is just assigned to another list!

Merci beaucoup! (forgive my Google Translate French Smile )
~Mark

Remember kids, "In a democracy, you get the government you deserve."
Find all posts by this user
Quote this message in a reply
04-17-2016, 04:12 PM
Post: #5
RE: How to set Spreadsheet formula from string?
Remember that QUOTE( ) is also just the spelled out long form of ' ' found on the shift paren key.

A formula in the spreadsheet is simply a stored algebraic object. This is why in RPN for example just doing 'A1+A2' works rather then the algebraic =A1+A2.

TW

Although I work for HP, the views and opinions I post here are my own.
Find all posts by this user
Quote this message in a reply
04-18-2016, 02:51 AM
Post: #6
RE: How to set Spreadsheet formula from string?
(04-17-2016 04:12 PM)Tim Wessman Wrote:  Remember that QUOTE( ) is also just the spelled out long form of ' ' found on the shift paren key.

Thanks for the clarification. I'm familiar with the behavior of quotes from my 48/50 days. I was trying to make the round trip from spreadsheet cell formula to string to file, then back again. I got stuck at the transform from string to formula prior to assignment to cell.

I'll use the quote form for transport in a program in the future, since pure formulas seem to get evaluated at inopportune times!

~Mark

Remember kids, "In a democracy, you get the government you deserve."
Find all posts by this user
Quote this message in a reply
Post Reply 




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