Average of field in other table

[wa_vi]

Hi to all,

Anyone can help/hint to this :slightly_smiling_face:

Have a field “formula” in table A
and want in this field the Average of the all field “currency” locate in table B

or better
have a table PRODUCT and want the average of all price offer from table VENDORS (every vendors have the different price for the same products)

any help is appreciate
(from my and all like this)

Thank you

[Chester_McLaugh]

Here’s a working example:
https://airtable.com/shrFuhk28kBB9UniJ

In the Products table:

• Column “Price List” is a Lookup field that creates a list of Prices pulled from the “Vendor Products” table.
• Column “Average Price” is a Formula field that calculates the average with: Average( {Price List} )

In the Vendor Products table:

• Column “Name” is a Formula field that creates a more useful name for the record with
  {Product} & " (" & {Vendor} & " - $" & {Price} & ")"

[Daniel_Robbins]

Chester,
I went to look at your example Base that you link to but the link no longer seems to point to your example. I’m having the same issue in that I don’t seem to be able to convert lookups to actual values so I can’t use them in a formula.

[Chester_McLaugh]

I believe what you’re looking for is the Formatting option on the Price List and Average Price List fields. To make it work properly, I had to make sure to select ‘Currency’ as the format for both fields.

If that isn’t the issue you’re facing, let me know and I’ll send you my email address so you can invite me to your base and I might be able to better help.

Also, I deleted that example a while back, but I’ve recreated it—here’s a read-only version of that Base for reference. If you want to be invited to get a closer look, just private message me with your email address and I’ll share the base with you so you can see all the field settings.

Airtable

Vendors, Products, Prices - Airtable

Explore the "Vendors, Products, Prices" base on Airtable.

[Daniel_Robbins]

Chester,
thanks for writing back. Yes, I’d love to see the forumlae on your
"products" demo base. My email address is:

danrobbins@gmail.com

In terms of the issue I’m trying to address, it has to do with Kanban not
being fully implemented, yet. I want to use Kanban (card sorting) to sort a
bunch of rows in Table A. Currently that only works if the sort field is a
"single select". I am then referencing a bunch of single-select sort
columns from various tables, another table, Table B. I want to do a
calculation (formula) based on the values from those sort columns BUT
because they had to be defined as “single select” they can’t be used in
numerical calculations directly. Putting a “value()” wrapper around them
within the formula doesn’t work either. I did find a workaround, though:
In the “source” tables that each have a “single select” field that I use
for Kanban, make another column that merely has a formula of “value({sort
field})”. Then in the target tables that actually do the calculations
across multiple tables, link to the formula “converter” field, rather than
the original “single select” field. That works although it pollutes my Base
with many extra fields that I then have to hide and manage.
The “fix”, on Airtable’s part, would be to allow Kanban views to sort on
numerical columns.

Thoughts?

[Chester_McLaugh]

Invite sent.

It sounds like you’re doing all that can be done—which for better or for worse tends to result in seemingly extraneous columns.