Skip to main content
Solved

Average() of Integers Returning NaN

Sevanna_Shields
Forum|alt.badge.img+5

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.

Best answer by pcbowers

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.

View original
Did this topic help you find an answer to your question?

2 replies

P
Forum|alt.badge.img+9
  • pcbowers
  • Inspiring
  • 22 replies
  • Answer
  • May 13, 2021

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.

Sevanna_Shields
R
2 people like this
  • Sevanna_Shields
    Sevanna_Shields
  • R
    rockpaperdesign
Sevanna_Shields
Forum|alt.badge.img+5
pcbowers wrote:

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.


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!

P
1 person likes this
  • P
    pcbowers

Reply


Most helpful members this week

Terms & Conditions