JimS said:
How do I average the numbers in column A if the
numbers in column B are greater than 0? There
is some text in column B.
You could use the following array formula[*]:
=AVERAGE(IF(B1:B6>0,A1:A6))
If you have Excel 2007 or later, you can use:
=AVERAGEIF(B1:B6,">0",A1:A6)
A note about the text in column B. B1:B6>0 will return TRUE for any text in
B1:B6. However, AVERAGE ignores text. Consequently, we do not need to test
ISNUMBER(B1:B6), which simplifies the AVERAGE(IF(...)) form and allows us to
use the AVERAGEIF(...) form.
-----
[*] Enter an array formula by pressing ctrl+shift+Enter instead of just
Enter. In the Formula Bar, you will see curly braces around the entire
formula, viz. {=formula}. You cannot type the curly braces yourself; Excel
displays them to distinguish an array formula. If you make a mistake,
select the cell, press F2, edit as needed, then press ctrl+shift+Enter.
----- original message -----
JimS said:
1 -20
1.6 -20
2.4 pass
3 35
1.5 14
2.7 7
How do I average the numbers in column A if the numbers in column B
are greater than 0? There is some text in column B.
