Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

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

Topic Labels: Formulas
Solved
Jump to Solution
1777 1
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

1 Reply 1
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.