Help

Trouble converting an excel filter formula into Airtable

Topic Labels: Formulas
504 2
cancel
Showing results for 
Search instead for 
Did you mean: 
funkedelic_bob
4 - Data Explorer
4 - Data Explorer

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).

image

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!

image

2 Replies 2

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?