Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 109
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 422
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 174
Default 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
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 06:29 PM
Find Count of Items with certain criteria Tucson Guy Excel Discussion (Misc queries) 3 January 1st 05 03:11 PM
find date in Col A corresponding to min value in Col B gregl Excel Worksheet Functions 15 November 19th 04 05:01 AM


All times are GMT +1. The time now is 09:40 PM.

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

About Us

"It's about Microsoft Excel"