Help

Average() of Integers Returning NaN

Topic Labels: Formulas
Solved
Jump to Solution
558 2
cancel
Showing results for 
Search instead for 
Did you mean: 

Hi! I need assistance if possible!

I have a table where I’m tracking information about a wave of people (table 1).

I have another table where I have the names and individual information for those people. (table 2)

I have a linked field connecting the names from table 2, to the wave on table 1.

Part of the information on table 2 is a formula that rolls up from another table with possibilities of 0-51.

I have a look up field bringing the numbers for each person into table 1 separated by commas and I can use Average() to get an average.

BUT if there is no one linked, there are no numbers to average and it returns NaN. I would like it to either return 0 or stay blank.

I have read previous articles but they mostly seem to focus on dates and I am using integers.

1 Solution

Accepted Solutions
pcbowers
6 - Interface Innovator
6 - Interface Innovator

What you are looking for is something like this for your Wave table:

image

When the People table looks like this:

image

To implement this, I use a rollup field instead of a lookup field with the following options:

image

The Aggregation formula is as follows: IF(OR(AVERAGE(values), AVERAGE(values)=0), AVERAGE(values))

By wrapping it in an IF statement, you can run various checks. This formula checks if AVERAGE(values) is truthy or it is equal to 0. If you did not check if it equaled 0, the cell would be blank instead of 0 when the average was exactly 0. By not specifying something else to return if false, it will simply return blank.

See Solution in Thread

2 Replies 2
pcbowers
6 - Interface Innovator
6 - Interface Innovator

What you are looking for is something like this for your Wave table:

image

When the People table looks like this:

image

To implement this, I use a rollup field instead of a lookup field with the following options:

image

The Aggregation formula is as follows: IF(OR(AVERAGE(values), AVERAGE(values)=0), AVERAGE(values))

By wrapping it in an IF statement, you can run various checks. This formula checks if AVERAGE(values) is truthy or it is equal to 0. If you did not check if it equaled 0, the cell would be blank instead of 0 when the average was exactly 0. By not specifying something else to return if false, it will simply return blank.

It worked, thank you so much!!

I didn’t use a rollup field, just because the number is not ALL that is in that table, and it never works right. BUT I was able to switch out where you put (Values) and put the field reference for the lookup field and it works perfectly! Thank you so much!

Labels