Skip to main content

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.

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.

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

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.


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?

Reply


Terms & Conditions