Formula - Change formula in individual cell


#1

I’m wondering if there is (or could be) a way to change a formula for a particular cell or row? For example: I use airtable to track gigs for artists I represent. Typically, we take x% for every gig. In certain special cases, we may take y%. Is there a way to change the formula just for that cell to reflect the correct totals?

Thanks!


#2

You can’t do this by changing a formula for a specific cell (it’s not a spreadsheet) but you can make a calculation which would allow for this type of thing.

In this instance, I would suggest an additional field called Override % and a formula such as:

if({Override %} = Blank(), Fee * x%, Fee * {Override %})

Note you may also have to divide by 100 - Airtable percentage fields are only a display format.


#3

Alternatively (to @Julian_Kirkness’s suggestion, which is a perfectly one), you could define a {Percent} field with a default value of x%. For those artists where you collect y% or z%, you simply change the value of {Percent} for that row.

It’s a ‘6 of one, half-dozen of the other’ situation — with one exception: If at some point you should change the value of x%, any solution that embeds the value in the formula will recalculate every instance of that formula, including [incorrectly] existing records. Using a default to hold the value of the ‘constant’ makes it persistent to the record and avoids the chance of corrupting historical data.

Note: This approach fails if you create the gig record as one linked from another table. (For instance, if your process has you beginning from an [Artist] record, from where you then select a link that reads “+ Link to a record from gigs.”) Unfortunately, thanks to an Airtable bug, default field values are not honored in records created as linked records. In the past, my solution for that scenario has been to do both, backfilling when required. That is, my formula would read

IF({Percent}=BLANK(),x%,{Percent})

When it came time to change the value of x% globally, you would first need to make sure any BLANK() instances of {Percent} were instead filled with the then-current value of x%, after which you could then change the default value for {Percent} and the default value embedded in the formula.

Again, though, if the value of x% never or very rarely changes, or if you have no need to protect historical records, Julian’s suggestion is easier to implement and maintain.