# Average() of Integers Returning NaN

Topic Labels: Formulas
Solved
1021 2
cancel
Showing results for
Did you mean:  6 - Interface Innovator

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  6 - Interface Innovator

What you are looking for is something like this for your `Wave` table: When the `People` table looks like this: To implement this, I use a rollup field instead of a lookup field with the following options: 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.

2 Replies 2  6 - Interface Innovator

What you are looking for is something like this for your `Wave` table: When the `People` table looks like this: To implement this, I use a rollup field instead of a lookup field with the following options: 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.  6 - Interface Innovator

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! 