Excel - Array or formula for min w/ multiple criteria

[dhamDIT]

Hi, I am having problems getting a minimum based on multiple criteria. I was successful getting a maximum using the following array, (I wanted the max based on day of week = Mon.)
{=MAX(IF($C$1:$AF$1="Mon",$C15:$AF15))}


My problem with the Min, using the same approach is that is does not ignore zero's. I was successful using a formula to get a minimum on the row ignoring zeroes
=SMALL(C15:AF15,COUNTIF($C$15:$A$15,0)+1)

however I am having problems fitting in the criteria to select only columns where $C$1:$AF$1="Mon and also to have it display a blank or a zero instead of a #NUM! (which occurs when the row only has zeroes and blanks.

Any assistance with this is greatly appreciated
Thanks in advance - D

 

[Lilliabeth]

=IF(ISERROR(SMALL(C15:AF15,COUNTIF($C$15:$A$15,0)+1)),"",SMALL(C15:AF15,COUNTIF($C$15:$A$15,0)+1))

 

[dhamDIT]

Thanks Lillabeth! that does solve the error issue but I still have the additional criteria of the
$C$1:$AF$1="Mon" to add as well

Thanks D

 

[dhamDIT]

Sorry just notice I did not actually post the correct starting formula for min - it sb

=IF(MIN(C23:AF23)=0,SMALL(C3:AF3,COUNTIF(C3:AF3,"=0")+1),MIN(C3:AF3))

 

[firefytr]

Do you really want the Small function? For Min & 0 you can use ..

=MIN(IF((C23:AF23=0)*(C23:AF23<>0),C3:AF3))

..entered with CSE.

Regards,
Zack Barresse

 

[dhamDIT]

Thanks Zach, you pointed me in the right direction and I was ultimately able to get what I needed. Array I ended up with is

=MIN(IF($C$1:$AF$1="Mon",IF($C3:$AF3>0,$C3:$AF3)))

Gives me the minimum, greater than zero in Mon columns.

 

[macropod]

Hi dhamDIT,

You can get the same result with the somewhat shorter:
=MIN(IF((C23:AF23=0)*(C23:AF23>0),C3:AF3))
This may also execute faster, but you might want to do your own benchmarking to confirm/deny.


[MS MVP - Word]

 

[firefytr]

Oops, had the extra character in there didn't I.. hehe. Glad you got it sorted though.

-----------
Regards,
Zack Barresse