cannot exclude a cell when using AVERAGEIF

[surfing69]

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
	C	D
4		200
5
6
7	67.8	33.90%
8	173.3	86.65%
9	0	0.00%
10	0	0.00%
11	0	0.00%
12	120.55	60.28%
13	#DIV/0!	37.30%
Sheet1

Cell Formulas
Range	Formula
D7	D7	=C7/D4
D8	D8	=C8/D4
D12	D12	=AVERAGEIF(D7:D11,"<>0")
D13	D13	=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]

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.

Thanks for your input

 

[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]

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.