# Average price formula

#1

HI, How can I get this same excel average price on AT? I can’t put the SUM(Price)/SUM(amount) formula into the ormula field.

#2

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.

#3

Current situation/problem

#4

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.