I would like to type various pieces of a formula as plain text in different cells and then assemble some of those pieces into a working formula in another cell.
I know how to concatenate pieces of text and can form the text of a function, but the assembled text does not operate like a function.
This would be a sort of Frankenstein's function but I do not know how to bring it to life.
[Re-Titled by Moderator]
If your assembled formula is in plain text and when you assembled it you gave it a leading = then switching the Data Format to Automatic will activate (if it's already Automatic then try Text and switch back again to Automatic).
Here I assembled a formula in E2, copied, and Edit > Paste Formula Results into E3, and forced E3 to Automatic.
If you've assembled a lot of these "text" formulas then you can bulk activate them using a script like this:
tell application "Numbers" to tell front document to tell active sheet
tell (first table whose selection range's class is range)
repeat with c in cells
tell c
if its value begins with "=" then
set its format to automatic
end if
end tell
end repeat
end tell
end tell
SG
If your assembled formula is in plain text and when you assembled it you gave it a leading = then switching the Data Format to Automatic will activate (if it's already Automatic then try Text and switch back again to Automatic).
Here I assembled a formula in E2, copied, and Edit > Paste Formula Results into E3, and forced E3 to Automatic.
If you've assembled a lot of these "text" formulas then you can bulk activate them using a script like this:
tell application "Numbers" to tell front document to tell active sheet
tell (first table whose selection range's class is range)
repeat with c in cells
tell c
if its value begins with "=" then
set its format to automatic
end if
end tell
end repeat
end tell
end tell
SG
I don't think you can do this automatically.
There is a function FORMULATEXT() which returns a string showing the formula behind a referenced cell, but there's no reverse on this to take a string and turn it into a formula.
Computationally, I think this would be very hard since the app would have to do a lot of text parsing to decode the string.
I think the closest you can get is to copy the resulting text then select your target cell and press = (to open the Formula Editor) and pasting in the result. It will be disconnected from the origin cell (so changes there won't automatically populate), but it would be a working formula.
I don't think so - LAMBDA() lets you embed a function within a cell, but I can't (yet?) see how to get it to interpret arbitrary text as a function.
Thanks very much for this. It's a clear and well constructed response and the approach works for me. I would probably never have thought to try it on my own.
It is important to appreciate that you are constructing one formula by the operation of another. Of course, that's the whole point, but when you are working with them in a sheet you need to be careful to handle each in the appropriate way, according to whether it should be treated by Numbers as a formula or a result.
It seems that "formula" is effectively an implicit type in Numbers that can be automatically recognized if you trigger the Automatic formatting in a cell. Is there a reason why "Formula" isn't available an explicit format option?
What would you do if you had a formula in a cell and you want its result to display as, say, a Number?
it's not so simple! Choices in the Data Format drop-down can affect both the data type (is it text? is it a number? is it a point in time? is it a span of time?) and how the data is to be displayed.
SG
Agreed. I would prefer it, and think many things would be clearer, if Numbers had explicit data types that were distinct from formats. (Also functions, i.e., formulas, that would support reasonable conversions between types.)
It might also make a distinction (and maybe it does somehow) between stored values and displayed values.
Not having to explain those sorts of distinctions to novice or casual users might make the tool more accessible to them--which is no small thing. But the tool does have to deal in some way with concerns in any case, and more experienced users, or users with more complex applications, are probably bound to stumble over them.
Will the newly introduced LAMBDA or LAMBDA.APPLY function do this or something like it? I can't upgrade to find out for myself.
How to create a functioning formula from plain text in Numbers?
