PDA

View Full Version : [SOLVED] Average and addtional conditions



jnix612
04-25-2018, 06:09 AM
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
04-25-2018, 08:56 AM
Just use


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

Paul_Hossler
04-25-2018, 10:19 AM
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
04-25-2018, 11:12 AM
@XLD, this worked. Thank you. I really thought it would be more complicated.

jnix612
04-25-2018, 11:24 AM
@Paul thank you. I forgot my spreadsheet is formula heavy. I was able to use the basic version of what you typed.