Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

Formula worked but is now only showing ERROR in the field (not an error for the formula itself)

Topic Labels: Formulas
Solved
Jump to Solution
176 3
cancel
Showing results for 
Search instead for 
Did you mean: 
RJeanR
4 - Data Explorer
4 - Data Explorer

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!

 

1 Solution

Accepted Solutions
Mike_AutomaticN
10 - Mercury
10 - Mercury

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

See Solution in Thread

3 Replies 3
Mike_AutomaticN
10 - Mercury
10 - Mercury

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!

RJeanR
4 - Data Explorer
4 - Data Explorer

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