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