I have two fields - a rollup field with 3 integer values and a currency field with a single value.
I want to divide the currency field by each value in the array to, hopefully, get a new array that includes the output of each calculation.
This seems like an obvious problem to do on an array but I cannot find the formula to perform the task.
Can someone point me in the right direction? I’m brute forcing this and it sucks.
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.
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:
Point being after an hour of trying to understand the answer I did the following instead
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