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

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 ,
TUNGANA KURMA RAJU 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

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 ,
TUNGANA KURMA RAJU 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

In article ,
TUNGANA KURMA RAJU 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

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 ,
TUNGANA KURMA RAJU 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

=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" wrote in
message ...
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 ,
TUNGANA KURMA RAJU 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

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" wrote in
message ...
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 ,
TUNGANA KURMA RAJU 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

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!

Thank you ,all formulas working great.

"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!

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!

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!G 2,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,(S heet1!A$2:A$100=Sheet2
!G2)*(SUBTOTAL(1,OFFSET(Sheet1!B$2:E$100,ROW(Sheet 1!B$2:E$100)-ROW(Sheet1
!B2),0,1))=Sheet2!K2/Sheet2!H2),0)),#N/A)

Hope this helps!

In article ,
TUNGANA KURMA RAJU 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.