PROBLEMS WITH AVERAGEIF FUNCTION IN EXCEL 2003

Bill Golden
Bill Golden used Ask the Experts™
on
Earlier I posted a problem of having 10 cells with numeric values and needed an average given that not all cells may have values.

If received a solution that goes like this...

="The average of the "& COUNTIF(A1:A10,"> 0") & " years is $ " & AVERAGEIF(A1:A10,">0")

I was unable to get it to work and a reply brought no response.

In testing, I brought the formula down to its simplest use...

=AVERAGEIF(A1:A10,">0")

But all I get is a #NAME? response.  

Does AVERAGEIF work in Excel 2003?  If not, is there a workaround?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try this array formula (after you copy the formula instead of hitting Enter you will use ctrl+shift+enter:
=AVERAGE(IF(A1:A10>0,A1:A10))

Open in new window

Top Expert 2016

Commented:
Hi,

if you don't want an array formula, pls try
=SUMIF(A1:A10;">0")/COUNTIF(A1:A10;">0")

Open in new window

Regards
Bill GoldenExecutive Managing Member

Author

Commented:
Thank you.  I had come to a total halt.  Please read next question I will be posting shortly.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial