Difference of cell data from another cell in same column?

[Aaron_Spesard1]

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.)

[Aaron_Spesard1]

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)?

[Mike_McLaughlin]

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.

Airtable - Support

Guide to formula, lookup, rollup, and other computed fields

Computed fields (columns) are special field types where the values in the field are computed by Airtable automatically, rather than directly edited by users. These can be fields that allow you to c...

[Aaron_Spesard1]

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.

[Mike_McLaughlin]

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.

[Mike_McLaughlin]

@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.

Find Arbitrary Item in Lookup(array) - like Vlookup Show and tell

Simple version (so you can cut and paste, and put to use) Link to demo base:TestLab Status Table 1: Latest Status LEFT( RIGHT(status_&"", LEN(status_&"") -FIND({Last Updated}&"",status_&"")-23 ), FIND({Last Updated}&"", RIGHT(status_&"",LEN(status_&"") -FIND({Last Updated}&"",status_&"")-23) )-1 ) Last Updated is just pulling the latest date for the linked records in Status Update Records. TheStatus_ is looking up all of the status-helpers for the linked records so they…

[dmacphee]

This thread is so helpful–THANK YOU!
I was able to use your guidance to solve a similar situation where I need to refer to data cells across different records for a formula. Though, the result I needed to capture from the rollup happens to be a .png attachment (a QR code) and it keeps shows up as a url link to the original image rather than inserting a copy of the image itself. Is there a way to specify that I want the rollup result to reflect the same format (‘attachment’) as the column it is operating on?
Or, as a workaround, is there a way to create a formula that takes the airtable url in the rollup column and returns the actual image attachment in a new column?
Thanks for your thoughts