Subtract values by Record ID?

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?

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)

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.

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.

1 Like

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.

Screen Shot 2022-08-15 at 6.52.50 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,

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?

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.

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.

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.

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.