Save the date! Join us on October 16 for our Product Ops launch event. Register here.
May 25, 2018 09:02 AM
I love AirTable but am still struggling with writing AirTable Equations. I wish I was as good at AirTable equations as I am at Excel Equations.
In the screen shot attached you see I have Transactions, Expenses, Income, Carryover and Type.
I am trying (so far unsuccessful) to write an equation so that IF the “Type” field is outgoing money (selected as red Expense or orange Payroll) than the “Transaction Amount” appears in “Expense Amount” as a Negative Number. Nothing should appear in Income Amount
Similarly, if the “Type” is incoming money (Selected as the green Income) than the Transaction amount appears in “Income Amount” as a positive number. Nothing should appear in Expense Amount.
The screenshot is a good representation of what I want to see… However, the Expense Amount and Income Amount were manually input to generate the screenshot. I would like these to be automatically input based on “Type” selection. The Type filed is a Single Select drop down. Transaction amount is always input a positive number and only changed to Negative if Type is selected as Expense.
Thank you in advance to anyone who can assist. The AirTable community has proven to be top notch so far.
May 25, 2018 09:27 AM
Formula in Expense Amount
Field:
IF(
OR(
Type = "Expense",
Type = "Payroll"
),
{Transaction Amount} - ({Transaction Amount} * 2),
BLANK()
)
Formula in Income Amount
Field:
IF(
Type = "Income",
ABS({Transaction Amount}),
BLANK()
)
That should do it for you, and it looks like you already have the Carryover Amount
field working the way you want. Let me know if that doesn’t work, or if you’d like an explanation of what I did there. :grinning_face_with_big_eyes:
EDIT:
It’s worth noting that it’s possible to collapse the functions your Expense Amount
, Income Amount
, and Carryover Amount
fields are performing into a single field with a nested IF()
formula - unless, for some reason, you want to see all three of those fields laid out as they are.
May 30, 2018 07:38 AM
Thank you! This is a huge help. I just couldn’t do it on my own. This did the trick!!!
Mar 03, 2022 09:18 AM
Can you tell me the formula that I would use that would collapse the functions your Expense Amount
, Income Amount
, and Carryover Amount
fields are performing into a single field with a nested IF()
formula?