Forumula like SMALL

[Mike_Torrence]

I’m looking for a formula that will return the smallest 8 of the last 20 items recorded. Here is the formula that I use in Excel to retrieve the information from a table:

=AVERAGE(SMALL(INDEX(Golfer[Index],ROWS(Golfer[Index])-20):INDEX(Golfer[Index],ROWS(Golfer[Index])),{1,2,3,4,5,6,7,8}))

Any thoughts that this is/can be supported in AirTable?

[Justin_Barrett]

Welcome to the community, @Mike_Torrence! :grinning_face_with_big_eyes: Airtable doesn’t have an existing function that behaves like your description of Excel’s SMALL function (which I’m not familiar with, but that’s not surprising considering that it’s been at least a decade since I’ve touched Excel). That aside, I can think of at least two ways to pull this off:

1. Using a somewhat complex series of connections between tables (purely theoretical, though I think I’ve set up stuff like this before…or at least I’ve tried to test the theory). Pros: it’s dynamic, updating on the fly as the data updates. Cons: it’s messy, and will require at least one new table and several new fields that don’t really do anything other than support this system, though the new table might not be necessary depending on the design of your base.
2. Add the Scripting app and write (or get assistance to write) a script to calculate what you need and output the data where you want it. Pros: no mess. Cons: the script will have to be manually run each time you want to recalculate the smallest 8 of 20. (Note: scripts require your base to be in a Pro-plan workspace or higher, though it’s still free for Free and Plus plans until March.)

A possible third option would be deeper than #2: write (or get assistance to write) a custom app. Pros: total flexibility to make the app do what you want, and it’ll update dynamically as your data changes. Cons: custom apps take more time to develop, partly because they’re at least a couple orders of magnitude more complex than a “plain” script that the Scripting app can run.

[Mike_Torrence]

Thanks for your response. I’m going to be upgrading to a Pro plan to try to script this myself. Does the API support something like Python…

[Justin_Barrett]

Mike_Torrence:

Does the API support something like Python…

If you use the standard REST API to interact with Airtable from an outside source, I believe that Python will work (not 100% sure, as I haven’t yet worked in that type of environment). All internal Airtable scripting—in the Scripting app, in scripting actions in automations, and when building custom apps—is done in JavaScript.

[Mike_Torrence]

Ok. Thanks. I can handle that I think.