Math on an array (rollup field)

Welcome to the Airtable community!

Airtable does not have array handling functions to do this. Instead, you need to “roll-down” the currency field to the linked records (with a rollup field), then have the linked records do the division, and finally roll up the quotient.

Depending on the math you are doing, and if you only need to see a final number, you could have the rollup sum the integers and then divide the sum by the currency field.

This is a confusing explanation, but I will give it a shot.

What @kuovonne is saying (as I understand it): do the math on the individual record level (field “number”, field “currency”, field “currency/number”) and get the results in the other table via rollup field on the “currency/number” field, using SUM(values) formula in that field.

This is way more complicated than it seems like it needs to be, so let me give some visuals to explain why the answer confuses me.

I’ve got four interlinked tables related to this question:

One is the rate-skills table. Each individual can have multiple rates because they have multiple skills (company specific data is hidden but the left field is the primary field which is text and the right hidden field is a linked field which links to the rates table)

Second is the rates table. This is just the rates in order - a list of entries of prices which link to the skills table above

Third is the staff table - this is the most complex table and here staff are assigned skills and I have a lookup for rates. (Note, the w9 and role are also links to other tables but they don’t touch the problem)

Then I have the responses table, which includes each person’s answer for expected pay and a rollup of their rates (which is being pulled from the lookup of their rates in the staff table (by linking each response to their entry in the staff table) which in turn is pulling from two nested lookups).

My understanding of your suggestion is that it involves X steps:

1. Create an Expected Pay Rollups in the rates table for each possible rate
   1. Create a link field in the rates table
   2. Assign each response entry (of which there are 40) to the multiple pertinent rates (I assume I cannot do this programmatically I have to look at their rates and manually assign them)
   3. Create 40 roll-up fields for each individual’s expected pay (because a single rollup would create the same problem of a column with an array I cannot run math on)
2. Create 40 Pay by Unit formula columns in rates to run the math on the Expected Pay Rollups
3. Create a link to the rates table in the response table (I am under the impression link fields are 1 way)
4. Create 40 roll ups to pull the calculations back in
5. ? (how do I then put all this data in a single column?)

Point being after an hour of trying to understand the answer I did the following instead

1. Opened Google Sheets to copy in the expected rate
2. Did a basic quotient of the amount and rate type, (I used airtables hidden filter to get the relevant people into a column)
3. Copied that result into the needed output column in airtable.

Tedious, but it took me about 15 minutes and didn’t result in a lot links that I would need to clean up after this report was delivered.

If I’m totally misunderstanding your answer that’s fine, but I would say that is because the answer assumed a level of knowledge about how to do fancy stuff with rollups that, if I had it, would probably result in me not asking the question in the fireplace