Finding the time, the first time a number is equal or above a certain number

[excelnabb]

Hi, Happy new year everybody!

I need help with two formulas

First formula: Should find the time (the green cell G119) the FIRST time it finds a cell in column C where the value is equal or above a certain number found in K120, and which match the DATE(22 nov) in cell K121.
Then I would have to copy that formula to find another number, ex 2.5

The second formula needs to find the lowest value in column D, and the time, between the two answers given by formula 1.

[Ace_XL]

With your sample workbook, try these

1. Array formula in K122
=MIN(IF($C$8:$C$374>K120,IF($G$8:$G$374>K121,$G$8:$G$374)))


2. Array formula in K131
=MIN(IF($C$8:$C$374>K130,$G$8:$G$374))

3. Array formula in L139
=MIN(IF($G$8:$G$374>L137,IF($G$8:$G$374<L138,$D$8:$D$374)))

4. Non-array formula in L140
=INDEX($G$8:$G$374,MATCH(1,INDEX(($D$8:$D$374=L139)*($G$8:$G$374>L137)*($G$8:$G$374<L138),0),0))


Note: Confirm array formulas with Ctrl+Shift+Enter and not just Enter

[mrice]

Try these user defined formulae - in the attachment

Please Login or Register  to view this content.

[excelnabb]

With your sample workbook, try these

1. Array formula in K122
=MIN(IF($C$8:$C$374>K120,IF($G$8:$G$374>K121,$G$8:$G$374)))


2. Array formula in K131
=MIN(IF($C$8:$C$374>K130,$G$8:$G$374))

3. Array formula in L139
=MIN(IF($G$8:$G$374>L137,IF($G$8:$G$374<L138,$D$8:$D$374)))

4. Non-array formula in L140
=INDEX($G$8:$G$374,MATCH(1,INDEX(($D$8:$D$374=L139)*($G$8:$G$374>L137)*($G$8:$G$374<L138),0),0))


Note: Confirm array formulas with Ctrl+Shift+Enter and not just Enter

Thank you so much! They all work flawless! What should I add to the formulas to get a blank cell if the formula doesn't find a value equal or higher to the number given in K120?

[Ace_XL]

Just wrap the formulas in an IFERROR(formula,"")

So..

=IFERROR(MIN(IF($C$8:$C$374>K120,IF($G$8:$G$374>K121,$G$8:$G$374))),"")

..so on and so forth for the others

[excelnabb]

Thanks, that works if there is no number in the K120, but if there is a number there, and its higher than the highest number in column C. Lets say I put 5 in K120, and the highest number in column C is 4. Then the formula gives me time: 00:00:00, instead of blank.

[Ace_XL]

Aah of course, in that case

=IF(MIN(IF($C$8:$C$374>K120,IF($G$8:$G$374>K121,$G$8:$G$374)))=0,"",MIN(IF($C$8:$C$374>K120,IF($G$8:$G$374>K121,$G$8:$G$374)))

Remember to array enter. Adapt for other formulas

Alternatively, you could change settings to hide zeros
https://support.office.com/en-us/art...5-a00e9e476b03

[excelnabb]

Thanks! Worked perfectly!

[excelnabb]

Try these user defined formulae - in the attachment

Please Login or Register  to view this content.

Thanks for the suggestions, but I went for the other solution