Skip to main content

Using a Lookup/Roll Up Field in a Formula Field

  • September 28, 2017
  • 6 replies
  • 63 views
N
Forum|alt.badge.img

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?

6 replies

N
  • Noel_Howell1
  • Participating Frequently
  • September 28, 2017

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
Forum|alt.badge.img+5
  • W_Vann_Hall
  • Inspiring
  • September 29, 2017

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

A
Forum|alt.badge.img+7
  • Angie_Belle
  • Known Participant
  • May 10, 2018

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

W
Forum|alt.badge.img+5
  • W_Vann_Hall
  • Inspiring
  • May 11, 2018

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


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.

J
O
C
A
Forum|alt.badge.img+7
  • Angie_Belle
  • Known Participant
  • May 27, 2018

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.


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
Forum|alt.badge.img+5
  • W_Vann_Hall
  • Inspiring
  • May 27, 2018

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?


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.

Terms & ConditionsAccessibility statement