Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

IF Equations for Selecting Cell and +/- Values

1936 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Paul_D22
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

snip expense equation.PNG

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.

3 Replies 3

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.

Paul_D22
5 - Automation Enthusiast
5 - Automation Enthusiast

Thank you! This is a huge help. I just couldn’t do it on my own. This did the trick!!!

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?