Forum Discussion
Solved
AVERAGE 10 Non-Contiguous Cells BUT IGNORE any cell with a Value=0
Have a worksheet that needs to AVERAGE a bunch of non-contiguous cells (For example: A10, B13, D6, K18, L12) where some of the cell will have $X.xx values and some will be 0.00. To get a true averag...
=SUM(A10,B13,D6,K18,L12)/((A10<>0)+(B13<>0)+(D6<>0)+(K18<>0)+(L12<>0))
Assuming DataRange is a named range that encompasses your values
= AVERAGE(IF(dataRange<>0, dataRange))
= AVERAGEIFS(dataRange, dataRange, "<>0")will each ignore 0s and blanks.
Thank you, but it does not. they are individual non-contiguous cells, and i am not sophisticated enough to know how to use the 'range' and 'name' functions. appreciate the attempt.
