# Difference of cell data from another cell in same column?

#1

Is it possible to have a formula that will calculate difference from one cell to another cell in the same column? Basically I’m just subtracting A1-a30, A2-A30, A3-A30, etc. and putting the value in B1, B2, B3, etc.)

#2

Ok, so I found out how to group the records by month, but now I’m having trouble doing the calculation of the current cell from the last cell in that group. Basically, Any idea how to reference the last row in the current group so the formula would like Close - (Close(last record in group)?

#3

Directly like a spreadsheet: no.

Workaround: Airtable is much more a relational database than a spreadsheet, as such one row in a table does not inherently ‘know’ that any other row exists. The address of a row (record) does not necessarily have a common reference structure like record 1,2,3,… so you can’t get a result from 2 records without explicitly telling a record to pull data from another record.

A way to do this would be to create a link between 2 rows of interest, as follows:

• Create a linked record field, referring to the same table itself, and the field with the number you want to work with in that field.

• create a rollup field, referring to the other record of interest

• Create a formula field to subtract the value existing in the row from the rolled up value

I tested the method I’m suggesting to make sure there were no circular reference issues.

1 Like
#4

Thanks for the response. Can you point me to a tutorial where I can return value X from a record based on a rollup (or formula) finding the max of value Y? I thought maybe FIND(MAX(),{Date},{Number}) would do it but that not right. I’m just trying to return the Number from the record with the latest date, so that last column should be all 3.

#5

I think I’ve got the workaround you need, you can look at my posts under ‘show and tell’ for the lengthy version, but I’ll try to summarize and post the concise version I use in practice tomorrow.

#6

@Aaron_Spesard1 see link below, I just updated it with the abridged (Simple Version) instructions, so I think it will do exactly what you need.

#7