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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

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
461
Replies
3
Views
89
Replies
5
Views
253
Replies
6
Views
173
Replies
6
Views
127

1,129,786
Messages
5,638,307
Members
417,020
Latest member
MSVII

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.

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