Thread: Average 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

=SUM(B8,B22,B25,B36)/COUNT(B8,B22,B25,B26)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"pinkmeat" <pinkmeat.2bmi45_1154035813.688@excelforum-nospam.com> wrote in
message news:pinkmeat.2bmi45_1154035813.688@excelforum-nospam.com...
>
> 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=565806
>

Hi!

Try this:

=SUM(B8,B22,B25,B36)/SUM((B8<>0),(B22<>0),(B25<>0),(B36<>0))

Biff

"pinkmeat" <pinkmeat.2bmi45_1154035813.688@excelforum-nospam.com> wrote in
message news:pinkmeat.2bmi45_1154035813.688@excelforum-nospam.com...
>
> 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=565806
>

Thank you guys you saved me from a nervous breakdown!