Formula Help
 

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=565797

Formula Help
 

This seemed to work okay for me:

=SUM(B8,B22,B25,B36)/INDEX(FREQUENCY((B8,B22,B25,B36),0),2)

"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=565797