how to use a function to average all the numbers except 8 in a col
 

how to use a function to average all the numbers except "8" in a column?
Filter out and average?

Any help is ppreciated!

--
happy world

how to use a function to average all the numbers except 8 in a col
 

Try one of these:

=SUMIF(A1:A10,"<8")/(COUNT(A1:A10)-COUNTIF(A1:A10,8))

Or, this array** formula:

=AVERAGE(IF(A1:A10<8,A1:A10))

**entered with the key combination of CTRL,SHIFT,ENTER (not just ENTER).

Biff

"dreamer" wrote in message
...
how to use a function to average all the numbers except "8" in a column?
Filter out and average?

Any help is ppreciated!

--
happy world

how to use a function to average all the numbers except 8 in a
 

Thank you so much! Valko.
--
happy world


"T. Valko" wrote:

Try one of these:

=SUMIF(A1:A10,"<8")/(COUNT(A1:A10)-COUNTIF(A1:A10,8))

Or, this array** formula:

=AVERAGE(IF(A1:A10<8,A1:A10))

**entered with the key combination of CTRL,SHIFT,ENTER (not just ENTER).

Biff

"dreamer" wrote in message
...
how to use a function to average all the numbers except "8" in a column?
Filter out and average?

Any help is ppreciated!

--
happy world

how to use a function to average all the numbers except 8 in a
 

You're welcome. Thanks for the feedback!

Biff

"dreamer" wrote in message
...
Thank you so much! Valko.
--
happy world


"T. Valko" wrote:

Try one of these:

=SUMIF(A1:A10,"<8")/(COUNT(A1:A10)-COUNTIF(A1:A10,8))

Or, this array** formula:

=AVERAGE(IF(A1:A10<8,A1:A10))

**entered with the key combination of CTRL,SHIFT,ENTER (not just ENTER).

Biff

"dreamer" wrote in message
...
how to use a function to average all the numbers except "8" in a
column?
Filter out and average?

Any help is ppreciated!

--
happy world

how to use a function to average all the numbers except 8 in a
 

How do I do this if the exception is not a number. For example, I want to
average all of column A if column B equals cell D1, except when the word "NO"
is in column B.

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

Biff

"dreamer" wrote in message
...
Thank you so much! Valko.
--
happy world


"T. Valko" wrote:

Try one of these:

=SUMIF(A1:A10,"<8")/(COUNT(A1:A10)-COUNTIF(A1:A10,8))

Or, this array** formula:

=AVERAGE(IF(A1:A10<8,A1:A10))

**entered with the key combination of CTRL,SHIFT,ENTER (not just ENTER).

Biff

"dreamer" wrote in message
...
how to use a function to average all the numbers except "8" in a
column?
Filter out and average?

Any help is ppreciated!

--
happy world

how to use a function to average all the numbers except 8 in a
 

Not sure I follow you on this.

Let's assume D1 = Yes.

Say you want to average col A where col B = Yes. Any NO entries will
automatically be ignored.

Unless you mean to average col A where col B = D1 *or* NO.

Need some clarification.

--
Biff
Microsoft Excel MVP


"yowzers" wrote in message
...
How do I do this if the exception is not a number. For example, I want to
average all of column A if column B equals cell D1, except when the word
"NO"
is in column B.

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

Biff

"dreamer" wrote in message
...
Thank you so much! Valko.
--
happy world


"T. Valko" wrote:

Try one of these:

=SUMIF(A1:A10,"<8")/(COUNT(A1:A10)-COUNTIF(A1:A10,8))

Or, this array** formula:

=AVERAGE(IF(A1:A10<8,A1:A10))

**entered with the key combination of CTRL,SHIFT,ENTER (not just
ENTER).

Biff

"dreamer" wrote in message
...
how to use a function to average all the numbers except "8" in a
column?
Filter out and average?

Any help is ppreciated!

--
happy world