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} & ")"
  

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.

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.

Vendors, Products, Prices - Airtable

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

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.

Vendors, Products, Prices - Airtable

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

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?

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.