Finding First Value that Falls in a Range Across Multiple Columns

[nb-]

Lets say I have data for a year that looks like this;

Date

Date

Temp High

Temp Low



01/05/13

19

10



02/05/13

20

11.1



03/05/13

20

12



04/05/13

20

11.5



05/05/13

19.5

11



06/05/13

20

12



07/05/13

20.2

12



08/05/13

21

13



09/05/13

22

12.5




What I want to do is find say the the first date in a range of where the high temperature exceeded 20, and the value it reached.

E.g. on the above data, the first time the high temperature was greater than 20 between the 03/05/13 and 08/05/13 (inclusive)?
The answer I would be looking for would be 07/05/13 and 20.2.

You could simply determine if this happens using a sumproduct and get the values using clunky array formulas, but I keep thinking there must be better and more efficient way of doing it, but I cant think of one. Any ideas?

[ashleyuk1984]

What are you doing with the data afterwards? I suppose you could just use conditional formatting??.. Anything higher than 20 then highlight. Then you would get your result.

Or you could use VBA.
Such as


Sub FindAbove20()

LastRow = Range("A65536").End(xlUp).Row


For x = 1 To LastRow
If Range("B" & x) > 20 Then
MsgBox Range("B" & x).Offset(0, -1)
Exit Sub
End If
Next x


End Sub




http://www.ultraimg.com/images/8iyr4.png

[D_Marcel]

Well, I guess this one don't have the best programming practices but it works for your purpose:


Sub Findind_First_Value()


Dim I_Date As Date
Dim L_Date As Date
Dim Register As Range
Dim Temp_High() As Long


I_Date = InputBox("Please, type the initial data", "Microsoft Excel")
L_Date = InputBox("Please, type the end data", "Microsoft Excel")


Set Source = Worksheets("Plan1")
Set WorkArea = Source.Range(Cells(2, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1))


For Each Register In WorkArea
If Control <> "X" Then
If Register.Value >= I_Date And Register.Value <= L_Date Then
Value = Register.Offset(0, 1).Value
If Value > 20 Then
MsgBox "The first date when the temperature exceeded 20 is " & Register & " with the value " & Value & "."
Control = "X"
End If
End If
End If
Next Register
End Sub

Hope it helps!

[nb-]

Thank you guys. I was trying to avoid VBA (sorry I forgot to mention).

[D_Marcel]

Hi dude! When I wrote the code, I was trying to create an array to insert all values to temperatures and then display in a msgbox, but unfortunatelly I couldn't think in a way to display also the first date when the temperature exceeded twenty. This way, you can safely remove the instruction 'Dim Temp_High() as Long'

Well, build a solution to this case without VBA seems to be not easy, but I'll try to think in something.