Help

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

Topic Labels: Formulas lookup
Solved
Jump to Solution
2085 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Valentino_Escal
7 - App Architect
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

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.

See Solution in Thread

Valentino_Escal
7 - App Architect
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! 👌🎊

See Solution in Thread

3 Replies 3
Valentino_Escal
7 - App Architect
7 - App Architect

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.

Valentino_Escal
7 - App Architect
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! 👌🎊