How to operate formulas with specific cells?

[Rafa_Ramos]

Hi!

I am trying to sum certain values ​​from a specific row and column and I don’t know how to do it. Better to explain it with an example:

In this image I am trying to get the following value in the third column: (1 + 2 + 3 + 4) / 4 = 2.5. That is, I am trying to add the 4 items from the second column (1 + 2 + 3 + 4) and divide them by the value from the first column (4). The result will be displayed in the third column.

Like this:
4 1 2.5
4 2 2.5
4 3 2.5
4 4 2.5

All this for various groupings, such as the one shown below.

Like this:
4 1 2,5
4 2 2,5
4 3 2,5
4 4 2,5

7 1 1
7 1 1
7 1 1
7 1 1
7 1 1
7 1 1
7 1 1

Could someone guide me?

Many thanks!!

[Justin_Barrett]

Arbitrary cell reference isn’t possible in Airtable like it is in a spreadsheet. Because Airtable’s data structure is more akin to a database, each row is a record, and each record is independent of all other records.

The only way to “connect” records together is by using a linked record field. This is most often done between tables, but linking to records in the same table is doable. The only downside is that you don’t get automatic reciprocal linking like you do with inter-table linking.

Applying this to your example, you could use links and formulas to pull this off, but the links would need to be made manually (unless you possibly tie in a script to assist). My main question, though, is related to the number that the sum from the center field is divided by. In your examples, the first field always has the same number, but are there cases where the numbers would be different? If so, is the calculation supposed to be different on every record? For example, say the data for the first two fields looks like this:

3 | 1
4 | 2
3 | 3
1 | 4

Would you want the third field calculation to use this logic?

(1 + 2 + 3 + 4) / 3 
(1 + 2 + 3 + 4) / 4
(1 + 2 + 3 + 4) / 3
(1 + 2 + 3 + 4) / 1

[Rafa_Ramos]

Hi Justin!

Thanks for your reply. The answer of your question is that “the number of the first column is always the same”. This is doe to this number is a counter. In the first set of numbers there are 4 files (so the number of the first columns is 4), in the second set of numbers there are 7 files (so the number of the first column is 7), and so on… I know that with other software would be only necessary one number and I should reference the cell of that number to do the division, but in airtable I am not able to reference one cell in that way…

[Justin_Barrett]

Thanks for the clarification. To make this easier, I would suggest moving some of this info over to a second table (I’ll call it [Summary]), with each record in the main table (I’ll call it [Main]) linking to a single record in [Summary]. With those links, the math becomes a lot easier to pull off, and it’s all done in the [Summary] table. Using a Count field type, you can count how many records are linked (7, 4, etc.). Using a rollup field, you can add up the values in one field of those records, then divide that by the count.

Does that make sense?