Average

[kcdill]

I am trying to average the values in B38:I38 in eight adjacent columns. The eight cells are linked to eight different spreadsheets. I am using the formula =SUM(SUMIF(B38:I38,{"<0",">0"}))/COUNT(B38:I38). This formula works ok if all eight cells have a value. But when the cell only contains the link formula and no number value the formula wants to divide by eight even if only two cells contain number values. This formula seemed to work in a different spreadsheet but doesn't want to work today. Thanks for your help.

 

[Datsmart]

I assume you are trying to Average without counting zeros.
If so, try this formula:

=SUM(B38:I38)/COUNTIF(B38:I38,">0")

 

[kcdill]

Good Morning DatSmart:
Your solution works great. One other question. When all eight of the cells are empty the formula returns #DIV/0!. What I would like to see is an empty cell. I tried =IF(I13<>"",SUM(B38:I38)/COUNTIF(B38:I38,">0"),"") but that had no effect. What can I add to your formula to return a blank cell? Thanks again for your help.

 

[Diffy]

do this:

=if(iserror(SUM(B38:I38)/COUNTIF(B38:I38,">0")),"",SUM(B38:I38)/COUNTIF(B38:I38,">0"))

 

[Datsmart]

Try this:

=IF(COUNT(B38:I38)=0,"",SUM(B38:I38)/COUNTIF(B38:I38,">0"))