Skip to main content

Trouble converting an excel filter formula into Airtable

F

Hi all,

I have a sheet of stock data that was too large for excel, so I decided to move it all over to Airtable. But I’m having trouble with one of the formulas I was using.

In excel I’m using the FILTER formula that looks across the column headers (dates) to return the price that relates to a date I enter in another cell. For example, you can see in the image below, I entered the date of 10/11/12 in B2, and with my filter formula in K2 =FILTER(L2:O2,L1:O1=B2) it returns the price that is in N2 (as N1 matches the date I entered in B2).

I see in Airtable there is a formula field type, but I don’t know how to replicate what I’m trying to do in excel… Any help would be much appreciated!

2 replies

Kamille_Parks11
Forum|alt.badge.img+15

There is no equivalent Airtable formula. In Airtable you would filer the View to only show records on a particular date or of a particular ticker.

I have a feeling you’re going to run into Airtable limits no matter which way you tackle your particular usecase. Are you tracking every Ticker in the NYSE? That’s 7,754 rows.

Either you’re going to run out of fields in your current setup (up to 500 per table, so less than 2 years worth of data), or you’re going to run out of records in the standard Airtable setup which would require you to have a junction table where each row is linked to 1 date and 1 ticker (7,754 records added per day, and the limit per table is 50,000 on Pro accounts).

F
  • funkedelic_bob
  • Author
  • New Participant
  • 1 reply
  • October 19, 2022
Kamille_Parks11 wrote:

There is no equivalent Airtable formula. In Airtable you would filer the View to only show records on a particular date or of a particular ticker.

I have a feeling you’re going to run into Airtable limits no matter which way you tackle your particular usecase. Are you tracking every Ticker in the NYSE? That’s 7,754 rows.

Either you’re going to run out of fields in your current setup (up to 500 per table, so less than 2 years worth of data), or you’re going to run out of records in the standard Airtable setup which would require you to have a junction table where each row is linked to 1 date and 1 ticker (7,754 records added per day, and the limit per table is 50,000 on Pro accounts).


Thanks for the response. Your last points won’t be an issue - as it’s not every ticker and it’s only ever a year of data. I wouldn’t hit the limits.

Maybe the data would best be put into a standard SQL database that’s more manipulatable?

Reply


Most helpful members this week

Terms & Conditions