find date that meets a criteria
 

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.

find date that meets a criteria
 

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
--
Don Guillett
SalesAid Software

"TUNGANA KURMA RAJU" wrote in
message ...
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.

find date that meets a criteria
 

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

"TUNGANA KURMA RAJU" wrote in
message ...
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.

find date that meets a criteria
 

Thank you so much...its great.

"Biff" wrote:

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

"TUNGANA KURMA RAJU" wrote in
message ...
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.

find date that meets a criteria
 

You're welcome. Thanks for the feedback!

Biff

"TUNGANA KURMA RAJU" wrote in
message ...
Thank you so much...its great.

"Biff" wrote:

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

"TUNGANA KURMA RAJU" wrote
in
message ...
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.

find date that meets a criteria
 

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).

"Biff" wrote:

You're welcome. Thanks for the feedback!

Biff

"TUNGANA KURMA RAJU" wrote in
message ...
Thank you so much...its great.

"Biff" wrote:

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

"TUNGANA KURMA RAJU" wrote
in
message ...
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.

find date that meets a criteria
 

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:A 10<=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

"TUNGANA KURMA RAJU" wrote in
message ...
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).

"Biff" wrote:

You're welcome. Thanks for the feedback!

Biff

"TUNGANA KURMA RAJU" wrote
in
message ...
Thank you so much...its great.

"Biff" wrote:

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

"TUNGANA KURMA RAJU"
wrote
in
message ...
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.

find date that meets a criteria
 

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).

"Biff" wrote:

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:A 10<=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

"TUNGANA KURMA RAJU" wrote in
message ...
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).

"Biff" wrote:

You're welcome. Thanks for the feedback!

Biff

"TUNGANA KURMA RAJU" wrote
in
message ...
Thank you so much...its great.

"Biff" wrote:

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

"TUNGANA KURMA RAJU"
wrote
in
message ...
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.

find date that meets a criteria
 

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.

"Biff" wrote:

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:A 10<=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

"TUNGANA KURMA RAJU" wrote in
message ...
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).

"Biff" wrote:

You're welcome. Thanks for the feedback!

Biff

"TUNGANA KURMA RAJU" wrote
in
message ...
Thank you so much...its great.

"Biff" wrote:

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

"TUNGANA KURMA RAJU"
wrote
in
message ...
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.

find date that meets a criteria
 

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

"TUNGANA KURMA RAJU" wrote in
message ...
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.

"Biff" wrote:

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:A 10<=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

"TUNGANA KURMA RAJU" wrote
in
message ...
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).

"Biff" wrote:

You're welcome. Thanks for the feedback!

Biff

"TUNGANA KURMA RAJU"
wrote
in
message ...
Thank you so much...its great.

"Biff" wrote:

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

"TUNGANA KURMA RAJU"
wrote
in
message ...
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.