Using a Lookup/Roll Up Field in a Formula Field

[Nicole_Scalama2]

Hello,

I am trying to perform a mathematical formula on a Roll Up Field. Specifically I have…

1. A bunch of records on Table A with FIELD ONE = (Float)
2. 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.
3. 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?

[Noel_Howell1]

You may have to add a second formula field on Table B with the value() function and then lookup that field on Table C.

[W_Vann_Hall]

Your scenario works fine for me. If you compare your base with this one, you should be able to determine what went wrong.

[Angie_Belle]

I am having the same problem. I have tried the value formula a second time, and still no dice. Just error messages.

[W_Vann_Hall]

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.

1. Cast the array to a string (text) and then use VALUE() to convert it to a number:

   VALUE({NumberIWant}&'')
   

2. Change the lookup field to a rollup field and use something like MAX(values) or SUM(values) as the aggregation function. (For that matter, I think you could just use values, 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.

[Angie_Belle]

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?

[W_Vann_Hall]

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.