+12Hi 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! 👌
Best answer by kuovonne
Second attachment 👇
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.
+12Second attachment 👇
+29Second attachment 👇
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.
+12Hi @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! 👌🎊
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.