Last week we asked…“How can you get Excel to return an average that doesn't consider 0 values?" The responses were all sound and valuable. You guys really know Excel! Most of you shared the most common solution, a combination of the SUM() and COUNTIF() functions, in the following form: =
SUM(range)/COUNTIF(range,"<>0")Joshua.Masson@... Offered an interesting array solution:
=AVERAGE(IF(range<>0,range,""))Gordon.cooper@... pointed out that the array accounts for blank cells, whereas the more traditional SUM() expression doesn't. He's right, but I agree with gbentley@... in this particular case. Neither expression is superior, but it is important that you know how both expressions respond to blanks and text (any value that doesn't specifically match the COUNTIF() function's criteria). A few of you mentioned Excel 2007's new AVERAGEIF() function. Interestingly, if you exclude 0 values, this function also ignores blank cells — curious. Thanks for playing! It's interesting to see so many different solutions to the same question.
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.