Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 18th 06, 12:34 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
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  
Old November 18th 06, 01:31 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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,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  
Old November 18th 06, 01:49 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
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" <[email protected] wrote in message
news:[email protected]:

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  
Old November 18th 06, 04:15 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
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 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 12:34 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