Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Average of rollup columns = NaN and want to have blank cell if so

Topic Labels: Formulas
Solved
Jump to Solution
2067 2
cancel
Showing results for 
Search instead for 
Did you mean: 
threehex
4 - Data Explorer
4 - Data Explorer

I’m averaging 3 columns, all rollups. Note that I have this formula in them, so certain cells are blank:

IF(ISERROR(AVERAGE(values))=0,AVERAGE(values),BLANK())

The 4th column, where I want to average THOSE columns, has NaN if all 3 columns are blank. Which makes sense - I just can’t figure out how to get that 4th column to BE blank if the result is NaN.

The column currently has this formula:

AVERAGE({Value 1},{Value 2},{Value 3})

I tried to essentially mimic what I’m doing in the other columns but nothing I’m trying seems to be valid.

Thanks!

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

This formula should work:

IF(OR({Value 1},{Value 2},{Value 3}), AVERAGE({Value 1},{Value 2},{Value 3}))

It will output the average only when there’s a value in at least one of the three fields. If they’re all empty, there will be no output.

See Solution in Thread

2 Replies 2
Justin_Barrett
18 - Pluto
18 - Pluto

This formula should work:

IF(OR({Value 1},{Value 2},{Value 3}), AVERAGE({Value 1},{Value 2},{Value 3}))

It will output the average only when there’s a value in at least one of the three fields. If they’re all empty, there will be no output.

Thank you! I couldn't figure this one out for anything.