# Averaging some cells -

#### MBaker25

##### New Member
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>

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Try

Code:
``{{=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.

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

If each 100 is in a cell of its own:

=AVERAGE(A1:A10)

should return 100, not 90.

Thanks Hoozits, it worked!

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.

Last edited:

Replies
2
Views
345
Replies
3
Views
375
Replies
4
Views
253
Replies
5
Views
690
Replies
0
Views
974

1,219,570
Messages
6,149,044
Members
450,853
Latest member
xtiinctt

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back