average value from a table

[Guest]

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!

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Domenic]

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]

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!

 

[Guest]

Thank you ,all formulas working great.

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!

 

[Guest]

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]

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!

 

[Guest]

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]

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!

 

[Guest]

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]

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

 

[Guest]

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.