EXCEL IF Function problem

  • Thread starter Thread starter andyp161
  • Start date Start date
A

andyp161

Hi there,

I want to calculate an average of H3:H80. However, I ONLY want th
average to be calculated on those cells that contain values greate
than 0. My attempt, as follows, returns #VALUE
:=IF(H3:H80>0,AVERAGE(H3:H80),""). Any ideas?

Many thank
 
andyp 161, here is one way =SUMPRODUCT((H3:H80)/COUNTIF(H3:H80,"<>0"))

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
 
Hi
and another option: Try the array formula (entered with
CTRL+SHIFT+ENTER):
=AVERAGE(IF(H3:H80>0,H3:H80))
 
Hi,


=SUMIF(H3:H80,">0")/COUNTIF(H3:H80,">0")


--
Regards,
Soo Cheon Jheong
_ _
^¢¯^
--
 
Back
Top