find min value between date range

[Steves73]

Hi All

Ok, I know its a simple mistake but just can pin point it
Trying to return minimum value between 2 dates,
Formula in cell N11,

But, the formula works in the cell with the number "408" however when drag is down I am getting 0, I assume as some of the cell are blank the formula is retuning 0 as the minimum

Is there a way yo write the current formula to omit 0 and return the lower whole number?
Current Formula

"=MIN(IF((BK6:CZ6>=A6)*(BK6:CZ6<=B6),(BK11:CZ11)))"

 

[Skybluekid]

If you are using a version prior to 2019, you will have use Ctrl-Shift-Enter, because you are doing an array calculation.

 

[frabulator]

=SMALL(V12:AC12,COUNTIF($V$12:$AC$12,0)+1)

The above could should calculate the minimum without including zero

 

[Steves73]

Thanks all

Howevere I am trying to find the smallest between to date ranges (Date in Cells O3 & O4)

You will note that some of the ranges (Example - BJ11:BP11) have gaps, thus I assume the formula is picking them up and saying 0 if the lowest value.

Need the 0 omitted

I have put the formula in an array but still the same result

 

[frabulator]

are you dragging the formula that you posted straight down, as is?

What I mean is, are you adding in the stationary '$' to make sure you are looking at the same dates?

 

[Skybluekid]

What version are you using?

 

[Skybluekid]

Try:

Pre 2019
If you are dragging the formula down, try =IF(MIN(IF(($BK$6:$CZ$6>=A6)*($BK$6:$CZ$6<=B6),($BK$11:$CZ&11)))=0,"",MIN(IF(($BK$6:$CZ$6>=A6)*($BK$6:$CZ$6<=B6),($BK$11:$CZ&11)))) - Use Ctrl-Shift-Enter

After 2019
=IF(MINIFS($BK$11:$CZ&11,$BK$6:$CZ$6,">="&$A$6,$BK$6:$CZ$6,"<="&$B$6)=0,"",MINIFS($BK$11:$CZ&11,$BK$6:$CZ$6,">="&$A$6,$BK$6:$CZ$6,"<="&$B$6))

 

[Steves73]

Running office 365

 

[Steves73]

Hi All

After a few goes I managed to get it working, thank you very much for your help

Used - =IF(MIN(IF(($BK$6:$CZ$6>=A6)*($BK$6:$CZ$6<=B6),($BK$11:$CZ&11)))=0,"",MIN(IF(($BK$6:$CZ$6>=A6)*($BK$6:$CZ$6<=B6),($BK$11:$CZ&11))))