Re: Equation Porting from Google Sheets

1298 3
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

I’m looking to replicate a formula from Google Sheets in AirTable.

Screen Shot 2018-12-01 at 7.15.03 PM.png

I have two tabs on a spreadsheet. One tab is a list of Editors (screenshot above) with the name of the editor in column A, an the amount owed in column B. The second tab is Customer Orders, which tracks each order, the editor assigned to the order, and the amount owed for completion of the order.

I’m trying to replicate the functionality such that the Editor tab sums up the amount owed for each editor. As I mark each payment on the Customer Orders tab complete with a “Y”, it is no longer counted toward the amount owed.

I’m having difficulty getting this functionality to work in AirTable. Thank you for your help!

6 Replies 6

You probably want a formula field (let’s call it Amount Outstanding) in the Customer Orders table that contains something like:

(I’m assuming you have a single select Order Paid field with “Yes” and “No” options, and an Order Amount field that contains the amount owed)

if( {Order Paid}=“Yes”, BLANK(), {Order Amount} )

You need a Link To Record field (that links to the Editor table) in the Customer Orders table. You can then assign an editor to each customer order.

Finally you need a Roll Up field in the Editors table. Choose the Amount Outstanding field in the Customer Orders table as the one to roll up. Put SUM(values) in the aggregation function box.

4 - Data Explorer
4 - Data Explorer

Great, thank you for your help. I’m updating my solution here for others’ future reference.

On the Orders table, I created an additional column to keep track of any amount that is not paid (value 240 below). Equation of IF(Paid=’’,Compensation), meaning if the advisor wasn’t paid for completing the order, then “Owed per Order” will tell me how much is outstanding. I manually update Compensation as the orders are completed (status column).

Screen Shot 2018-12-03 at 9.47.23 AM.png

Then the separate Advisors table has a Rollup function where I just sum up the “Owed per Order” that’s in the Orders table (above)

No problem. Just as an aside to help with your data integrity… I would strongly consider changing your Paid column to a single select column with “Yes” as an option. And would also change your Compensation column to a Currency (or, at least, Number) field.

What about a Checkbox?

Yes - probably even better.

4 - Data Explorer
4 - Data Explorer

Done. Thank you both.