capturing a number

hello,
i have data inputting to various cells in real time, which is updated several times a second. ie cell g6, 1.9, 1.92, 1.87, 2, 2.1, 1.7 and so on.

what i need to do is, if this stream of numbers falls to or below a set number for instabce 1.9, i wish to be able to capture that number and then capture any further falls in value until it hits its lowest point, ie started at 2.2 fell to 1.89(hopefully capture, if it goes out to 3, does not matter, but if it drops to 1.5 then also capture this second number.

i thought i had it for a moment with if(g6<=1.9,g6,""), although it works, if g6 then falls lower the value changes, or if it goes higher 1.9, nothing. i need it to capture 1.9 or lower, and irrespective of any change in value, i need to capture this initial state.

hope this makes sense, any help/suggestions would be appreciated.

regards tony

i was unable to upload the file ... so here is the solution description below. hope this helps.

PS: if i may ask, what is the objective you have in mind, i.e. why do you have this wierd requirement to identify "the lowest number so far" kind of thing? curious.

1. assuming ... Sheet1, Cell "B2" is where you are inputting the stream of numbers
2. vba code for Sheet1 as follows ...

Const lowest_val As Single = 1.9

Private Static Sub Worksheet_Change(ByVal Target As Range)
Dim m As Single
If Target.Address = "$B$2" Then
If Not m = 0 Then
If Target.Value < m And Target.Value < lowest_val Then
m = Target.Value
b = MsgBox("The min value of the cell so far is ..." & m, vbOKOnly, "Min Val")
End If
Else
If Target.Value < lowest_val Then
m = Target.Value
b = MsgBox("The min value of the cell so far is ..." & m, vbOKOnly, "Min Val")
End If
End If
End If
End Sub

many thanks grishap. did not occur to me to attempt a vba solution.

the purose is quite simple. i am trying to capture movements in odds on a betting exchange. the prices are quoted in decimals as oppossed to traditional fractions ie, 3/1 against deci equiv 4
5/2 against deci equiv 3.5
evens deci equiv 2
10/11 on deci equiv 1.9
3/1 on (1/3) deci equiv 1.3 and so on.

i am connected to the betting exchange by api calls, and can see all price movemnts via a simple excel sheet.

i wish to record all horses whose price moves in to 10/11, and then capture the lowest price they reach.

ie a horse that wins will finish with a lay price of 1/100 or deci 1.01 the lowest price possable.

a horse that is in the frame near to the end will come to very short odds on, then will steam right out of the betting.

in short, capturing prices near to the end of a race

many thanks for your help, kind regards tony