Average if
 

Hi,

Please let me know if there is a formula that allows me to do the following:

A1 A2
AVG: 5
0 5
3
0 2
7
5
0 1

I just want the average to reflect the average of the numbers in column A2
where column A1 does not have a zero.

Thanks

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=AVERAGE(IF(A1:A100<0,B1:B100))

--
Regards
Frank Kabel
Frankfurt, Germany

"Vlookup help" schrieb im
Newsbeitrag ...
Hi,

Please let me know if there is a formula that allows me to do the
following:

A1 A2
AVG: 5
0 5
3
0 2
7
5
0 1

I just want the average to reflect the average of the numbers in
column A2
where column A1 does not have a zero.

Thanks

Unfortunately, this is not working for me... when I follow your dirrections
I receive the following error:
#DIV/0!

I am trying to average all of the contents in Column B where there is NOT a
0 in Column A.

Thanks for your help,

Jon

"Frank Kabel" wrote:

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=AVERAGE(IF(A1:A100<0,B1:B100))

--
Regards
Frank Kabel
Frankfurt, Germany

"Vlookup help" schrieb im
Newsbeitrag ...
Hi,

Please let me know if there is a formula that allows me to do the
following:

A1 A2
AVG: 5
0 5
3
0 2
7
5
0 1

I just want the average to reflect the average of the numbers in
column A2
where column A1 does not have a zero.

Thanks

Works for me, unless all your values in A are either blank or zero
you should not get that answer

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"Vlookup help" wrote in message
...
Unfortunately, this is not working for me... when I follow your
dirrections
I receive the following error:
#DIV/0!

I am trying to average all of the contents in Column B where there is NOT
a
0 in Column A.

Thanks for your help,

Jon

"Frank Kabel" wrote:

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=AVERAGE(IF(A1:A100<0,B1:B100))

--
Regards
Frank Kabel
Frankfurt, Germany

"Vlookup help" schrieb im
Newsbeitrag ...
Hi,

Please let me know if there is a formula that allows me to do the
following:

A1 A2
AVG: 5
0 5
3
0 2
7
5
0 1

I just want the average to reflect the average of the numbers in
column A2
where column A1 does not have a zero.

Thanks

Based on Peo's comment, I changed all of the blanck cells (which were
actually "") in Column A to say "1", but I continue to get the same error
(#DIV/0!). Could this be because I have a formula in Column A that generates
a 0 or 1 rather than an actual 0 or 1. Please help.

Jon

"Peo Sjoblom" wrote:

Works for me, unless all your values in A are either blank or zero
you should not get that answer

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"Vlookup help" wrote in message
...
Unfortunately, this is not working for me... when I follow your
dirrections
I receive the following error:
#DIV/0!

I am trying to average all of the contents in Column B where there is NOT
a
0 in Column A.

Thanks for your help,

Jon

"Frank Kabel" wrote:

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=AVERAGE(IF(A1:A100<0,B1:B100))

--
Regards
Frank Kabel
Frankfurt, Germany

"Vlookup help" schrieb im
Newsbeitrag ...
Hi,

Please let me know if there is a formula that allows me to do the
following:

A1 A2
AVG: 5
0 5
3
0 2
7
5
0 1

I just want the average to reflect the average of the numbers in
column A2
where column A1 does not have a zero.

Thanks

Does your formula return a string or a number?

like:
=if(a1="ok","1","")

How about if you really returned a number

=if(a1="ok",1,"")





Vlookup help wrote:

Based on Peo's comment, I changed all of the blanck cells (which were
actually "") in Column A to say "1", but I continue to get the same error
(#DIV/0!). Could this be because I have a formula in Column A that generates
a 0 or 1 rather than an actual 0 or 1. Please help.

Jon

"Peo Sjoblom" wrote:

Works for me, unless all your values in A are either blank or zero
you should not get that answer

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"Vlookup help" wrote in message
...
Unfortunately, this is not working for me... when I follow your
dirrections
I receive the following error:
#DIV/0!

I am trying to average all of the contents in Column B where there is NOT
a
0 in Column A.

Thanks for your help,

Jon

"Frank Kabel" wrote:

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=AVERAGE(IF(A1:A100<0,B1:B100))

--
Regards
Frank Kabel
Frankfurt, Germany

"Vlookup help" schrieb im
Newsbeitrag ...
Hi,

Please let me know if there is a formula that allows me to do the
following:

A1 A2
AVG: 5
0 5
3
0 2
7
5
0 1

I just want the average to reflect the average of the numbers in
column A2
where column A1 does not have a zero.

Thanks






--

Dave Peterson