Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 18th 06, 04:50 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 171
Default 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.

  #3   Report Post  
Old November 18th 06, 06:54 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,688
Default 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.



  #4   Report Post  
Old November 20th 06, 04:19 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 171
Default 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.




  #5   Report Post  
Old November 20th 06, 06:47 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,688
Default 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.








  #6   Report Post  
Old November 21st 06, 04:40 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 171
Default 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.






  #7   Report Post  
Old November 21st 06, 06:19 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,688
Default 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.








  #8   Report Post  
Old November 21st 06, 07:24 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 171
Default 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.









  #9   Report Post  
Old November 21st 06, 10:51 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 171
Default 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.









  #10   Report Post  
Old November 21st 06, 07:08 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,688
Default 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.













Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find category value based on date range? thewiz1972 Excel Worksheet Functions 5 June 10th 06 03:28 AM
find minimum of range based on multiple criteria Weissme Excel Worksheet Functions 3 May 21st 06 05:21 PM
dsum with date criteria Jed Martin Excel Worksheet Functions 0 March 24th 05 05:29 PM
Find Count of Items with certain criteria Tucson Guy Excel Discussion (Misc queries) 3 January 1st 05 02:11 PM
find date in Col A corresponding to min value in Col B gregl Excel Worksheet Functions 15 November 19th 04 04:01 AM


All times are GMT +1. The time now is 05:48 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017