Hey!
I am trying to calculate the percent change between two numbers in the same column, just different rows. If I were doing it in Excel, it would be =(B9-B3)/B3 but I can’t seem to find the way to make this formula work in Airtable. Is it possible?
These are the two rows that I am trying to calculate for:
Hopefully someone knows how to help! Thank you!
Hey @Jackie,
Despite Airtable’s advertising language, you can’t really treat your tables as spreadsheets.
It looks like you are trying to subtract the value in {Received to Photo} from record #11 from the value in {Received to Photo} from record #12. The problem is that in a database (which is what Airtable actually is, as opposed to a spreadsheet) the records do not have that same spatial relationship to each other. In a spreadsheet, you can say,
Subtract the value directly above this one from this, then divide by the value directly above this one
because the “value directly above this one” does not change in a spreadsheet.
But in a database, the values are not “above” or “below” each other. The database doesn’t care where it is showing a value to exist. You can see this by the fact that if you were to filter your records to show only records where {Week number} = 11, you will be looking at a different collection of records, and the “value above this one” will be different for any given record than it was previously. You’ll have a different “Record #11”. There is no concept of “Column B, Cell 9”.
This means that what you are trying to achieve there is not possible in the simple way you are trying to do it.
A way to work around this is to use another Table as an intermediary between two records in the same table.
Your second table would link to records from the table you screengrabbed above. Each record would have it’s own corresponding record in the new table, and that record in the new table would “Lookup” the value in the {Received to Photo} field, so that another record in your original table can access it.
I think the easiest way to do this might be to first create an Autonumber field in your original table. This will give each new record (which appears to represent a “week” of the year) a unique and sequentially incrementing number.
Right next to that field, create a Linked Record field linking to the new table. Then, whenever you create a new “Week” record, you can just copy/paste the Autonumber value into the link field to create a new record in the new table that corresponds with your “Week” record (and you can do this in bulk for your existing records).
Next, in your original table, make a Formula field that has this formula in it:
Autonumber - 1
This represents the name of the record you want to access from the new table – namely, the record prior to it.
Next to this field, create ANOTHER Linked Record field linking to the new table (yes, you will have two linked record fields linking your original table with the new one). Copy and paste this {Autonumber-1} value into this new linked record field.
For BOTH Linked Records fields, make sure you only allow 1 linked record per field to avoid errors:
In your NEW table, make a Lookup Field that finds the {Received to Photo} value from the FIRST linked record:
And then look that value up again in your ORIGINAL table, based on the SECOND linked record field:
Now you have both values available in each “Week” record to use for calculating your % difference:
Here’s that formula in a copy/pastable format:
IF(
{Previous Value},
(({Received to Photo - {Previous Value}) / {Previous Value}) * 100
)
The end result looks like this:
It takes a little more work to maintain, but copy-pasting two values isn’t that bad – and it gets you what you want, assuming every new “Week” record you make will always be sequentially subsequent to the record made before it.
Let me know if you need any further help setting that up.
Hey @Jackie,
Despite Airtable’s advertising language, you can’t really treat your tables as spreadsheets.
It looks like you are trying to subtract the value in {Received to Photo} from record #11 from the value in {Received to Photo} from record #12. The problem is that in a database (which is what Airtable actually is, as opposed to a spreadsheet) the records do not have that same spatial relationship to each other. In a spreadsheet, you can say,
Subtract the value directly above this one from this, then divide by the value directly above this one
because the “value directly above this one” does not change in a spreadsheet.
But in a database, the values are not “above” or “below” each other. The database doesn’t care where it is showing a value to exist. You can see this by the fact that if you were to filter your records to show only records where {Week number} = 11, you will be looking at a different collection of records, and the “value above this one” will be different for any given record than it was previously. You’ll have a different “Record #11”. There is no concept of “Column B, Cell 9”.
This means that what you are trying to achieve there is not possible in the simple way you are trying to do it.
A way to work around this is to use another Table as an intermediary between two records in the same table.
Your second table would link to records from the table you screengrabbed above. Each record would have it’s own corresponding record in the new table, and that record in the new table would “Lookup” the value in the {Received to Photo} field, so that another record in your original table can access it.
I think the easiest way to do this might be to first create an Autonumber field in your original table. This will give each new record (which appears to represent a “week” of the year) a unique and sequentially incrementing number.
Right next to that field, create a Linked Record field linking to the new table. Then, whenever you create a new “Week” record, you can just copy/paste the Autonumber value into the link field to create a new record in the new table that corresponds with your “Week” record (and you can do this in bulk for your existing records).
Next, in your original table, make a Formula field that has this formula in it:
Autonumber - 1
This represents the name of the record you want to access from the new table – namely, the record prior to it.
Next to this field, create ANOTHER Linked Record field linking to the new table (yes, you will have two linked record fields linking your original table with the new one). Copy and paste this {Autonumber-1} value into this new linked record field.
For BOTH Linked Records fields, make sure you only allow 1 linked record per field to avoid errors:
In your NEW table, make a Lookup Field that finds the {Received to Photo} value from the FIRST linked record:
And then look that value up again in your ORIGINAL table, based on the SECOND linked record field:
Now you have both values available in each “Week” record to use for calculating your % difference:
Here’s that formula in a copy/pastable format:
IF(
{Previous Value},
(({Received to Photo - {Previous Value}) / {Previous Value}) * 100
)
The end result looks like this:
It takes a little more work to maintain, but copy-pasting two values isn’t that bad – and it gets you what you want, assuming every new “Week” record you make will always be sequentially subsequent to the record made before it.
Let me know if you need any further help setting that up.
Thank you SO MUCH for this comprehensive answer! I will try this out to see how well it works for us.
I really appreciate it!
Hey @Jackie,
Despite Airtable’s advertising language, you can’t really treat your tables as spreadsheets.
It looks like you are trying to subtract the value in {Received to Photo} from record #11 from the value in {Received to Photo} from record #12. The problem is that in a database (which is what Airtable actually is, as opposed to a spreadsheet) the records do not have that same spatial relationship to each other. In a spreadsheet, you can say,
Subtract the value directly above this one from this, then divide by the value directly above this one
because the “value directly above this one” does not change in a spreadsheet.
But in a database, the values are not “above” or “below” each other. The database doesn’t care where it is showing a value to exist. You can see this by the fact that if you were to filter your records to show only records where {Week number} = 11, you will be looking at a different collection of records, and the “value above this one” will be different for any given record than it was previously. You’ll have a different “Record #11”. There is no concept of “Column B, Cell 9”.
This means that what you are trying to achieve there is not possible in the simple way you are trying to do it.
A way to work around this is to use another Table as an intermediary between two records in the same table.
Your second table would link to records from the table you screengrabbed above. Each record would have it’s own corresponding record in the new table, and that record in the new table would “Lookup” the value in the {Received to Photo} field, so that another record in your original table can access it.
I think the easiest way to do this might be to first create an Autonumber field in your original table. This will give each new record (which appears to represent a “week” of the year) a unique and sequentially incrementing number.
Right next to that field, create a Linked Record field linking to the new table. Then, whenever you create a new “Week” record, you can just copy/paste the Autonumber value into the link field to create a new record in the new table that corresponds with your “Week” record (and you can do this in bulk for your existing records).
Next, in your original table, make a Formula field that has this formula in it:
Autonumber - 1
This represents the name of the record you want to access from the new table – namely, the record prior to it.
Next to this field, create ANOTHER Linked Record field linking to the new table (yes, you will have two linked record fields linking your original table with the new one). Copy and paste this {Autonumber-1} value into this new linked record field.
For BOTH Linked Records fields, make sure you only allow 1 linked record per field to avoid errors:
In your NEW table, make a Lookup Field that finds the {Received to Photo} value from the FIRST linked record:
And then look that value up again in your ORIGINAL table, based on the SECOND linked record field:
Now you have both values available in each “Week” record to use for calculating your % difference:
Here’s that formula in a copy/pastable format:
IF(
{Previous Value},
(({Received to Photo - {Previous Value}) / {Previous Value}) * 100
)
The end result looks like this:
It takes a little more work to maintain, but copy-pasting two values isn’t that bad – and it gets you what you want, assuming every new “Week” record you make will always be sequentially subsequent to the record made before it.
Let me know if you need any further help setting that up.
This solution still works! And now that we have automations, no need to copy and paste when you can automate those updates. I was so close to having the right setup, just missed one thing that I fixed from your clear and helpful instructions. 😁
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
