Forum Discussion
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 average, ir needs to be the total of non-zero cells divided by the count of non-zero cells. Have tried to search these forums for answers, but most seem to work only for a range of cells vs. a number of individual cells. Can anyone help me please? Thanks! BobW40746
=SUM(A10,B13,D6,K18,L12)/((A10<>0)+(B13<>0)+(D6<>0)+(K18<>0)+(L12<>0))
5 Replies
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.
=SUM(A10,B13,D6,K18,L12)/((A10<>0)+(B13<>0)+(D6<>0)+(K18<>0)+(L12<>0))
Thanks! I tried it and it works. Not quite as 'elegant' as hoped, but it does the job and that is what matters. I sincerely appreciate your reply!!
An alternative would be to use formulas in a contiguous range of cells
=A10
=B13
=D6
=K18
=L12
And then use the AVERAGEIF function on that range:
=AVERAGEIF(contiguous_range, "<>0")
