Average and addtional conditions

[jnix612]

I need some guidance. I am unable to determine if I need a formula for what I will describe below and which formula:

Give me the average of these 4 rows, unless the average is 0 (zero), if the average is 0 (zero) enter this text (“this text” is an adjacent row).

When I create a PivotTable, and if 0 is the result I will get a lot of questions about what does 0 indicate. I have 7 reasons why scores would be 0, and this type of data would be useful for analytics so I want it spelled out clearly.

I have attached sample in Excel.

[Bob Phillips]

Just use

=IF(AVERAGE(B1:B4)=0,text_cell,AVERAGE(B1:B4))

[Paul_Hossler]

The cells being averaged seem to come from an external file with a 180 char name, not data in the sheet:

=AVERAGE('\\cdc.gov\project\CGH_GID_Active\OD\STOP\STOP Master Data Lists\Country Assignment Lists 1-51 MASTER_pw protected.xlsx'!CountryAssignment[@[Technical_Rating]:[Commitment_Rating]])

=IF(AVERAGE(B1:B4)=0,text_cell,AVERAGE(B1:B4))

would make the formula about 400 char long, and isn't there a 253 char formula limit?

[jnix612]

@XLD, this worked. Thank you. I really thought it would be more complicated.

[jnix612]

@Paul thank you. I forgot my spreadsheet is formula heavy. I was able to use the basic version of what you typed.