Average price formula

Hey Lewis,

Since you’re looking for an aggregate value (i.e. the average price across all orders), the way to do it in Airtable is with a new table and using rollup fields.

I’m slightly confused by your current table, but let’s say it were to look like this (I’ve left the fee out for simplicity):

Orders

• ID
• Price
• Quantity
• Total Value (formula field equal to Price * Quantity)

The new table you create could look like the following:

Summary

• Name
• Items (linked to the first table and including as many items as you want to aggregate)
• Quantity (a rollup field of the sum of quantity from the first table)
• Total Value (a rollup field of the sum of total value from the first table)
• Average Price (a formula field equal to Total Value / Average Price)

Here’s an example I put together so you can visualize it.

Hope that helps! Let me know if anything doesn’t quite make sense.

Current situation/problem

You pretty much have to do it the way @Danny outlined.

Airtable presently does not have a way to do columnar math. That is, while you can use the summary bar to perform a calculation on a column or (by grouping) portions of a column, you cannot access those summaries or sub-summaries in a formula.

However, if you link all of the records you wish to summarize to a record in another table, you can use a rollup formula in the linked-to record to perform a calculation against values in the l.inked-from records. You can then reference the calculated field in the linked-to record from your linked-from record.

Clear as mud?

Maybe this will make things a little clearer — it’s an implementation of the sample calculation you posted, as best I understand it. (You’ll want to duplicate the base so you can explore the field configurations.)

There are two tables, [Table 1], which mimics the one in your screenshot, above, and [Link], which is where the calculations actually take place. All of the records in [Table 1] link to the single record in [Link]. This allows rollup fields in [Link] to sum columns in [Table 1]. Once totals for {Price} and {Net Amount} have been determined, a formula field performs the {Total Price}/{Total net Amount} calculation you desire. Finally, the value of that calculated field iis passed back to [Table 1] via a lookup field.

I realize this isn’t the most intuitive method of performing such a calculation; unfortunately, at present it’s the best option we have.