find date that meets a criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i need a function that gives me a date from
col A ---------------Col B-------------------Col C
Date----------------HighValue--------------LowValue
20/NOV/2006-----50.25--------------------48.50
21/NOV/2006-----60------------------------57.25
22/NOV/2006-----55.75--------------------50.25
24/NOV/2006-----52------------------------48
the difference of highvalue and lowvalue is the volatile spread value of the
day.
I want a function that gives me most(maximum) volatile day from the above
range.
The answer of the above range is 22/NOV/2006.(spread values of above range
are 1.75,2.75,5.50,4.00 and maximum value is 5.50).Thanks any body gives me a
compact function without creating a helper column to the above range.
 
How about a nice macro?

Sub maxvolatilevalue() 'cols g:i
mr = 0
For i = Cells(Rows.Count, "h").End(xlUp).Row To 7 Step -1
x = Cells(i, "H") - Cells(i, "i")
y = Cells(i - 1, "H") - Cells(i - 1, "i")
If x > y And x > mr Then mr = i
Next i
MsgBox Cells(mr, "g")
End Sub
 
Try this:

Array entered:

=INDEX(A2:A5,MATCH(MAX(B2:B5-C2:C5),B2:B5-C2:C5,0))

Format the cell as DATE.

Note: if there is more than one instance where the difference is the same
the first instance will be returned.

Biff
 
Thank you so much...its great.

Biff said:
Try this:

Array entered:

=INDEX(A2:A5,MATCH(MAX(B2:B5-C2:C5),B2:B5-C2:C5,0))

Format the cell as DATE.

Note: if there is more than one instance where the difference is the same
the first instance will be returned.

Biff
Click to expand...
 
Mr.Biff,just for curiosity,how to get most volatile day and volatile spread
value from a specified date to end of range date(say today).
 
I'm assuming (based on your posted sample) that the dates are in ascending
order:

Array entered:

=INDEX(A2:A10,MATCH(MAX(IF((A2:A10>=TODAY())*(A2:A10<=MAX(A2:A10)),B2:B10-C2:C10)),B2:B10-C2:C10,0))

Format as DATE

Assume the above formula is in E2. For the difference:

=VLOOKUP(E2,A2:C10,2,0)-VLOOKUP(E2,A2:C10,3,0)

Format as GENERAL or NUMBER

Biff
 
Thanks Mr.biff,no where the specific date is not referred in your formula.Say
from my posted sample I want most volatile day from 22/NOV/2006 to till
today.(my dates are in assending order).
 
Thank you so much,both formulas are worth enough.The correction I have done
in your first formula is,instead of "Today()" I used that "specific Date"
from Date which I want most volatile day.Thanks and please ignore my
previous reply.
 
Ok.

I thought you wanted from Today() to the end of the range. Since your dates
are in ascending order A2:A10<=MAX(A2:A10) would automatically find that end
of the range.

Thanks for the feedback!

Biff
 
Back
Top