I have created a shortcut that add a record to the end of a Numbers table. I would like to add this formula to one of the columns IF(B65="Ending",C65−C64,0). I can get the formula added to the table but it acts as a text field and not a formula. How do I get Numbers to recognize it as a formula? Also, the rows will be different every time so how do I determine the current row in a shortcut?
Thanks in advance.
Put an = at the front of it. When it is inserted into the cell it becomes a formula.
I do not see anything in Shortcuts that will tell you the row, other than writing a short AppleScript that spits it out or spits out the full text of the formula. A different way to write your formula that does not require the row to be known is to use INDEX, ROW, and some "whole column" addressing. If the row you are inserting is row 65 then instead of
=IF(B65="Ending",C65−C64,0)
use
=IF(B="Ending",C−INDEX(C,ROW()−1),0)
The B and the first C in this formula refer to the cells in the same row as the formula (B65 and C65) because Numbers is expecting a single cell/value, not a range, for that data. The second C is the entire column because Numbers expects that parameter to be a range. ROW() will be 65 so INDEX(C,ROW()-1) will be cell C64
Put an = at the front of it. When it is inserted into the cell it becomes a formula.
I do not see anything in Shortcuts that will tell you the row, other than writing a short AppleScript that spits it out or spits out the full text of the formula. A different way to write your formula that does not require the row to be known is to use INDEX, ROW, and some "whole column" addressing. If the row you are inserting is row 65 then instead of
=IF(B65="Ending",C65−C64,0)
use
=IF(B="Ending",C−INDEX(C,ROW()−1),0)
The B and the first C in this formula refer to the cells in the same row as the formula (B65 and C65) because Numbers is expecting a single cell/value, not a range, for that data. The second C is the entire column because Numbers expects that parameter to be a range. ROW() will be 65 so INDEX(C,ROW()-1) will be cell C64
Hi Joseph,
An interesting question!
My questions:
More information may lead to a solution!
Regards,
Ian.
My questions:
josephfromwallagrass wrote:
I have created a shortcut that add a record to the end of a Numbers table... I can get the formula added to the table ...
What does your shortcut look like?
SG
Perfect!! Your formula does exactly what I want. Thanks so much for taking time to help solve the problem.
Shortcut to create a dynamic formula in Numbers
