| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
average value from a table
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
average value from a 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
average value from a 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
average value from a table
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
average value from a 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
average value from a 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
average value from a 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
average value from a 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! |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
average value from a table
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! |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
average value from a table
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! |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
average value from a table
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. |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
average value from a table
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!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. |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
average value from a table
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,OFFSE T(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,O FFSET(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(I2K2,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:E 100)-ROW(B2 ),0,1))=K2/H2)),A2:A100) ....confirmed with just ENTER Hope this helps! In article , TUNGANA KURMA RAJU 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!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. |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
average value from a table
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,OFFSE T(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,O FFSET(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(I2K2,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:E 100)-ROW(B2 ),0,1))=K2/H2)),A2:A100) ....confirmed with just ENTER Hope this helps! In article , TUNGANA KURMA RAJU 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!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. |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
average value from a table
You're very welcome! I'm very curious, though. Which formula did you
end up with? In article , TUNGANA KURMA RAJU 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. |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
average value from a table
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,OFFS ET(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:A10 0=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 , TUNGANA KURMA RAJU 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| in a pivot table, can the average include blank entries? | Excel Discussion (Misc queries) | |||
| Totals of calculated field in pivot table give incorrect results | Excel Worksheet Functions | |||
| moving average of figures from separate tables | Excel Discussion (Misc queries) | |||
| Lookup Table Dilemma | Excel Worksheet Functions | |||
| Pivot Tables, Help? | Excel Discussion (Misc queries) | |||
Linear Mode
