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

D

dreamer

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

Any help is ppreciated!
 
T

T. Valko

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
 
D

dreamer

Thank you so much! Valko.
--
happy world


T. Valko said:
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
Click to expand...
 
Y

yowzers

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

T. Valko

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

averaging multiple items from one list 2
AverageIf function with multiple columns 1
Averaging a colume of values. 1
countifs Function not giving desired result with date criteria 2
Need help with average/lookup formula 2
Average Unique 7
Need help with a formula 3
need quick help averaging multiple rows 3

Top