Skip to main content
Solved

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

T

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!

Best answer by Justin_Barrett

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.

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

2 replies

Justin_Barrett
Forum|alt.badge.img+5

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.

A
B
2 people like this
  • A
    AlliAlosa
  • B
    BigHowdy
B
  • BigHowdy
  • New Participant
  • 1 reply
  • November 21, 2024
Justin_Barrett wrote:

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.

Reply


Most helpful members this week

Terms & Conditions