Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
find date that meets a criteria
I have a range a1:c500.col A is "date",Col B is "high value",Col C is "Low
value".The basic calculation is "high value" minus "Low value" is volatile value of the day.I need a function to find most volatile day from the above range. Col A(Date)-----------Col B(high value)-------------------Col C(Low value) 11-NOV -------------100 -------------------95 12-NOV --------------125 ---------------------115 13-NOV --------------122 ----------------------118 16-NOV --------------101 -----------------------90 18-NOV ---------------85 -----------------------84 From the above example the maximum difference value is 11(101-90),so the most volatile day is 16-NOV.I tried with offset function ,could not succeed. Please help. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200611/1 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
find date that meets a criteria
You can use OFFSET if you can make a row reference.
First let's insert a helper column, column D In D1 enter = B1-C1 and copy down. The 11 is now visible and =OFFSET(A1,(MATCH(MAX(D:D),D:D,0)-1),0,1,1) will return the value in column A the corresponds to the max in column D. -- Gary''s Student "tkraju via OfficeKB.com" wrote: I have a range a1:c500.col A is "date",Col B is "high value",Col C is "Low value".The basic calculation is "high value" minus "Low value" is volatile value of the day.I need a function to find most volatile day from the above range. Col A(Date)-----------Col B(high value)-------------------Col C(Low value) 11-NOV -------------100 -------------------95 12-NOV --------------125 ---------------------115 13-NOV --------------122 ----------------------118 16-NOV --------------101 -----------------------90 18-NOV ---------------85 -----------------------84 From the above example the maximum difference value is 11(101-90),so the most volatile day is 16-NOV.I tried with offset function ,could not succeed. Please help. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200611/1 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
find date that meets a criteria
Someone more knowledgable than me can probably cut this procedure down
but I was able to get your 16-Nov like so: With your first data row on row 4: I created a "helper-column" (col D) - Cell D4 - =B4-C4 and copied down Then in an out-of-the-way cell I entered (say J1): =OFFSET(INDIRECT(ADDRESS(MATCH(MAX($D$4:$D$8),$D$4 :$D$8,0)+3,4)),0,-3) The "+3" above allows for the 3 rows BEFORE the first data row; Best I could do, for free... HTH "tkraju via OfficeKB.com" <u16627@uwe wrote in message news:6978810c3b99e@uwe: I have a range a1:c500.col A is "date",Col B is "high value",Col C is "Low value".The basic calculation is "high value" minus "Low value" is volatile value of the day.I need a function to find most volatile day from the above range. Col A(Date)-----------Col B(high value)-------------------Col C(Low value) 11-NOV -------------100 -------------------95 12-NOV --------------125 ---------------------115 13-NOV --------------122 ----------------------118 16-NOV --------------101 -----------------------90 18-NOV ---------------85 -----------------------84 From the above example the maximum difference value is 11(101-90),so the most volatile day is 16-NOV.I tried with offset function ,could not succeed. Please help. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200611/1 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
find date that meets a criteria
Try
=INDEX(A1:A500,MATCH(MAX(C1:C500-B1:B500),C1:C500-B1:B500,0)) confirmed with CTRL+SHIFT+ENTER "tkraju via OfficeKB.com" wrote: I have a range a1:c500.col A is "date",Col B is "high value",Col C is "Low value".The basic calculation is "high value" minus "Low value" is volatile value of the day.I need a function to find most volatile day from the above range. Col A(Date)-----------Col B(high value)-------------------Col C(Low value) 11-NOV -------------100 -------------------95 12-NOV --------------125 ---------------------115 13-NOV --------------122 ----------------------118 16-NOV --------------101 -----------------------90 18-NOV ---------------85 -----------------------84 From the above example the maximum difference value is 11(101-90),so the most volatile day is 16-NOV.I tried with offset function ,could not succeed. Please help. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200611/1 |
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 |