Home 
Search 
Today's Posts 
#1




find date that meets a criteria
i need a function that gives me a date from
col A Col BCol C DateHighValueLowValue 20/NOV/200650.2548.50 21/NOV/20066057.25 22/NOV/200655.7550.25 24/NOV/20065248 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




find date that meets a criteria
Try this:
Array entered: =INDEX(A2:A5,MATCH(MAX(B2:B5C2:C5),B2:B5C2: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 BCol C DateHighValueLowValue 20/NOV/200650.2548.50 21/NOV/20066057.25 22/NOV/200655.7550.25 24/NOV/20065248 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




find date that meets a criteria
Thank you so much...its great.
"Biff" wrote: Try this: Array entered: =INDEX(A2:A5,MATCH(MAX(B2:B5C2:C5),B2:B5C2: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 BCol C DateHighValueLowValue 20/NOV/200650.2548.50 21/NOV/20066057.25 22/NOV/200655.7550.25 24/NOV/20065248 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




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:B5C2:C5),B2:B5C2: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 BCol C DateHighValueLowValue 20/NOV/200650.2548.50 21/NOV/20066057.25 22/NOV/200655.7550.25 24/NOV/20065248 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




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:B5C2:C5),B2:B5C2: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 BCol C DateHighValueLowValue 20/NOV/200650.2548.50 21/NOV/20066057.25 22/NOV/200655.7550.25 24/NOV/20065248 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




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:B10C2:C10)),B2:B10C2: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:B5C2:C5),B2:B5C2: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 BCol C DateHighValueLowValue 20/NOV/200650.2548.50 21/NOV/20066057.25 22/NOV/200655.7550.25 24/NOV/20065248 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




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:B10C2:C10)),B2:B10C2: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:B5C2:C5),B2:B5C2: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 BCol C DateHighValueLowValue 20/NOV/200650.2548.50 21/NOV/20066057.25 22/NOV/200655.7550.25 24/NOV/20065248 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




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:B10C2:C10)),B2:B10C2: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:B5C2:C5),B2:B5C2: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 BCol C DateHighValueLowValue 20/NOV/200650.2548.50 21/NOV/20066057.25 22/NOV/200655.7550.25 24/NOV/20065248 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




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:B10C2:C10)),B2:B10C2: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:B5C2:C5),B2:B5C2: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 BCol C DateHighValueLowValue 20/NOV/200650.2548.50 21/NOV/20066057.25 22/NOV/200655.7550.25 24/NOV/20065248 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 
Display Modes  


Similar Threads  
Thread  Forum  
Find category value based on date range?  Excel Worksheet Functions  
find minimum of range based on multiple criteria  Excel Worksheet Functions  
dsum with date criteria  Excel Worksheet Functions  
Find Count of Items with certain criteria  Excel Discussion (Misc queries)  
find date in Col A corresponding to min value in Col B  Excel Worksheet Functions 