Averageif does not equal a name and greater than zero

[mfarr76]

Hello,

I have searched the threads and I haven't been able to find a solution to my problem.

I have several columns of names that has hours worked below it. I want to average the hours worked columns if they are greater than zero and they do not equal the name in the exclude list shown on the right hand side. I tried using the following and it didn't exclude the names and average everything.

Does anyone have any suggestions?

=AVERAGEIFS(B2:H2,NAMES,"<>=EXCLUDE_NAMES",K2:K4,">0")

[TABLE="width: 704"]
<tbody>[TR]
[TD="class: xl66, width: 64"]DAYS[/TD]
[TD="class: xl66, width: 64"]CHRIS[/TD]
[TD="class: xl66, width: 64"]ASHLEY[/TD]
[TD="class: xl66, width: 64"]WENDY[/TD]
[TD="class: xl66, width: 64"]BRENT[/TD]
[TD="class: xl66, width: 64"]RICHARD[/TD]
[TD="class: xl66, width: 64"]JACK[/TD]
[TD="class: xl66, width: 64"]SOPHIA[/TD]
[TD="class: xl66, width: 64"]ANDREA[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]EXCLUDE FROM AVERAGE[/TD]
[/TR]
[TR]
[TD="class: xl66"]1[/TD]
[TD="class: xl65"]29[/TD]
[TD="class: xl65"]25[/TD]
[TD="class: xl65"]12[/TD]
[TD="class: xl65"]19[/TD]
[TD="class: xl65"]37[/TD]
[TD="class: xl65"]22[/TD]
[TD="class: xl65"]20[/TD]
[TD="class: xl65"]13[/TD]
[TD][/TD]
[TD="class: xl67"]RICHARD[/TD]
[/TR]
[TR]
[TD="class: xl66"]2[/TD]
[TD="class: xl65"]25[/TD]
[TD="class: xl65"]22[/TD]
[TD="class: xl65"]25[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]27[/TD]
[TD="class: xl65"]30[/TD]
[TD="class: xl65"]36[/TD]
[TD="class: xl65"]17[/TD]
[TD][/TD]
[TD="class: xl67"]CHRIS[/TD]
[/TR]
[TR]
[TD="class: xl66"]3[/TD]
[TD="class: xl65"]21[/TD]
[TD="class: xl65"]30[/TD]
[TD="class: xl65"]16[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]38[/TD]
[TD="class: xl65"]24[/TD]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"]37[/TD]
[TD][/TD]
[TD="class: xl67"]JACK[/TD]
[/TR]
[TR]
[TD="class: xl66"]4[/TD]
[TD="class: xl65"]15[/TD]
[TD="class: xl65"]33[/TD]
[TD="class: xl65"]17[/TD]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]29[/TD]
[TD="class: xl65"]11[/TD]
[TD="class: xl65"]22[/TD]
[TD="class: xl65"]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance!

 

[Aladin Akyurek]

Ther is no K2:K4 in your exhibit. Care to elaborate?

 

[mfarr76]

Ther is no K2:K4 in your exhibit. Care to elaborate?

Sorry for the confusion. My html maker is corrupted and the download site is down.
The k2:k4 is where the names to exclude are located.

 

[Aladin Akyurek]

Sorry for the confusion. My html maker is corrupted and the download site is down.
The k2:k4 is where the names to exclude are located.

In J2 control+shift+enter, not just enter, and copy down:

=AVERAGE(IF(1-ISNUMBER(MATCH(NAMES,EXCLUDE_NAMES,0)),IF(ISNUMBER(B2:I2),IF(B2:I2>0,B2:I2))))

Is this what you had in mind?

 

[mfarr76]

Aladin,

That worked just as I wanted.

Thank you for taking the time to help!

 

[Aladin Akyurek]

Aladin,

That worked just as I wanted.

Thank you for taking the time to help!

You are welcome. Note that the formula excludes figures which are 0 or negative. If that is not the intent:

=AVERAGE(IF(1-ISNUMBER(MATCH(NAMES,EXCLUDE_NAMES,0)),IF(ISNUMBER(B2:I2),B2:I2)))