Subtract values by Record ID?

[Rob_Jameson]

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?

[Vivid-Squid]

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)

[Mohamed_Swella1]

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.

[kuovonne]

Rob_Jameson:

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.

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.

[Rob_Jameson]

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.

[ScottWorld]

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,

[Rob_Jameson]

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?

[ScottWorld]

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.

[Rob_Jameson]

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.

[ScottWorld]

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.