Airtable Cobuilder is here! Learn more about our new no-code app creation feature, powered by AI on the Airtable Academy

Dividing numbers in a lookup field separated by a comma (,)

Topic Labels: Formulas lookup
Solved
2795 3
cancel
Showing results for
Did you mean:
7 - App Architect

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! 👌

2 Solutions

Accepted Solutions
18 - Pluto

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.

7 - App Architect

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! 👌🎊

3 Replies 3
7 - App Architect

Second attachment 👇

18 - Pluto

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.

7 - App Architect

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! 👌🎊