Problem with ARRAY FORMULA MIN

[Armelle Aaserød]

Hi,

I am using the following array formula to try and determine the minimum value of a range of values verifying a certain criteria :
{=MIN(('Détail OF'!$F$2:$F$2040=Overview!$A3&Overview!$B3)*('Détail OF'!$J$2:$J$2040))}

This however only returns 0 as results while I have no 0 values in the range J2:J2040????

Using the exact same formula to determine the maximum value of the range works fine so I don't understand why it's going wrong? :

={MAX(('Détail OF'!$F$2:$F$2040=Overview!$A3&Overview!$B3)*('Détail OF'!$J$2:$J$2040))} does return maximum values

Your help is very much appreciated !!!

Best Regards,



Armelle Aaserød

 

[Bob Phillips]

Add an extra test

=MAX(('Détail OF'!$F$2:$F$2040=Overview!$A3&Overview!$B3)*('Détail
OF'!$J$2:$J$2040))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Armelle Aaserød" <aaaserodatdanfoss.com> wrote in message
Hi,

I am using the following array formula to try and determine the minimum
value of a range of values verifying a certain criteria :
{=MIN(('Détail OF'!$F$2:$F$2040=Overview!$A3&Overview!$B3)*('Détail
OF'!$J$2:$J$2040))}

This however only returns 0 as results while I have no 0 values in the range
J2:J2040????

Using the exact same formula to determine the maximum value of the range
works fine so I don't understand why it's going wrong? :

={MAX(('Détail OF'!$F$2:$F$2040=Overview!$A3&Overview!$B3)*('Détail
OF'!$J$2:$J$2040))} does return maximum values

Your help is very much appreciated !!!

Best Regards,



Armelle Aaserød

 

[Armelle Aaserød]

The formula you've given is the exact same one I had ?? {=MIN(('Détail
OF'!$F$2:$F$2040=Overview!$A3&Overview!$B3)*('Détail

OF'!$J$2:$J$2040))}

I am not sure what extra test I can add since there are no 0 values in the
range 'Détail OF'!$J$2:$J$2040 why does it return 0 as Minimum Value??

Thanks in advance,

Armelle Aaserød

 

[Bob Phillips]

Sorry Armelle, I copied the wrong formula out of my worksheet.

What I really meant was

=MIN(IF((('Détail OF'!$F$2:$F$2040=Overview!$A3&Overview!$B3)*('Détail
OF'!$J$2:$J$2040))>0,'Détail OF'!$J$2:$J$2040))

which is still an array formula

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Armelle Aaserød]

Thanks for your help !

Armelle

Sorry Armelle, I copied the wrong formula out of my worksheet.

What I really meant was

=MIN(IF((('Détail OF'!$F$2:$F$2040=Overview!$A3&Overview!$B3)*('Détail
OF'!$J$2:$J$2040))>0,'Détail OF'!$J$2:$J$2040))

which is still an array formula

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)