Hello all
Im trying to create a formula to average some figures that are not next
to each other. I also do not want to include the zero amounts. The
formula in the help section of excel for Calculating the average of
numbers, ignoring zero (0) values is (=AVERAGE(IF(A2:A7<0,
A2:A7,""))) and it works great if the data is right next to each other.
However my layout for example I need the average of B8,B22,B25, and B36
to show up in say Q45. I tried to type the formula as
=AVERAGE(IF(B8,b22,b25,b36<0, B8,b22,b25,b36,"")) however it will not
function. I am using excel 2003 if that helps. Does any one have any
ideas any help will be greatly appreciated. Thanks in advance ~Jamie


--
pinkmeat
------------------------------------------------------------------------
pinkmeat's Profile: http://www.excelforum.com/member.php...o&userid=36865
View this thread: http://www.excelforum.com/showthread...hreadid=565796

Assuming that all the numbers are next to each other, even the ones with zero
value, try using this formula, replacing the range in the formula with the
range you need to average:

=SUM(A1:A10)/COUNTIF(A1:A10,"0")

"pinkmeat" wrote:


Hello all
Im trying to create a formula to average some figures that are not next
to each other. I also do not want to include the zero amounts. The
formula in the help section of excel for Calculating the average of
numbers, ignoring zero (0) values is (=AVERAGE(IF(A2:A7<0,
A2:A7,""))) and it works great if the data is right next to each other.
However my layout for example I need the average of B8,B22,B25, and B36
to show up in say Q45. I tried to type the formula as
=AVERAGE(IF(B8,b22,b25,b36<0, B8,b22,b25,b36,"")) however it will not
function. I am using excel 2003 if that helps. Does any one have any
ideas any help will be greatly appreciated. Thanks in advance ~Jamie


--
pinkmeat
------------------------------------------------------------------------
pinkmeat's Profile: http://www.excelforum.com/member.php...o&userid=36865
View this thread: http://www.excelforum.com/showthread...hreadid=565796