Aug 11, 2022 11:23 AM
Hi everyone, I’m trying to solve a basic issue with Airtable formulas…
I want to subtract the value of one cell from another. For example, in excel, it’s subtracting cell E2 from E3…
In Airtable, I learned about the Record ID that I can get from the cell URL, however I don’t see examples of how to write a formula referencing the values of that cell.
Can someone show me how to subtract the values of 2 cells, that are each specified by a Record ID?
Aug 11, 2022 12:18 PM
Hi @Rob_Jameson,
Every Record(row) will have a unique Record_ID, the Cell is a mapping of Record and Field(column).
A formula type Field can perform calculations on any Field in its Record.
If you have two number type fields and you want to subtract them add a formula type field and your formula would be:
{number 1}-{number 2}
If you wish to calculate numbers from different Records, you can link/lookup and then use a Roll Up field to SUM(values)
Aug 11, 2022 12:57 PM
Hi @Rob_Jameson
Technically this is not a straightforward thing, the record ID can help in certain ways (although I don’t know what is the use case where you would have said record ID)
IF you have the record ID, you can link them in separate fields, have lookup / rollup fields to do the calculations
You can explain a bit more about the use case so we can help you better.
Aug 11, 2022 03:57 PM
You cannot reference a specific cell using a formula field. A formula field acts on an entire column at a time, not specific cells.
This is one of the fundamental differences between a database system and a spreadsheet.
Record IDs are not particularly useful for formula inputs. They are useful when working with scripts or record urls. Record IDs also refer the entire row, not a specific cell.
Aug 15, 2022 03:58 AM
Thanks for the insights everyone… Good to learn about fields/records and about differences between an airtable database vs spreadsheet. This is all new for me.
That said, I’m still not clear how to solve this question. See the image. I just want to subtract these 2 numbers. What is the best way to do this? 21-1 … two different records in the same field.
Aug 15, 2022 04:45 AM
Your screenshot doesn’t give enough information. What is your database trying to do? What do those numbers represent? How are those 2 records related to each other?
As others have mentioned above, Airtable is not a spreadsheet, so you can’t simply subtract those 2 numbers that you are showing in your screenshot,
Aug 15, 2022 10:41 AM
hi Scott,
Those numbers are just sums from other records in my table that I calculate with a counting field. I didn’t include much more information, because I’m trying to learn how to do something generic…that I would like to do in various circumstances:
To subtract two values in the same Field.
i.e. if I have a blank table with only two values (say 21 and 1) in the same number Field, how could I subtract them?
Aug 15, 2022 12:15 PM
That is not possible to do natively in Airtable. You can not perform math on values from 2 different rows that are not linked to one another.
One possible way of doing math on 2 unlinked rows might be to write your own custom JavaScript script, but I’m not a JavaScript coder so I wouldn’t know the feasibility of that.
Aug 15, 2022 12:48 PM
interesting! Ok, this is good to know… I thought I was missing something obvious. It’s fascinating something this simple in a spreadsheet is not in airtable.
I do not know JS either… so I’m not sure how to proceed.
Aug 15, 2022 01:59 PM
Airtable is not a spreadsheet, it is a database.
It sounds like you need a spreadsheet. if so, you can always use a tool like Make.com to send your data from Airtable to Google Sheets or Microsoft Excel, and then you can do your spreadsheet functions there.