AVERAGE - Disregard zero and numbers over 60?
 

I need to compute averages of several rows of numbers. Every cell contains
a number. None are negative.

I want the average to disregard '0' and any number over 60.

Thanks in advance.

AVERAGE - Disregard zero and numbers over 60?
 

Use an array** formula like this:

=AVERAGE(IF((A2:A6<0)*(A2:A6<60),A2:A6))

**Array formulas must be confirmed using Ctrl+Shift+Enter, not just Enter
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Ted" wrote:

I need to compute averages of several rows of numbers. Every cell contains
a number. None are negative.

I want the average to disregard '0' and any number over 60.

Thanks in advance.



.

AVERAGE - Disregard zero and numbers over 60?
 

"Ted" wrote:
I need to compute averages of several rows of numbers.
Every cell contains a number. None are negative.
I want the average to disregard '0' and any number over 60.

I presume by '0', you really mean the number zero, not the text "0".

Try this array formula[*]:

=average(if((0<A1:A100)*(A1:A100<=60),A1:A100))

The "*" functions as AND. You cannot use the AND function itself in this
context.
[*] An array formula is committed by pressing ctrl+shift+Enter instead of
just Enter. You should see curly braces around the entire formula in the
Formula Bar, e.g. {=formula}. You cannot enter the curly braces yourself;
Excel inserts them to denote an array formula. If you make a mistake,
select the cell, press F2, edit as needed, and finally press
ctrl+shift+Enter.

AVERAGE - Disregard zero and numbers over 60?
 

Perfect! Thank you both for your help!

"Joe User" <joeu2004 wrote in message
...
"Ted" wrote:
I need to compute averages of several rows of numbers.
Every cell contains a number. None are negative.
I want the average to disregard '0' and any number over 60.

I presume by '0', you really mean the number zero, not the text "0".

Try this array formula[*]:

=average(if((0<A1:A100)*(A1:A100<=60),A1:A100))

The "*" functions as AND. You cannot use the AND function itself in this
context.

[*] An array formula is committed by pressing ctrl+shift+Enter instead of
just Enter. You should see curly braces around the entire formula in the
Formula Bar, e.g. {=formula}. You cannot enter the curly braces yourself;
Excel inserts them to denote an array formula. If you make a mistake,
select the cell, press F2, edit as needed, and finally press
ctrl+shift+Enter.