max and min if while excluding date range, array formula

[twinwings]

Hello,

I would like to calculate the max and min of a row of numbers, while excluding those numbers that correspond to a date range

Row 3, or range C3:JO3 is the row with ALL the dates
tab excludelist, range $AF$84:$AF$95 is the range of dates that I want to exclude from the max/min calculation
C186:JO186 is the row that the calculation is based upon.

I thought I had this, but the more I google, the more confused im getting.

I don't understand what I am missing in the below?

=MIN(IF(C3:JO3<>'excludelist'!$AF$84:$AF$95,C186:JO186)

 

[Weazel]

maybe something like....


=MIN(IF(ISNA(C3:JO3<>excludelist!$AF$84:$AF$95),C186:JO186)) control shift enter

 

[Canapone]

Hi,

array entered (control+shift+enter)

=MIN(IF(COUNTIF('Excludelist'!$AF$84:$AF$95,C3:JO3)=0,C186:JO186)

If you'd be an Excel 2010 (or newer versions) user:

=AGGREGATE(15,6,C186:JO186/(COUNTIF('Excludelist'!$AF$84:$AF$95,C3:JO3)=0),1)

Hope it helps