Skip to main content

I'm new to Airtable formulas, so I would appreciate your input.

I created a formula that I absolutely KNOW was working. But suddenly the field where the calculation was says #ERROR! now although it said 100% before. I can't figure out what has changed because I didn't change the fields used in the formula.

I would appreciate your help.

The field name is Sold v Booked %

The formula is:

IF({# of Winners Booked},IF({Number of Winners/GCs Sold},{# of Winners Booked}/{Number of Winners/GCs Sold}))
 
The # of Winners Booked field is a Lookup field from another table. 
The Number of Winners/GCs Sold field is a Number field.
 
I wasn't sure if the above formula would work because the # of Winners Booked field had 1, 1 in it as it's looking up multiple records.  So I was thrilled when the Sold v Booked % field showed "100%" which was correct since it was 2 that sold and 2 that booked..
 
Then the field data changed to #ERROR! instead of 100% - and I don't know why.
I didn't change the type of field used in the formula. I didn't rename those fields.  
So I don't understand why it worked at first and then suddenly has #ERROR!.  
 
I've attached photos of the screens/formulas.  
 
If there is a better way to do this, I am open to it! As I said, I'm new to formulas and am still learning how to do things, so I don't yet know the best way to do things in formulas.
 
Thank you for your help!

 

Hey @RJeanR!

Lookups are ARRAYS, rather than single values or numbers. This above means that a formula applied to a lookup would look something like this: (1, 1)/2 for example.
Airtable cannot divide a list of numbers (1, 1) by two. It would be able to divide an actual number. 
To achieve that, I would suggest using a rollup rather than a lookup. On the formula for your rollup you could use MAX(), SUM(), AVG() or whatever suits your needs the best.

Let me know if you need any further help!
For more information on rollups fields, you can check this article.
For more information on lookup fields, you can check this other article.

 Mike, Consultant @ Automatic Nation

Hey @RJeanR!

Lookups are ARRAYS, rather than single values or numbers. This above means that a formula applied to a lookup would look something like this: (1, 1)/2 for example.
Airtable cannot divide a list of numbers (1, 1) by two. It would be able to divide an actual number. 
To achieve that, I would suggest using a rollup rather than a lookup. On the formula for your rollup you could use MAX(), SUM(), AVG() or whatever suits your needs the best.

Let me know if you need any further help!
For more information on rollups fields, you can check this article.
For more information on lookup fields, you can check this other article.

 Mike, Consultant @ Automatic Nation


Thank you, @Mike_AutomaticN, for your explanation and help. I'll give that a try and get back to you. I appreciate it!

@Mike_AutomaticN The Rollup with SUM worked. Thank you for your help and for the articles. 

Reply


Terms & Conditions