How not to include a cell using the function AVERAGEIF?

[Fabs_98]

Hello guys, I was wondering if someone could help me. I have a range from H5:H12 from wich I need the average but I cannot include the cell H11. Because I'm in an Excel Course, the professor asked specifically for the function AVERAGEIF but many classmates and myself included, can't find the correct criteria to not include cell H11 in the formula.

 

[Fluff]

Hi & welcome to MrExcel.
Do you need to exclude certain values, or specific cells?

 

[Fabs_98]

Hi & welcome to MrExcel.
Do you need to exclude certain values, or specific cells?

Hi!
Just a one specific cell. Cell H11.

 

[Fluff]

As far as I'm aware the averageif cannot be used to exclude a particular cell. You can use it to exclude the value in a specific cell, but not just that cell.

 

[Fabs_98]

As far as I'm aware the averageif cannot be used to exclude a particular cell. You can use it to exclude the value in a specific cell, but not just that cell.

Hmmm interesting, well the value in the cell is $3,534.28, there are bigger and lower values on the same range. Is there a way I could use that same function with a criteria specified for that value?

 

[Fluff]

You can use

=AVERAGEIF(H5:H12,"<>"&H11)

but if the value in H11 occurs in other cells, then all of them will be ignored.

+Fluff 1.xlsm
	H	I	J
5	1	4.428571	4
6	2
7	3
8	4
9	7
10	6
11	7
12	8
Main

Cell Formulas
Range	Formula
I5	I5	=AVERAGE(H5:H10,H12)
J5	J5	=AVERAGEIF(H5:H12,"<>"&H11)

 

[Fabs_98]

You can use

=AVERAGEIF(H5:H12,"<>"&H11)

but if the value in H11 occurs in other cells, then all of them will be ignored.

+Fluff 1.xlsm
	H	I	J
5	1	4.428571	4
6	2
7	3
8	4
9	7
10	6
11	7
12	8
Main

Cell Formulas
Range	Formula
I5	I5	=AVERAGE(H5:H10,H12)
J5	J5	=AVERAGEIF(H5:H12,"<>"&H11)

YOU ARE THE ABSOLUTE BEST, THANK YOU SO MUCH, MY WHOLE CLASS APPRECIATES YOU.

 

[Fluff]

You're welcome & thanks for the feedback.