
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
āSep 28, 2017 12:27 PM
Hello,
I am trying to perform a mathematical formula on a Roll Up Field. Specifically I haveā¦
- A bunch of records on Table A with FIELD ONE = (Float)
- On Table B I take an Average of the value of FIELD ONE s on Table A which I am doing using a Roll Up Field. This field with be named FIELD TWO.
- On Table C I would like to lookup the FIELD TWO from Table B and then multiply it by a factor of 1.2. Whenever I try to do this, every cell prints #ERROR!.
I have tried using a Lookup Field and various types of Average/ Array Field Formats.
I have also tried to use the āvalue()ā function in my formula on Table C however it is not working.
Does anyone know how to make this work?
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
āSep 28, 2017 12:56 PM
You may have to add a second formula field on Table B with the value() function and then lookup that field on Table C.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
āSep 28, 2017 06:58 PM
Your scenario works fine for me. If you compare your base with this one, you should be able to determine what went wrong.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
āMay 10, 2018 01:54 PM
I am having the same problem. I have tried the value formula a second time, and still no dice. Just error messages.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
āMay 11, 2018 06:14 AM
You are probably running into conflict with the way Airtable handles lookups: All lookups (AFAIK) are returned as arrays ā even those with only a single item, and even though Airtable currently doesnāt provide any array functions.
Assuming the lookup field you wish to reference in a formula is supposed to be a numeric value, there are typically two ways around this problem.
-
Cast the array to a string (text) and then use
VALUE()
to convert it to a number:VALUE({NumberIWant}&'')
-
Change the lookup field to a rollup field and use something like
MAX(values)
orSUM(values)
as the aggregation function. (For that matter, I think you could just usevalues
, but since I donāt know exactly how your base is breaking, I canāt test and verify possible solutions.)
Either of these approaches should work.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
āMay 26, 2018 05:29 PM
Hi W Vann Hall! Thanks for your replies on a few questions Iāve had. Sorry for not acknowledging them yetāIāve got 3 little ones, so my concentration times are limited! Anyway, Iām trying to process your suggestion and struggling to understand, as Iāve tried using Value() with continued error messages. :frowning: Iām attaching a screenshot below, in which I attempted to have the name field reference a currency field and a unit type field. Ideally it would say, āTP: $0.45/double rolls.ā When I tried to adjust the number by starting with a VALUE function, it gave me error messages. Any ideas how to overcome this and limit it to two places?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
āMay 27, 2018 07:24 AM
Just what you need! More long posts to read!
To answer your last question first, it turns out formatting currency fields converted to strings can be a surprisingly complex choreā¦
To answer your first question, I dunno: Could you PM me a link to your base so I can see what might be causing your errors. To do so, select āShareā > āCreate a shared link to the whole baseā > āPrivate read-only linkā, and message me the generated URL.
In the meantime, often a NaN
ā ānot a numberā ā result indicates one is trying to solve a formula in which one or more of the expressions (frequently the divisor) is null. Check to make sure all the components of your price-generating formula exist; that is, if {Price}
is defined as {Cost}/{Quantity}
and is returning NaN
, make sure both {Cost}
and {Quantity}
have numeric values assigned to them.
