# cannot exclude a cell when using AVERAGEIF

#### surfing69

##### New Member
I have the following range where some cells are either blank or contain zero. However there is one cell that contains a number generated by a formula. However despite formatting that cell as 'text' , the AVERAGEIF function still sees it as a number.

For the range below, the cell containing the number 86.65% which is has been generated from another formula i.e. =C8/D4 is still seen by the AVERAGE OR AVERAGEIF formula even if i have formatted this cell as 'text'.

Book2
CD
4200
5
6
767.833.90%
8173.386.65%
900.00%
1000.00%
1100.00%
12120.5560.28%
13#DIV/0!37.30%
Sheet1
Cell Formulas
RangeFormula
D7D7=C7/D4
D8D8=C8/D4
D12D12=AVERAGEIF(D7:D11,"<>0")
D13D13=STDEV.S(IF(D7:D11>0,D7:D11))
Press CTRL+SHIFT+ENTER to enter array formulas.

The average for this range should be 1 if cells D8 to D11 had been excluded.

If formatting cell D8 as Text does not exclude the value in this cell, how can i adjust the AVERAGEIF formula to achieve this? However I still want to see the 'value' in cell D8 but dont want it as part of the calculation.

Thanks

#### surfing69

##### New Member
wow - long formula . I did try to fiddle with the AVERAGE Function but could find a way. The help pages on MS excel always seem to have adjoining cells i.e. A1:A6 and B1:B6 etc.. - not sure why every example had this format.

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### Joe4

You are welcome.
wow - long formula
That's nothing! Browse around here a bit, and you will find some crazy formulas that are multiple lines long!

Basically, we are doing our own average, which is the total values added up, divided by the number of entries.
So, for the Sum part, we just summed up the entire range, and subtracted the one cell that we wanted to exclude (adding 0s to the sum does not change anything).
Then, for the count part, we just counted all values that are greater than zero, and subtracted one if F8 was greater than 0.

#### surfing69

##### New Member
i was tempted to go down this route myself but I thought there must be an easier way which involved the AVERAGE function. Oh well - u learn something new every day !

#### Joe4

The AVERAGEIF function works just fine for contiguous ranges. It is when you have non-contiguous ranges, like in your example, that give it trouble.

Interestingly, if you name your range, and then use the named ranges in the formulas, AVERAGE will work with non-contiguous ranges, but it appears AVERAGEIF will not.
However, since you want to exclude zeroes, you cannot use AVERAGE.

Replies
7
Views
377
Replies
3
Views
83
Replies
5
Views
243
Replies
6
Views
129
Replies
6
Views
53

1,126,994
Messages
5,622,053
Members
415,875
Latest member
Tarali

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