Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Dec 01, 2022 11:47 AM
Hi Airtable Community!
In essence, I want to be able to divide multiple numbers from lookup field (Quantity / Count). The formula that I have returns the value of 1.000 if there is more than one number in the lookup field (Quantity).
I have attached two screenshots below 👇
Thanks! 👌
Solved! Go to Solution.
Dec 01, 2022 03:08 PM
It looks like you want to divide *each* of the values in the {Quantity} lookup field by the rolled up {Count}. However, in Airtable, math doesn't work when there are multiple values in a lookup field.
You can use a system of back-and-forth rollups to pass the {Count} down to the linked table. Then each linked record can divide its {Quantity} by the total {Count}. Finally, lookup the quotient from the linked records. This system does not work for some base schemas but works quite well in others, especially when there is only handful of linked records for each main record.
On the other hand, if you want to divide the *sum* of the {Quantities} by the {Count}, you could turn the {Quantity} into a rollup field with the formula SUM(value). Then you would have only one value in the {Quantity} field.
Dec 02, 2022 08:46 AM
Hi @kuovonne,
Thanks so much for taking the time to reply and give various examples of workarounds.
The solution that you provided in one your examples worked in this case.
The workaround was to create a lookup field for the {count} in the table where the {quantity} originated. Then I created the same formula (Airtable Test 2.jpg) in the {quantity} table. Then creating a lookup field for the formula in the original table and works like a charm.
I have provided the attachment below 👇
Thanks again, I really appreciate it! 👌🎊
Dec 01, 2022 11:48 AM
Dec 01, 2022 03:08 PM
It looks like you want to divide *each* of the values in the {Quantity} lookup field by the rolled up {Count}. However, in Airtable, math doesn't work when there are multiple values in a lookup field.
You can use a system of back-and-forth rollups to pass the {Count} down to the linked table. Then each linked record can divide its {Quantity} by the total {Count}. Finally, lookup the quotient from the linked records. This system does not work for some base schemas but works quite well in others, especially when there is only handful of linked records for each main record.
On the other hand, if you want to divide the *sum* of the {Quantities} by the {Count}, you could turn the {Quantity} into a rollup field with the formula SUM(value). Then you would have only one value in the {Quantity} field.
Dec 02, 2022 08:46 AM
Hi @kuovonne,
Thanks so much for taking the time to reply and give various examples of workarounds.
The solution that you provided in one your examples worked in this case.
The workaround was to create a lookup field for the {count} in the table where the {quantity} originated. Then I created the same formula (Airtable Test 2.jpg) in the {quantity} table. Then creating a lookup field for the formula in the original table and works like a charm.
I have provided the attachment below 👇
Thanks again, I really appreciate it! 👌🎊