average value from a table

[TUNGANA KURMA RAJU]

I am looking for a function that gives me best average rate from a table
without creating a helper column. col A is date ,col B to col E price1 ,price
2,price 3,price 4.
dates in col A are in ascending order but not continuous.By looking from
certain date in col a ,on whice date the best average price arrived.
date price 1 price 2 price 3 price 4
02-jan 114.5 116.7 112.85 115 (avg.price=114.76)
compare this avg rates all rows till end of the table

[Domenic]

Assumptions:

A2:E5 contains your data

By 'best average price' you mean lowest average price

Formula:

=INDEX(A2:A5,MATCH(MIN(SUBTOTAL(1,OFFSET(B2:E5,ROW(B2:E5)-ROW(B2),0,1))),
SUBTOTAL(1,OFFSET(B2:E5,ROW(B2:E5)-ROW(B2),0,1)),0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. If by 'best' you
mean highest average price, change MIN to MAX.

Hope this helps!

In article <[email protected]>,
TUNGANA KURMA RAJU <[email protected]> wrote:

> I am looking for a function that gives me best average rate from a table
> without creating a helper column. col A is date ,col B to col E price1 ,price
> 2,price 3,price 4.
> dates in col A are in ascending order but not continuous.By looking from
> certain date in col a ,on whice date the best average price arrived.
> date price 1 price 2 price 3 price 4
> 02-jan 114.5 116.7 112.85 115 (avg.price=114.76)
> compare this avg rates all rows till end of the table

[TUNGANA KURMA RAJU]

Thaks Domenic,you helped me a great.Since last week I am stuggling to get it
by using only offset function.'Best average price' means ' highest'.I got
perfect date with your formula,incase I want the best avg.price then what
changes are to be done to this formula ?.Thanks once again.

"Domenic" wrote:

> Assumptions:
>
> A2:E5 contains your data
>
> By 'best average price' you mean lowest average price
>
> Formula:
>
> =INDEX(A2:A5,MATCH(MIN(SUBTOTAL(1,OFFSET(B2:E5,ROW(B2:E5)-ROW(B2),0,1))),
> SUBTOTAL(1,OFFSET(B2:E5,ROW(B2:E5)-ROW(B2),0,1)),0))
>
> ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. If by 'best' you
> mean highest average price, change MIN to MAX.
>
> Hope this helps!
>
> In article <[email protected]>,
> TUNGANA KURMA RAJU <[email protected]> wrote:
>
> > I am looking for a function that gives me best average rate from a table
> > without creating a helper column. col A is date ,col B to col E price1 ,price
> > 2,price 3,price 4.
> > dates in col A are in ascending order but not continuous.By looking from
> > certain date in col a ,on whice date the best average price arrived.
> > date price 1 price 2 price 3 price 4
> > 02-jan 114.5 116.7 112.85 115 (avg.price=114.76)
> > compare this avg rates all rows till end of the table
>

[TUNGANA KURMA RAJU]

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?

"Domenic" wrote:

> Assumptions:
>
> A2:E5 contains your data
>
> By 'best average price' you mean lowest average price
>
> Formula:
>
> =INDEX(A2:A5,MATCH(MIN(SUBTOTAL(1,OFFSET(B2:E5,ROW(B2:E5)-ROW(B2),0,1))),
> SUBTOTAL(1,OFFSET(B2:E5,ROW(B2:E5)-ROW(B2),0,1)),0))
>
> ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. If by 'best' you
> mean highest average price, change MIN to MAX.
>
> Hope this helps!
>
> In article <[email protected]>,
> TUNGANA KURMA RAJU <[email protected]> wrote:
>
> > I am looking for a function that gives me best average rate from a table
> > without creating a helper column. col A is date ,col B to col E price1 ,price
> > 2,price 3,price 4.
> > dates in col A are in ascending order but not continuous.By looking from
> > certain date in col a ,on whice date the best average price arrived.
> > date price 1 price 2 price 3 price 4
> > 02-jan 114.5 116.7 112.85 115 (avg.price=114.76)
> > compare this avg rates all rows till end of the table
>

[Bob Phillips]

=MIN(IF(A2:A100>=--"2006-01-04",SUBTOTAL(1,OFFSET(B2:E100,ROW(B2:E100)-ROW(B
2),0,1))))

still an array formula

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"TUNGANA KURMA RAJU" <[email protected]> wrote in
message news:[email protected]...
> 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?
>
> "Domenic" wrote:
>
> > Assumptions:
> >
> > A2:E5 contains your data
> >
> > By 'best average price' you mean lowest average price
> >
> > Formula:
> >
> >
=INDEX(A2:A5,MATCH(MIN(SUBTOTAL(1,OFFSET(B2:E5,ROW(B2:E5)-ROW(B2),0,1))),
> > SUBTOTAL(1,OFFSET(B2:E5,ROW(B2:E5)-ROW(B2),0,1)),0))
> >
> > ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. If by 'best'
you
> > mean highest average price, change MIN to MAX.
> >
> > Hope this helps!
> >
> > In article <[email protected]>,
> > TUNGANA KURMA RAJU <[email protected]> wrote:
> >
> > > I am looking for a function that gives me best average rate from a
table
> > > without creating a helper column. col A is date ,col B to col E price1
,price
> > > 2,price 3,price 4.
> > > dates in col A are in ascending order but not continuous.By looking
from
> > > certain date in col a ,on whice date the best average price arrived.
> > > date price 1 price 2 price 3 price 4
> > > 02-jan 114.5 116.7 112.85 115 (avg.price=114.76)
> > > compare this avg rates all rows till end of the table
> >

[TUNGANA KURMA RAJU]

Thanks,Bob ! there is one more doubt among my replies,please refer and reply

"Bob Phillips" wrote:

> =MIN(IF(A2:A100>=--"2006-01-04",SUBTOTAL(1,OFFSET(B2:E100,ROW(B2:E100)-ROW(B
> 2),0,1))))
>
> still an array formula
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "TUNGANA KURMA RAJU" <[email protected]> wrote in
> message news:[email protected]...
> > 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?
> >
> > "Domenic" wrote:
> >
> > > Assumptions:
> > >
> > > A2:E5 contains your data
> > >
> > > By 'best average price' you mean lowest average price
> > >
> > > Formula:
> > >
> > >
> =INDEX(A2:A5,MATCH(MIN(SUBTOTAL(1,OFFSET(B2:E5,ROW(B2:E5)-ROW(B2),0,1))),
> > > SUBTOTAL(1,OFFSET(B2:E5,ROW(B2:E5)-ROW(B2),0,1)),0))
> > >
> > > ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. If by 'best'
> you
> > > mean highest average price, change MIN to MAX.
> > >
> > > Hope this helps!
> > >
> > > In article <[email protected]>,
> > > TUNGANA KURMA RAJU <[email protected]> wrote:
> > >
> > > > I am looking for a function that gives me best average rate from a
> table
> > > > without creating a helper column. col A is date ,col B to col E price1
> ,price
> > > > 2,price 3,price 4.
> > > > dates in col A are in ascending order but not continuous.By looking
> from
> > > > certain date in col a ,on whice date the best average price arrived.
> > > > date price 1 price 2 price 3 price 4
> > > > 02-jan 114.5 116.7 112.85 115 (avg.price=114.76)
> > > > compare this avg rates all rows till end of the table
> > >
>
>
>

[Domenic]

In article <[email protected]>,
TUNGANA KURMA RAJU <[email protected]> wrote:

> Thaks Domenic,you helped me a great.

You're very welcome! Glad I could help!

>Incase I want the best avg.price then what changes are to be done to
this formula ?

=MAX(SUBTOTAL(1,OFFSET(B2:E100,ROW(B2:E100)-ROW(B2),0,1)))

....confirmed with CONTROL+SHIFT+ENTER

[Domenic]

In article <[email protected]>,
TUNGANA KURMA RAJU <[email protected]> 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,OFFSET(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!

[TUNGANA KURMA RAJU]

Thank you ,all formulas working great.

"Domenic" wrote:

> In article <[email protected]>,
> TUNGANA KURMA RAJU <[email protected]> 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,OFFSET(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!
>

[TUNGANA KURMA RAJU]

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 <[email protected]>,
> TUNGANA KURMA RAJU <[email protected]> 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,OFFSET(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!
>

[Domenic]

Hopefully, I've understood you correctly. Let's assume the following...

Sheet1 contains the source table (Average Prices)

Sheet2 contains the results table (BuyDate, XBonusDate, etc.)

Try the following formulas, which need to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER....

> 1.To find highest avg.price from buyDate(04-Jan-06) to a datebefore
> XBonusDate(18-jan-06)

I2, copied down:

=MAX(IF(Sheet1!A$2:A$100>=Sheet2!F2,IF(Sheet1!A$2:A$100<IF(N(Sheet2!G2),S
heet2!G2,9.99999999999999E+307),SUBTOTAL(1,OFFSET(Sheet1!B$2:E$100,ROW(Sh
eet1!B$2:E$100)-ROW(Sheet1!B$2),0,1)))))

> 2.To find highest avg.price from XBonusDate(19-jan-06) to till date in my
> table and mutiply the value with Bonus factor.

K2, copied down:

=IF(N(Sheet2!G2),MAX(IF(Sheet1!A$2:A$100>=Sheet2!G2,SUBTOTAL(1,OFFSET(She
et1!B$2:E$100,ROW(Sheet1!B$2:E$100)-ROW(Sheet1!B$2),0,1))))*Sheet2!H2,0)

> 3.To find the corresponding date for maximum value of above two tasks.

J2, copied down:

=INDEX(Sheet1!A$2:A$100,MATCH(1,(Sheet1!A$2:A$100>=Sheet2!F2)*(Sheet1!A$2
:A$100<IF(N(Sheet2!G2),Sheet2!G2,9.99999999999999E+307))*(SUBTOTAL(1,OFFS
ET(Sheet1!B$2:E$100,ROW(Sheet1!B$2:E$100)-ROW(Sheet1!B$2),0,1))=Sheet2!I2
),0))

and

L2, copied down:

=IF(N(Sheet2!G2),INDEX(Sheet1!A$2:A$100,MATCH(1,(Sheet1!A$2:A$100>=Sheet2
!G2)*(SUBTOTAL(1,OFFSET(Sheet1!B$2:E$100,ROW(Sheet1!B$2:E$100)-ROW(Sheet1
!B2),0,1))=Sheet2!K2/Sheet2!H2),0)),#N/A)

Hope this helps!

In article <[email protected]>,
TUNGANA KURMA RAJU <[email protected]> wrote:

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

[TUNGANA KURMA RAJU]

Mr.Dominic,thanks for responding ,but this time you could not catch my
logic.I want sigle cell formula to get the best sell date.(there are no 2
sheets).
If a company announces bonus(assume 1:1) a record date(xbonus date) is fixed
to give that additional shares to the shareholders,and from that date the
share price will be reduced according to the ratio of bonus,thus the share
holders will get additional shares.So, my logic is if the daily avg.price of
the share from buy date to a day before xbonus date is greater than daily
avg.price from xbonus date to till date*2 ,the best sell date is the maximum
of daily avg.price from buy date to a day before xbonus date.If the daily
avg.price from buy date to a day before xbonus date is less than daily
avg.price from xbonus date to till date*2,the best sell date is corresponing
date row of maximum of daily avg.price from xbonus date to till date.Almost I
have arrived this formula logic with the help of your earlier formulas you
suggested.Only icing part is pending.You can also suggest me a new
method.Thanks for writing me with patience.

"Domenic" wrote:

> Hopefully, I've understood you correctly. Let's assume the following...
>
> Sheet1 contains the source table (Average Prices)
>
> Sheet2 contains the results table (BuyDate, XBonusDate, etc.)
>
> Try the following formulas, which need to be confirmed with
> CONTROL+SHIFT+ENTER, not just ENTER....
>
> > 1.To find highest avg.price from buyDate(04-Jan-06) to a datebefore
> > XBonusDate(18-jan-06)
>
> I2, copied down:
>
> =MAX(IF(Sheet1!A$2:A$100>=Sheet2!F2,IF(Sheet1!A$2:A$100<IF(N(Sheet2!G2),S
> heet2!G2,9.99999999999999E+307),SUBTOTAL(1,OFFSET(Sheet1!B$2:E$100,ROW(Sh
> eet1!B$2:E$100)-ROW(Sheet1!B$2),0,1)))))
>
> > 2.To find highest avg.price from XBonusDate(19-jan-06) to till date in my
> > table and mutiply the value with Bonus factor.
>
> K2, copied down:
>
> =IF(N(Sheet2!G2),MAX(IF(Sheet1!A$2:A$100>=Sheet2!G2,SUBTOTAL(1,OFFSET(She
> et1!B$2:E$100,ROW(Sheet1!B$2:E$100)-ROW(Sheet1!B$2),0,1))))*Sheet2!H2,0)
>
> > 3.To find the corresponding date for maximum value of above two tasks.
>
> J2, copied down:
>
> =INDEX(Sheet1!A$2:A$100,MATCH(1,(Sheet1!A$2:A$100>=Sheet2!F2)*(Sheet1!A$2
> :A$100<IF(N(Sheet2!G2),Sheet2!G2,9.99999999999999E+307))*(SUBTOTAL(1,OFFS
> ET(Sheet1!B$2:E$100,ROW(Sheet1!B$2:E$100)-ROW(Sheet1!B$2),0,1))=Sheet2!I2
> ),0))
>
> and
>
> L2, copied down:
>
> =IF(N(Sheet2!G2),INDEX(Sheet1!A$2:A$100,MATCH(1,(Sheet1!A$2:A$100>=Sheet2
> !G2)*(SUBTOTAL(1,OFFSET(Sheet1!B$2:E$100,ROW(Sheet1!B$2:E$100)-ROW(Sheet1
> !B2),0,1))=Sheet2!K2/Sheet2!H2),0)),#N/A)
>
> Hope this helps!
>
> In article <[email protected]>,
> TUNGANA KURMA RAJU <[email protected]> wrote:
>
> > 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]

Let's see if I've understood you correctly....

For the highest average price greater than or equal to the BuyDate and
less than the XBonusDate...

I2:

=MAX(IF(A2:A100>=F2,IF(A2:A100<G2,SUBTOTAL(1,OFFSET(B2:E100,ROW(B2:E100)-
ROW(B2),0,1)))))

....confirmed with CONTROL+SHIFT+ENTER.

For the corresponding date...

J2:

=INDEX(A2:A100,MATCH(1,(A2:A100>=F2)*(A2:A100<G2)*(SUBTOTAL(1,OFFSET(B2:E
100,ROW(B2:E100)-ROW(B2),0,1))=I2),0))

....confirmed with CONTROL+SHIFT=ENTER

For the highest average price greater than or equal to the XBonusDate,
multiplied by 2...

K2:

=MAX(IF(A2:A100>=G2,SUBTOTAL(1,OFFSET(B2:E100,ROW(B2:E100)-ROW(B2),0,1)))
)*H2

....confirmed with CONTROL+SHIFT+ENTER

For the corresponding date...

L2:

=INDEX(A2:A100,MATCH(1,(A2:A100>=G2)*(SUBTOTAL(1,OFFSET(B2:E100,ROW(B2:E1
00)-ROW(B2),0,1))=K2/H2),0))

....confirmed with CONTROL+SHIFT+ENTER

To return the sought after date...

M2:

=IF(I2>K2,J2,L2)

Note that if the highest average price greater than or equal to the
BuyDate and less than the XBonusDate is the same as the highest average
price greater than or equal to the XBonusDate, the date corresponding to
the latter is returned. To return the former instead, use the following
formula...

=IF(I2>=K2,J2,L2)

Also, if for example there are two or more dates tied with the highest
average price, the above formulas (J2 and L2) return the first
occurrence. To return the last occurrence, try the following formulas
instead...

J2:

=LOOKUP(2,1/((A2:A100>=F2)*(A2:A100<G2)*(SUBTOTAL(1,OFFSET(B2:E100,ROW(B2
:E100)-ROW(B2),0,1))=I2)),A2:A100)

....confirmed with just ENTER

L2:

=LOOKUP(2,1/((A2:A100>=G2)*(SUBTOTAL(1,OFFSET(B2:E100,ROW(B2:E100)-ROW(B2
),0,1))=K2/H2)),A2:A100)

....confirmed with just ENTER

Hope this helps!

In article <[email protected]>,
TUNGANA KURMA RAJU <[email protected]> wrote:

> Mr.Dominic,thanks for responding ,but this time you could not catch my
> logic.I want sigle cell formula to get the best sell date.(there are no 2
> sheets).
> If a company announces bonus(assume 1:1) a record date(xbonus date) is fixed
> to give that additional shares to the shareholders,and from that date the
> share price will be reduced according to the ratio of bonus,thus the share
> holders will get additional shares.So, my logic is if the daily avg.price of
> the share from buy date to a day before xbonus date is greater than daily
> avg.price from xbonus date to till date*2 ,the best sell date is the maximum
> of daily avg.price from buy date to a day before xbonus date.If the daily
> avg.price from buy date to a day before xbonus date is less than daily
> avg.price from xbonus date to till date*2,the best sell date is corresponing
> date row of maximum of daily avg.price from xbonus date to till date.Almost I
> have arrived this formula logic with the help of your earlier formulas you
> suggested.Only icing part is pending.You can also suggest me a new
> method.Thanks for writing me with patience.
>
> "Domenic" wrote:
>
> > Hopefully, I've understood you correctly. Let's assume the following...
> >
> > Sheet1 contains the source table (Average Prices)
> >
> > Sheet2 contains the results table (BuyDate, XBonusDate, etc.)
> >
> > Try the following formulas, which need to be confirmed with
> > CONTROL+SHIFT+ENTER, not just ENTER....
> >
> > > 1.To find highest avg.price from buyDate(04-Jan-06) to a datebefore
> > > XBonusDate(18-jan-06)
> >
> > I2, copied down:
> >
> > =MAX(IF(Sheet1!A$2:A$100>=Sheet2!F2,IF(Sheet1!A$2:A$100<IF(N(Sheet2!G2),S
> > heet2!G2,9.99999999999999E+307),SUBTOTAL(1,OFFSET(Sheet1!B$2:E$100,ROW(Sh
> > eet1!B$2:E$100)-ROW(Sheet1!B$2),0,1)))))
> >
> > > 2.To find highest avg.price from XBonusDate(19-jan-06) to till date in my
> > > table and mutiply the value with Bonus factor.
> >
> > K2, copied down:
> >
> > =IF(N(Sheet2!G2),MAX(IF(Sheet1!A$2:A$100>=Sheet2!G2,SUBTOTAL(1,OFFSET(She
> > et1!B$2:E$100,ROW(Sheet1!B$2:E$100)-ROW(Sheet1!B$2),0,1))))*Sheet2!H2,0)
> >
> > > 3.To find the corresponding date for maximum value of above two tasks.
> >
> > J2, copied down:
> >
> > =INDEX(Sheet1!A$2:A$100,MATCH(1,(Sheet1!A$2:A$100>=Sheet2!F2)*(Sheet1!A$2
> > :A$100<IF(N(Sheet2!G2),Sheet2!G2,9.99999999999999E+307))*(SUBTOTAL(1,OFFS
> > ET(Sheet1!B$2:E$100,ROW(Sheet1!B$2:E$100)-ROW(Sheet1!B$2),0,1))=Sheet2!I2
> > ),0))
> >
> > and
> >
> > L2, copied down:
> >
> > =IF(N(Sheet2!G2),INDEX(Sheet1!A$2:A$100,MATCH(1,(Sheet1!A$2:A$100>=Sheet2
> > !G2)*(SUBTOTAL(1,OFFSET(Sheet1!B$2:E$100,ROW(Sheet1!B$2:E$100)-ROW(Sheet1
> > !B2),0,1))=Sheet2!K2/Sheet2!H2),0)),#N/A)
> >
> > Hope this helps!
> >
> > In article <[email protected]>,
> > TUNGANA KURMA RAJU <[email protected]> wrote:
> >
> > > 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.
> >

[TUNGANA KURMA RAJU]

Thanks,Dominic,With your formula posted on 03/13/2006 at 6.12am ,I have
cracked single cell formula for "best cell date".I need not to create helper
cell formulas as you described in this post.Thanks once again.

"Domenic" wrote:

> Let's see if I've understood you correctly....
>
> For the highest average price greater than or equal to the BuyDate and
> less than the XBonusDate...
>
> I2:
>
> =MAX(IF(A2:A100>=F2,IF(A2:A100<G2,SUBTOTAL(1,OFFSET(B2:E100,ROW(B2:E100)-
> ROW(B2),0,1)))))
>
> ....confirmed with CONTROL+SHIFT+ENTER.
>
> For the corresponding date...
>
> J2:
>
> =INDEX(A2:A100,MATCH(1,(A2:A100>=F2)*(A2:A100<G2)*(SUBTOTAL(1,OFFSET(B2:E
> 100,ROW(B2:E100)-ROW(B2),0,1))=I2),0))
>
> ....confirmed with CONTROL+SHIFT=ENTER
>
> For the highest average price greater than or equal to the XBonusDate,
> multiplied by 2...
>
> K2:
>
> =MAX(IF(A2:A100>=G2,SUBTOTAL(1,OFFSET(B2:E100,ROW(B2:E100)-ROW(B2),0,1)))
> )*H2
>
> ....confirmed with CONTROL+SHIFT+ENTER
>
> For the corresponding date...
>
> L2:
>
> =INDEX(A2:A100,MATCH(1,(A2:A100>=G2)*(SUBTOTAL(1,OFFSET(B2:E100,ROW(B2:E1
> 00)-ROW(B2),0,1))=K2/H2),0))
>
> ....confirmed with CONTROL+SHIFT+ENTER
>
> To return the sought after date...
>
> M2:
>
> =IF(I2>K2,J2,L2)
>
> Note that if the highest average price greater than or equal to the
> BuyDate and less than the XBonusDate is the same as the highest average
> price greater than or equal to the XBonusDate, the date corresponding to
> the latter is returned. To return the former instead, use the following
> formula...
>
> =IF(I2>=K2,J2,L2)
>
> Also, if for example there are two or more dates tied with the highest
> average price, the above formulas (J2 and L2) return the first
> occurrence. To return the last occurrence, try the following formulas
> instead...
>
> J2:
>
> =LOOKUP(2,1/((A2:A100>=F2)*(A2:A100<G2)*(SUBTOTAL(1,OFFSET(B2:E100,ROW(B2
> :E100)-ROW(B2),0,1))=I2)),A2:A100)
>
> ....confirmed with just ENTER
>
> L2:
>
> =LOOKUP(2,1/((A2:A100>=G2)*(SUBTOTAL(1,OFFSET(B2:E100,ROW(B2:E100)-ROW(B2
> ),0,1))=K2/H2)),A2:A100)
>
> ....confirmed with just ENTER
>
> Hope this helps!
>
> In article <[email protected]>,
> TUNGANA KURMA RAJU <[email protected]> wrote:
>
> > Mr.Dominic,thanks for responding ,but this time you could not catch my
> > logic.I want sigle cell formula to get the best sell date.(there are no 2
> > sheets).
> > If a company announces bonus(assume 1:1) a record date(xbonus date) is fixed
> > to give that additional shares to the shareholders,and from that date the
> > share price will be reduced according to the ratio of bonus,thus the share
> > holders will get additional shares.So, my logic is if the daily avg.price of
> > the share from buy date to a day before xbonus date is greater than daily
> > avg.price from xbonus date to till date*2 ,the best sell date is the maximum
> > of daily avg.price from buy date to a day before xbonus date.If the daily
> > avg.price from buy date to a day before xbonus date is less than daily
> > avg.price from xbonus date to till date*2,the best sell date is corresponing
> > date row of maximum of daily avg.price from xbonus date to till date.Almost I
> > have arrived this formula logic with the help of your earlier formulas you
> > suggested.Only icing part is pending.You can also suggest me a new
> > method.Thanks for writing me with patience.
> >
> > "Domenic" wrote:
> >
> > > Hopefully, I've understood you correctly. Let's assume the following...
> > >
> > > Sheet1 contains the source table (Average Prices)
> > >
> > > Sheet2 contains the results table (BuyDate, XBonusDate, etc.)
> > >
> > > Try the following formulas, which need to be confirmed with
> > > CONTROL+SHIFT+ENTER, not just ENTER....
> > >
> > > > 1.To find highest avg.price from buyDate(04-Jan-06) to a datebefore
> > > > XBonusDate(18-jan-06)
> > >
> > > I2, copied down:
> > >
> > > =MAX(IF(Sheet1!A$2:A$100>=Sheet2!F2,IF(Sheet1!A$2:A$100<IF(N(Sheet2!G2),S
> > > heet2!G2,9.99999999999999E+307),SUBTOTAL(1,OFFSET(Sheet1!B$2:E$100,ROW(Sh
> > > eet1!B$2:E$100)-ROW(Sheet1!B$2),0,1)))))
> > >
> > > > 2.To find highest avg.price from XBonusDate(19-jan-06) to till date in my
> > > > table and mutiply the value with Bonus factor.
> > >
> > > K2, copied down:
> > >
> > > =IF(N(Sheet2!G2),MAX(IF(Sheet1!A$2:A$100>=Sheet2!G2,SUBTOTAL(1,OFFSET(She
> > > et1!B$2:E$100,ROW(Sheet1!B$2:E$100)-ROW(Sheet1!B$2),0,1))))*Sheet2!H2,0)
> > >
> > > > 3.To find the corresponding date for maximum value of above two tasks.
> > >
> > > J2, copied down:
> > >
> > > =INDEX(Sheet1!A$2:A$100,MATCH(1,(Sheet1!A$2:A$100>=Sheet2!F2)*(Sheet1!A$2
> > > :A$100<IF(N(Sheet2!G2),Sheet2!G2,9.99999999999999E+307))*(SUBTOTAL(1,OFFS
> > > ET(Sheet1!B$2:E$100,ROW(Sheet1!B$2:E$100)-ROW(Sheet1!B$2),0,1))=Sheet2!I2
> > > ),0))
> > >
> > > and
> > >
> > > L2, copied down:
> > >
> > > =IF(N(Sheet2!G2),INDEX(Sheet1!A$2:A$100,MATCH(1,(Sheet1!A$2:A$100>=Sheet2
> > > !G2)*(SUBTOTAL(1,OFFSET(Sheet1!B$2:E$100,ROW(Sheet1!B$2:E$100)-ROW(Sheet1
> > > !B2),0,1))=Sheet2!K2/Sheet2!H2),0)),#N/A)
> > >
> > > Hope this helps!
> > >
> > > In article <[email protected]>,
> > > TUNGANA KURMA RAJU <[email protected]> wrote:
> > >
> > > > 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]

You're very welcome! I'm very curious, though. Which formula did you
end up with?

In article <[email protected]>,
TUNGANA KURMA RAJU <[email protected]> wrote:

> Thanks,Dominic,With your formula posted on 03/13/2006 at 6.12am ,I have
> cracked single cell formula for "best cell date".I need not to create helper
> cell formulas as you described in this post.Thanks once again.

[TUNGANA KURMA RAJU]

I am sorry I could not inform you the formula I end up with.Its your
formula,not mine.
IF(MAX(IF(A2:A100>=J2,K2*SUBTOTAL(1,OFFSET(B2:E100,ROW(B2:E100)-ROW(B2),0,1))))>MAX(IF(A2:A100>=I2,SUBTOTAL(1,OFFSET(B2:E100,ROW(B2:E100)-ROW(B2),0,1)))),INDEX(A2:A100,MATCH(1,(A2:A100>=J2)*(SUBTOTAL(1,OFFSET(B2:E100,ROW(B2:E100)-ROW(B2),0,1))=MAX(IF(A2:A100>=J2,SUBTOTAL(1,OFFSET(B2:E100,ROW(B2:E100)-ROW(B2),0,1))))),0)),INDEX(A2:A100,MATCH(1,(A2:A100>=I2)*(SUBTOTAL(1,OFFSET(B2:E100,ROW(B2:E100)-ROW(B2),0,1))=MAX(IF(A2:A100>=I2,SUBTOTAL(1,OFFSET(B2:E100,ROW(B2:E100)-ROW(B2),0,1))))),0)))
confirm CTRL+SHIFT+ENTER
WHERE J2=xBonusDate,K2=BonusFactor,and I2=StartingDate
Thank you soooooooooomuch.

"Domenic" wrote:

> You're very welcome! I'm very curious, though. Which formula did you
> end up with?
>
> In article <[email protected]>,
> TUNGANA KURMA RAJU <[email protected]> wrote:
>
> > Thanks,Dominic,With your formula posted on 03/13/2006 at 6.12am ,I have
> > cracked single cell formula for "best cell date".I need not to create helper
> > cell formulas as you described in this post.Thanks once again.
>