Averaging some cells -

[MBaker25]

I have a column that contains ten cells that will have a score of 100, 0 or the words “No Impact” in them. I need excel to average this column but exclude the cells that contain the words “No Impact”. So basically, if I take 10 tests and score 100 on all of them but 1, how do I get excel to average 100 by forgiving one of those test that I would be labeled “No Impact”)?</SPAN>

100 – test one</SPAN>
100 – test two</SPAN>
100 – test three</SPAN>
100 – test four</SPAN>
100 – test five</SPAN>
100 – test six </SPAN>
No Impact – test seven</SPAN>
100 – test eight</SPAN>
100 – test nine </SPAN>
100 – test ten </SPAN>

Average should return 100% not 90%</SPAN></SPAN>

 

[Hoozits]

Try

{{=sum(if(A1:A10<>"No Impact",A1:A10))/count(if(A1:A10<>"No Impact",A1:A10))}

Note that it's an array formula, so you need to hit ctrl-shift-enter while your curor is inside the cell with the formula.

 

[azumi]

=AVERAGEIF(A1:A10,"<>"&"No Impact")

 

[Aladin Akyurek]

If each 100 is in a cell of its own:

=AVERAGE(A1:A10)

should return 100, not 90.

 

[MBaker25]

Thanks Hoozits, it worked!

 

[Aladin Akyurek]

Thanks Hoozits, it worked!

AVERAGE ignore empty cells and cells with text. Therefore:

=AVERAGE(A1:A10)

will work and return in this case 100, not. 90. Thus, no array formula is required.