Not

[tlindeman]

I know not is the wrong function for this but I need something where I averageif where it does not equal 6.1 or 5.2, so this is my "fake" formula

=averageif($D3:$D$501, not(5.2,6.1),$I$3:$I$501)

Thank You

 

[Aladin Akyurek]

I know not is the wrong function for this but I need something where I averageif where it does not equal 6.1 or 5.2, so this is my "fake" formula

=averageif($D3:$D$501, not(5.2,6.1),$I$3:$I$501)

Thank You

For which range 5.2 and 6.1 must not hold - D-range or I-range?

 

[tlindeman]

D range,
Thank You

 

[T. Valko]

I know not is the wrong function for this but I need something where I averageif where it does not equal 6.1 or 5.2, so this is my "fake" formula

=averageif($D3:$D$501, not(5.2,6.1),$I$3:$I$501)

Thank You

Try this array formula**:

Use cells to hold the criteria:

• A3 = 5.2
• A4 = 6.1

Array entered**:

=AVERAGE(IF(ISNA(MATCH(D3:D501,A3:A4,0)),I3:I501))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Aladin Akyurek]

D range,
Thank You

Control+shift+enter, not just enter:

=AVERAGE(IF(1-ISNUMBER(MATCH($D3:$D$501,{5.2,6.1},0)),$I$3:$I$501))

This formula hard-codes the criteria to exclude.

However, you can also have a range housing the critera to exclude and name this range ExcludeList. This set up allows you to invoke...

=AVERAGE(IF(1-ISNUMBER(MATCH($D3:$D$501,ExcludeList,0)),$I$3:$I$501))

Note. If D-range house formulas, you might need to invoke something like...

=AVERAGE(IF(1-ISNUMBER(MATCH(ROUND($D3:$D$501,1),ExcludeList,0)),$I$3:$I$501))

The latter two formulas also require control+shift+enter.

 

[tlindeman]

Thank you both, works perfect

 

[T. Valko]

1-ISNUMBER(MATCH(

ISNA(MATCH(

• Does EXACTLY the same thing
• Is a few keystrokes shorter
• And is more efficient

 

[T. Valko]

Thank you both, works perfect

You're welcome. Thanks for the feedback!

 

[Aladin Akyurek]

Thank you both, works perfect

You are welcome. I appreciate the feedback.

 

[T. Valko]

You are welcome. I appreciate the feedback.

You did EXACTLY what I knew you would do and when you would do it!