Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
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 |