Remember Me?

#1
November 18th 06, 12:34 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Aug 2006 Posts: 109
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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200611/1

#2
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
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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200611/1

#3
November 18th 06, 01:49 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 422
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):

The "+3" above allows for the 3 rows BEFORE the first data row;

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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200611/1

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200611/1

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post thewiz1972 Excel Worksheet Functions 5 June 10th 06 03:28 AM Weissme Excel Worksheet Functions 3 May 21st 06 05:21 PM Jed Martin Excel Worksheet Functions 0 March 24th 05 05:29 PM Tucson Guy Excel Discussion (Misc queries) 3 January 1st 05 02:11 PM gregl Excel Worksheet Functions 15 November 19th 04 04:01 AM

All times are GMT +1. The time now is 12:34 PM.