Thread: average formula ingoring empty cells
View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
duane duane is offline
external usenet poster
  
Posts: 1
Default average formula ingoring empty cells

the average formula you posted will ignore blanks, but not zero values.
here is an alternative formula, which will average the cells you
requested, including only the non zero values. J is column 10, M is
column 13, etc.

=SUMPRODUCT((MOD(COLUMN(G3:S3)-1,3)=0)*(G3:S3<0)*(G3:S3))/SUMPRODUCT((MOD(COLUMN(G3:S3)-1,3)=0)*(G3:S3<0))


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=560539

Reply With QuoteReply With Quote