Mr.Domenic,thank you and I am coming to my project query to which all these
related , I want to accomplish a formula in "stock markets project" to
evaluate the "best sell date" in a cell.
My data contains a2:e1000 ,in col 'a' date values starts from 02-jan-2006 to
till date,as I said in earlier questions and b2:e1000 open,high low and
close prices of a scrip.
col a---col b---col c-- col d--col e---col f---- col g---------col h
Date---open--high----low---close--buyDate--XbonusDate--BonusFactor
04-jan-06 --19-jan-06---- 2
My task is:
1.To find highest avg.price from buyDate(04-Jan-06) to a datebefore
XBonusDate(18-jan-06)
2.To find highest avg.price from XBonusDate(19-jan-06) to till date in my
table and mutiply the value with Bonus factor.
3.To find the corresponding date for maximum value of above two tasks.
note:those scrips that does not have any bonus history,g2=00-jan-00 and h2=0
or blank.
can this be done with your formula.

"Domenic" wrote:

In article ,
TUNGANA KURMA RAJU wrote:

Mr.Dominic,A slight logic modification. My data contains a2:e100,in col a
date values starts from 02-Jan to till date.Suppose I want this avg.value
from 20-Jan to till date then what will be the formula?

Bob has given you the formula to find the lowest average price, starting
from a specified date. As you know, you can change MIN to MAX to find
the highest average price.

To find the corresponding date for the highest average price, starting
from a specified date, try the following formula, which also needs to be
confirmed with CONTROL+SHIFT+ENTER...

=INDEX(A2:A100,MATCH(1,(A2:A100=G2)*(SUBTOTAL(1,O FFSET(B2:E100,ROW(B2:E1
00)-ROW(B2),0,1))=MAX(IF(A2:A100=G2,SUBTOTAL(1,OFFSET (B2:E100,ROW(B2:E10
0)-ROW(B2),0,1))))),0))

....where G2 contains the starting date.

Hope this helps!