Average if

[Vlookup help]

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

 

[Frank Kabel]

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

 

[Vlookup help]

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

 

[Peo Sjoblom]

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]

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

 

[Dave Peterson]

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,"")