Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Value between 2 dates
I need formulas to calculate a dividend when it occurs between 2 dates.
Ive tried a few options & looked through the group but I dont have the skill to put the info I found to use. Thanks for any help. I have a sheet named Value as setup below. I have ranges for dividend rates and daily prices as below. What I need are the formulas to extract the Div Rate for column E and the Div Price for column F. So that I extract .25 for cell F3 and 15.94 for cell G3 and 0.484 for cell F8 and G8 when they occur. The lookup values in columns A & C may not always match the dates in the lookup ranges and finding the closest match that results using a standard lookup function wont always be correct. Value Sheet Monday Friday Current Div Added Total Date Price Date Price Shares Rate Price Shares Shares 2/8/99 16.3 2/5/99 16.28 1000 1000 2/16/99 15.94 2/12/99 15.86 =I2 .25 15.94 15.684 1015.684 2/22/99 16.07 2/19/99 15.8 =I3 3/1/99 15.82 2/26/99 15.85 =I4 3/8/99 16.23 3/5/99 16.24 =I5 3/15/99 16.4 3/12/99 16.43 =I6 3/22/99 15.96 3/19/99 16.45 =I7 0.484 16.44 29.957 1045.641 3/29/99 15.86 3/26/99 15.69 =I8 DivTable set as a named range Date Rate 3/4/99 2.038 3/12/99 0.25 3/17/99 0.484 3/30/99 0.063 PriceTable set as a named range Date Price 3/3/99 15.84 3/4/99 16.02 3/5/99 16.24 3/8/99 16.23 3/9/99 16.16 3/10/99 16.34 3/11/99 16.36 3/12/99 15.94 3/15/99 16.4 3/16/99 16.41 3/17/99 16.44 3/18/99 16.58 3/19/99 16.45 3/22/99 15.96 3/23/99 15.63 3/24/99 15.75 3/25/99 15.82 3/26/99 15.69 3/29/99 15.86 3/30/99 15.63 |
#2
|
|||
|
|||
You need to create a clearer description of your problem in order to get the
solution you are looking for. Try to simplify your problem, preferably focusing on one row. Normally when you get the solution for one row, you can copy down for the rest. Another useful technique is to ask for one solution at a time. For example, create another post solely for your date lookup problem. This will help you specify the problem more clearly. For example, if "A & C may not always match the dates in the lookup ranges", then tell us what the rules are so they will always match. It looks to me like you are trying to calculate the shares resulting from a dividend reinvestment program. Is this correct? If so, why does F3 (presumably for a dividend declared between 2/6/99 {is 2/16/99 a typo?} and 2/12/99) extract a price of 15.94 when that occurred on 3/12/99? Is 3/12/99 the dividend reinvestment date? If so, then I think you will need to add this date to your row. This will then allow you to look it up. Hope this helps. -- Regards, Fred "AG" wrote in message ... I need formulas to calculate a dividend when it occurs between 2 dates. I've tried a few options & looked through the group but I don't have the skill to put the info I found to use. Thanks for any help. I have a sheet named "Value" as setup below. I have ranges for dividend rates and daily prices as below. What I need are the formulas to extract the Div Rate for column E and the Div Price for column F. So that I extract .25 for cell F3 and 15.94 for cell G3 and 0.484 for cell F8 and G8 when they occur. The lookup values in columns A & C may not always match the dates in the lookup ranges and finding the closest match that results using a standard lookup function won't always be correct. Value Sheet Monday Friday Current Div Added Total Date Price Date Price Shares Rate Price Shares Shares 2/8/99 16.3 2/5/99 16.28 1000 1000 2/16/99 15.94 2/12/99 15.86 =I2 .25 15.94 15.684 1015.684 2/22/99 16.07 2/19/99 15.8 =I3 3/1/99 15.82 2/26/99 15.85 =I4 3/8/99 16.23 3/5/99 16.24 =I5 3/15/99 16.4 3/12/99 16.43 =I6 3/22/99 15.96 3/19/99 16.45 =I7 0.484 16.44 29.957 1045.641 3/29/99 15.86 3/26/99 15.69 =I8 DivTable set as a named range Date Rate 3/4/99 2.038 3/12/99 0.25 3/17/99 0.484 3/30/99 0.063 PriceTable set as a named range Date Price 3/3/99 15.84 3/4/99 16.02 3/5/99 16.24 3/8/99 16.23 3/9/99 16.16 3/10/99 16.34 3/11/99 16.36 3/12/99 15.94 3/15/99 16.4 3/16/99 16.41 3/17/99 16.44 3/18/99 16.58 3/19/99 16.45 3/22/99 15.96 3/23/99 15.63 3/24/99 15.75 3/25/99 15.82 3/26/99 15.69 3/29/99 15.86 3/30/99 15.63 |
#3
|
|||
|
|||
This doesn't give the results that you predict, but it is as I read the
description. Assuming PriceTable is M1:N21, and DivTable is O1:P21, then rate comes from =SUMPRODUCT(--(TRANSPOSE(ROW(INDIRECT($A3&":"&$C3)))=$O$2:$O$21) ,$P$2:$P$21) and price comes from =SUMPRODUCT(--(TRANSPOSE(ROW(INDIRECT(A3&":"&C3)))=$M$2:$M$21),$ N$2:$N$21) -- HTH RP (remove nothere from the email address if mailing direct) "AG" wrote in message ... I need formulas to calculate a dividend when it occurs between 2 dates. I've tried a few options & looked through the group but I don't have the skill to put the info I found to use. Thanks for any help. I have a sheet named "Value" as setup below. I have ranges for dividend rates and daily prices as below. What I need are the formulas to extract the Div Rate for column E and the Div Price for column F. So that I extract .25 for cell F3 and 15.94 for cell G3 and 0.484 for cell F8 and G8 when they occur. The lookup values in columns A & C may not always match the dates in the lookup ranges and finding the closest match that results using a standard lookup function won't always be correct. Value Sheet Monday Friday Current Div Added Total Date Price Date Price Shares Rate Price Shares Shares 2/8/99 16.3 2/5/99 16.28 1000 1000 2/16/99 15.94 2/12/99 15.86 =I2 .25 15.94 15.684 1015.684 2/22/99 16.07 2/19/99 15.8 =I3 3/1/99 15.82 2/26/99 15.85 =I4 3/8/99 16.23 3/5/99 16.24 =I5 3/15/99 16.4 3/12/99 16.43 =I6 3/22/99 15.96 3/19/99 16.45 =I7 0.484 16.44 29.957 1045.641 3/29/99 15.86 3/26/99 15.69 =I8 DivTable set as a named range Date Rate 3/4/99 2.038 3/12/99 0.25 3/17/99 0.484 3/30/99 0.063 PriceTable set as a named range Date Price 3/3/99 15.84 3/4/99 16.02 3/5/99 16.24 3/8/99 16.23 3/9/99 16.16 3/10/99 16.34 3/11/99 16.36 3/12/99 15.94 3/15/99 16.4 3/16/99 16.41 3/17/99 16.44 3/18/99 16.58 3/19/99 16.45 3/22/99 15.96 3/23/99 15.63 3/24/99 15.75 3/25/99 15.82 3/26/99 15.69 3/29/99 15.86 3/30/99 15.63 |
#4
|
|||
|
|||
Sorry for the confusion & error.
Ill answer you reply in reverse order. My error on the Value sheet was that the dividend should have been for 3/12/99. Ill include a corrected table. My table does want to calculate the shares from a dividend reinvestment. Actually the table shown is a simplified part of a sheet that I use for other purposes. I do know the formulas to calculate columns H and I so that wasnt supposed to be a point of confusion; I just didnt put that in the table for the sake of clarity. (which I guess wasnt too clear on my part!) So I do not need the formulas for columns H or I. Now as to the problem which I didnt clearly explain. I really am looking for formulas that I can use in columns F and G (on all rows that will be copied down) that will extract the rate and price of the dividend within the week in which it occurs. For column F, I need to know the formula that would extract the dividend rate from the DivTable that may occur between the dates in cell A5 and A6 (or C5 and C6) would also be an applicable lookup criterion.) For column I, I need to know the formula that would extract the price from the PriceTable that may occur between the dates in cell A5 and A6 (or C5 and C6) would also be an applicable lookup criterion.) I have this sheet setup to only track weekly activity; hence the dates in the columns A & C are Monday & Friday dates respectively. Now a dividend may occur on any day of the week on the row for that week. I do not need or want to include that date in my table. If I use a standard Vlookup formula to try and match the date of the dividend within its week of occurrence, there are times when Vlookup (because of its constraints) will find the closest match which may not be for the week in question. Value Sheet Monday Friday Current Div Added Total Date Price Date Price Shares Rate Price Shares Shares 2/8/99 16.3 2/5/99 16.28 1000 1000 2/16/99 15.94 2/12/99 15.86 =I2 ? ? =(E2*F2)/G2 =E2+H2 2/22/99 16.07 2/19/99 15.8 =I3 ? ? =(E3*F3)/G3 =E3+H3 3/1/99 15.82 2/26/99 15.85 =I4 ? ? =(E4*F4)/G4 =E4+H4 3/8/99 16.23 3/5/99 16.24 =I5 ? ? =(E5*F5)/G5 =E5+H5 3/15/99 16.4 3/12/99 16.43 =I6 .25 15.94 15.684 1015.684 3/22/99 15.96 3/19/99 16.45 =I7 .484 16.44 29.957 45.64184 3/29/99 15.86 3/26/99 15.69 =I8 ? ? =(E8*F8)/G8 =E8+H8 DivTable set as a named range Date Rate 3/4/99 2.038 3/12/99 0.25 3/17/99 0.484 3/30/99 0.063 PriceTable set as a named range Date Price 3/3/99 15.84 3/4/99 16.02 3/5/99 16.24 3/8/99 16.23 3/9/99 16.16 3/10/99 16.34 3/11/99 16.36 3/12/99 15.94 3/15/99 16.4 3/16/99 16.41 3/17/99 16.44 3/18/99 16.58 3/19/99 16.45 3/22/99 15.96 3/23/99 15.63 3/24/99 15.75 3/25/99 15.82 3/26/99 15.69 3/29/99 15.86 3/30/99 15.63 "Fred Smith" wrote: You need to create a clearer description of your problem in order to get the solution you are looking for. Try to simplify your problem, preferably focusing on one row. Normally when you get the solution for one row, you can copy down for the rest. Another useful technique is to ask for one solution at a time. For example, create another post solely for your date lookup problem. This will help you specify the problem more clearly. For example, if "A & C may not always match the dates in the lookup ranges", then tell us what the rules are so they will always match. It looks to me like you are trying to calculate the shares resulting from a dividend reinvestment program. Is this correct? If so, why does F3 (presumably for a dividend declared between 2/6/99 {is 2/16/99 a typo?} and 2/12/99) extract a price of 15.94 when that occurred on 3/12/99? Is 3/12/99 the dividend reinvestment date? If so, then I think you will need to add this date to your row. This will then allow you to look it up. Hope this helps. -- Regards, Fred "AG" wrote in message ... I need formulas to calculate a dividend when it occurs between 2 dates. I've tried a few options & looked through the group but I don't have the skill to put the info I found to use. Thanks for any help. I have a sheet named "Value" as setup below. I have ranges for dividend rates and daily prices as below. What I need are the formulas to extract the Div Rate for column E and the Div Price for column F. So that I extract .25 for cell F3 and 15.94 for cell G3 and 0.484 for cell F8 and G8 when they occur. The lookup values in columns A & C may not always match the dates in the lookup ranges and finding the closest match that results using a standard lookup function won't always be correct. Value Sheet Monday Friday Current Div Added Total Date Price Date Price Shares Rate Price Shares Shares 2/8/99 16.3 2/5/99 16.28 1000 1000 2/16/99 15.94 2/12/99 15.86 =I2 .25 15.94 15.684 1015.684 2/22/99 16.07 2/19/99 15.8 =I3 3/1/99 15.82 2/26/99 15.85 =I4 3/8/99 16.23 3/5/99 16.24 =I5 3/15/99 16.4 3/12/99 16.43 =I6 3/22/99 15.96 3/19/99 16.45 =I7 0.484 16.44 29.957 1045.641 3/29/99 15.86 3/26/99 15.69 =I8 DivTable set as a named range Date Rate 3/4/99 2.038 3/12/99 0.25 3/17/99 0.484 3/30/99 0.063 PriceTable set as a named range Date Price 3/3/99 15.84 3/4/99 16.02 3/5/99 16.24 3/8/99 16.23 3/9/99 16.16 3/10/99 16.34 3/11/99 16.36 3/12/99 15.94 3/15/99 16.4 3/16/99 16.41 3/17/99 16.44 3/18/99 16.58 3/19/99 16.45 3/22/99 15.96 3/23/99 15.63 3/24/99 15.75 3/25/99 15.82 3/26/99 15.69 3/29/99 15.86 3/30/99 15.63 |
#5
|
|||
|
|||
Thanks for the reply.
As to the prediction discrepancy, my fault as my original data was incorrect for the dividend date of 2/12/99; it should have been 3/17/99. Value Sheet Date Price Date Price Shares Rate Price Shares 2/8/99 16.3 2/5/99 16.28 1000 1000 2/16/99 15.94 2/12/99 15.86 2/22/99 16.07 2/19/99 15.8 3/1/99 15.82 2/26/99 15.85 3/8/99 16.23 3/5/99 16.24 3/15/99 16.4 3/12/99 16.43 .25 15.94 15.684 1015.684 3/22/99 15.96 3/19/99 16.45 .484 16.44 29.957 1045.641 3/29/99 15.86 3/26/99 15.69 I setup a corrected sheet as you interpreted my sheet to be (with the Div Price table in M2:M33 and the Div Rate inO2:P5, the formula does pickup the Div Rate for F7 on 3/12/99 but not for F8 for 3/17/99. BTW, if the formatting doesnt show the table clearly. Can I send you a copy? I also tried constructing a similar formula for the Div Price for column G but with no luck. For column F, I need to know the formula that would extract the dividend rate from the DivTable that may occur between the dates in cell A5 and A6 (or C5 and C6) would also be an applicable lookup criterion.) For column G, I need to know the formula that would extract the price from the PriceTable that may occur between the dates in cell A5 and A6 (or C5 and C6) would also be an applicable lookup criterion.) Any help would greatly appreciate. Thanks again. "Bob Phillips" wrote: This doesn't give the results that you predict, but it is as I read the description. Assuming PriceTable is M1:N21, and DivTable is O1:P21, then rate comes from =SUMPRODUCT(--(TRANSPOSE(ROW(INDIRECT($A3&":"&$C3)))=$O$2:$O$21) ,$P$2:$P$21) and price comes from =SUMPRODUCT(--(TRANSPOSE(ROW(INDIRECT(A3&":"&C3)))=$M$2:$M$21),$ N$2:$N$21) -- HTH RP (remove nothere from the email address if mailing direct) "AG" wrote in message ... I need formulas to calculate a dividend when it occurs between 2 dates. I've tried a few options & looked through the group but I don't have the skill to put the info I found to use. Thanks for any help. I have a sheet named "Value" as setup below. I have ranges for dividend rates and daily prices as below. What I need are the formulas to extract the Div Rate for column E and the Div Price for column F. So that I extract .25 for cell F3 and 15.94 for cell G3 and 0.484 for cell F8 and G8 when they occur. The lookup values in columns A & C may not always match the dates in the lookup ranges and finding the closest match that results using a standard lookup function won't always be correct. Value Sheet Monday Friday Current Div Added Total Date Price Date Price Shares Rate Price Shares Shares 2/8/99 16.3 2/5/99 16.28 1000 1000 2/16/99 15.94 2/12/99 15.86 =I2 .25 15.94 15.684 1015.684 2/22/99 16.07 2/19/99 15.8 =I3 3/1/99 15.82 2/26/99 15.85 =I4 3/8/99 16.23 3/5/99 16.24 =I5 3/15/99 16.4 3/12/99 16.43 =I6 3/22/99 15.96 3/19/99 16.45 =I7 0.484 16.44 29.957 1045.641 3/29/99 15.86 3/26/99 15.69 =I8 DivTable set as a named range Date Rate 3/4/99 2.038 3/12/99 0.25 3/17/99 0.484 3/30/99 0.063 PriceTable set as a named range Date Price 3/3/99 15.84 3/4/99 16.02 3/5/99 16.24 3/8/99 16.23 3/9/99 16.16 3/10/99 16.34 3/11/99 16.36 3/12/99 15.94 3/15/99 16.4 3/16/99 16.41 3/17/99 16.44 3/18/99 16.58 3/19/99 16.45 3/22/99 15.96 3/23/99 15.63 3/24/99 15.75 3/25/99 15.82 3/26/99 15.69 3/29/99 15.86 3/30/99 15.63 |
#6
|
|||
|
|||
AG,
Clear some confusion in my mind (BTW thanks for the clearer format data). F2: What dates should that be comparing against the dates in the Price/Div Table. I got confused by F7, F8 A5/C5 and A6/6. I had assumed that we were looking to see if any date in Price/Div table was within the dates A2-C2, and so on. -- HTH RP (remove nothere from the email address if mailing direct) "AG" wrote in message ... Thanks for the reply. As to the prediction discrepancy, my fault as my original data was incorrect for the dividend date of 2/12/99; it should have been 3/17/99. Value Sheet Date Price Date Price Shares Rate Price Shares 2/8/99 16.3 2/5/99 16.28 1000 1000 2/16/99 15.94 2/12/99 15.86 2/22/99 16.07 2/19/99 15.8 3/1/99 15.82 2/26/99 15.85 3/8/99 16.23 3/5/99 16.24 3/15/99 16.4 3/12/99 16.43 .25 15.94 15.684 1015.684 3/22/99 15.96 3/19/99 16.45 .484 16.44 29.957 1045.641 3/29/99 15.86 3/26/99 15.69 I setup a corrected sheet as you interpreted my sheet to be (with the Div Price table in M2:M33 and the Div Rate inO2:P5, the formula does pickup the Div Rate for F7 on 3/12/99 but not for F8 for 3/17/99. BTW, if the formatting doesn't show the table clearly. Can I send you a copy? I also tried constructing a similar formula for the Div Price for column G but with no luck. For column F, I need to know the formula that would extract the dividend rate from the DivTable that may occur between the dates in cell A5 and A6 (or C5 and C6) would also be an applicable lookup criterion.) For column G, I need to know the formula that would extract the price from the PriceTable that may occur between the dates in cell A5 and A6 (or C5 and C6) would also be an applicable lookup criterion.) Any help would greatly appreciate. Thanks again. "Bob Phillips" wrote: This doesn't give the results that you predict, but it is as I read the description. Assuming PriceTable is M1:N21, and DivTable is O1:P21, then rate comes from =SUMPRODUCT(--(TRANSPOSE(ROW(INDIRECT($A3&":"&$C3)))=$O$2:$O$21) ,$P$2:$P$21) and price comes from =SUMPRODUCT(--(TRANSPOSE(ROW(INDIRECT(A3&":"&C3)))=$M$2:$M$21),$ N$2:$N$21) -- HTH RP (remove nothere from the email address if mailing direct) "AG" wrote in message ... I need formulas to calculate a dividend when it occurs between 2 dates. I've tried a few options & looked through the group but I don't have the skill to put the info I found to use. Thanks for any help. I have a sheet named "Value" as setup below. I have ranges for dividend rates and daily prices as below. What I need are the formulas to extract the Div Rate for column E and the Div Price for column F. So that I extract .25 for cell F3 and 15.94 for cell G3 and 0.484 for cell F8 and G8 when they occur. The lookup values in columns A & C may not always match the dates in the lookup ranges and finding the closest match that results using a standard lookup function won't always be correct. Value Sheet Monday Friday Current Div Added Total Date Price Date Price Shares Rate Price Shares Shares 2/8/99 16.3 2/5/99 16.28 1000 1000 2/16/99 15.94 2/12/99 15.86 =I2 .25 15.94 15.684 1015.684 2/22/99 16.07 2/19/99 15.8 =I3 3/1/99 15.82 2/26/99 15.85 =I4 3/8/99 16.23 3/5/99 16.24 =I5 3/15/99 16.4 3/12/99 16.43 =I6 3/22/99 15.96 3/19/99 16.45 =I7 0.484 16.44 29.957 1045.641 3/29/99 15.86 3/26/99 15.69 =I8 DivTable set as a named range Date Rate 3/4/99 2.038 3/12/99 0.25 3/17/99 0.484 3/30/99 0.063 PriceTable set as a named range Date Price 3/3/99 15.84 3/4/99 16.02 3/5/99 16.24 3/8/99 16.23 3/9/99 16.16 3/10/99 16.34 3/11/99 16.36 3/12/99 15.94 3/15/99 16.4 3/16/99 16.41 3/17/99 16.44 3/18/99 16.58 3/19/99 16.45 3/22/99 15.96 3/23/99 15.63 3/24/99 15.75 3/25/99 15.82 3/26/99 15.69 3/29/99 15.86 3/30/99 15.63 |
#7
|
|||
|
|||
Yes, because of the way my table is setup, it is confusing.
You understood me correctly in saying we're looking for dates in Price/Div tables. My search range for each week is determined by A1 to A2, A2 to A3, etc. NOT A1 to C1, NOT A2 to C2, etc. (BTW, I only need to know the value from the Price Table IF a date from the Div Rate table falls within the lookup criteria.) So as an example, IF a date in the DivRate table occurs between A5 and A6 as it does (the date being 3/12) then I want to extract the info. A similar example occurs between A6 and A7 (the date being 3/17.) Again, Im looking at dates from column A (or C) that define a week. So just as A1 to A2, A2 to A3, etc. would be a valid search range so would C1 to C2, C2 to C3, etc. Either are representative of a valid search range because I want to look within a week. Since the date of 3/12 in the Div table falls between A5 and A6 in row 6, then I need to extract its rate (.25) from the DivRate table and its price (15.94) from the DivPrice table. Similarly since 3/17 occurs between A6 and A7 in row 7 then I need to extract its rate (.484) from the DivRate table and its price (16.44) from the DivPrice table. I guess Im not that adept in posting my thoughts; the confusing table layout doesnt help either but I really do have a rational for that characteristic!) Thanks again. Ive been struggling with this problem for a week and as I said before, I just dont seem to have the skill to apply what I gleaned from the forum to my situation. "Bob Phillips" wrote: AG, Clear some confusion in my mind (BTW thanks for the clearer format data). F2: What dates should that be comparing against the dates in the Price/Div Table. I got confused by F7, F8 A5/C5 and A6/6. I had assumed that we were looking to see if any date in Price/Div table was within the dates A2-C2, and so on. -- HTH RP (remove nothere from the email address if mailing direct) "AG" wrote in message ... Thanks for the reply. As to the prediction discrepancy, my fault as my original data was incorrect for the dividend date of 2/12/99; it should have been 3/17/99. Value Sheet Date Price Date Price Shares Rate Price Shares 2/8/99 16.3 2/5/99 16.28 1000 1000 2/16/99 15.94 2/12/99 15.86 2/22/99 16.07 2/19/99 15.8 3/1/99 15.82 2/26/99 15.85 3/8/99 16.23 3/5/99 16.24 3/15/99 16.4 3/12/99 16.43 .25 15.94 15.684 1015.684 3/22/99 15.96 3/19/99 16.45 .484 16.44 29.957 1045.641 3/29/99 15.86 3/26/99 15.69 I setup a corrected sheet as you interpreted my sheet to be (with the Div Price table in M2:M33 and the Div Rate inO2:P5, the formula does pickup the Div Rate for F7 on 3/12/99 but not for F8 for 3/17/99. BTW, if the formatting doesn't show the table clearly. Can I send you a copy? I also tried constructing a similar formula for the Div Price for column G but with no luck. For column F, I need to know the formula that would extract the dividend rate from the DivTable that may occur between the dates in cell A5 and A6 (or C5 and C6) would also be an applicable lookup criterion.) For column G, I need to know the formula that would extract the price from the PriceTable that may occur between the dates in cell A5 and A6 (or C5 and C6) would also be an applicable lookup criterion.) Any help would greatly appreciate. Thanks again. "Bob Phillips" wrote: This doesn't give the results that you predict, but it is as I read the description. Assuming PriceTable is M1:N21, and DivTable is O1:P21, then rate comes from =SUMPRODUCT(--(TRANSPOSE(ROW(INDIRECT($A3&":"&$C3)))=$O$2:$O$21) ,$P$2:$P$21) and price comes from =SUMPRODUCT(--(TRANSPOSE(ROW(INDIRECT(A3&":"&C3)))=$M$2:$M$21),$ N$2:$N$21) -- HTH RP (remove nothere from the email address if mailing direct) "AG" wrote in message ... I need formulas to calculate a dividend when it occurs between 2 dates. I've tried a few options & looked through the group but I don't have the skill to put the info I found to use. Thanks for any help. I have a sheet named "Value" as setup below. I have ranges for dividend rates and daily prices as below. What I need are the formulas to extract the Div Rate for column E and the Div Price for column F. So that I extract .25 for cell F3 and 15.94 for cell G3 and 0.484 for cell F8 and G8 when they occur. The lookup values in columns A & C may not always match the dates in the lookup ranges and finding the closest match that results using a standard lookup function won't always be correct. Value Sheet Monday Friday Current Div Added Total Date Price Date Price Shares Rate Price Shares Shares 2/8/99 16.3 2/5/99 16.28 1000 1000 2/16/99 15.94 2/12/99 15.86 =I2 .25 15.94 15.684 1015.684 2/22/99 16.07 2/19/99 15.8 =I3 3/1/99 15.82 2/26/99 15.85 =I4 3/8/99 16.23 3/5/99 16.24 =I5 3/15/99 16.4 3/12/99 16.43 =I6 3/22/99 15.96 3/19/99 16.45 =I7 0.484 16.44 29.957 1045.641 3/29/99 15.86 3/26/99 15.69 =I8 DivTable set as a named range Date Rate 3/4/99 2.038 3/12/99 0.25 3/17/99 0.484 3/30/99 0.063 PriceTable set as a named range Date Price 3/3/99 15.84 3/4/99 16.02 3/5/99 16.24 3/8/99 16.23 3/9/99 16.16 3/10/99 16.34 3/11/99 16.36 3/12/99 15.94 3/15/99 16.4 3/16/99 16.41 3/17/99 16.44 3/18/99 16.58 3/19/99 16.45 3/22/99 15.96 3/23/99 15.63 3/24/99 15.75 3/25/99 15.82 3/26/99 15.69 3/29/99 15.86 3/30/99 15.63 |
#8
|
|||
|
|||
I think I can get the rate okay, with
=SUMPRODUCT(--($A2<=$O$2:$O$21),--($A3=$O$2:$O$21),$P$2:$P$21) but price is more difficult, as there are multiple dates in the Prfice table that coorelate to the week of say 1st March to 8th March. My formula adds them all up. Which one should it choose, or maybe average? If average, try =IF(SUMPRODUCT(--($A2<=$M$2:$M$21),--($A3=$M$2:$M$21))=0,0,SUMPRODUCT(--($A 2<=$M$2:$M$21),--($A3=$M$2:$M$21),$N$2:$N$21)/SUMPRODUCT(--($A2<=$M$2:$M$21 ),--($A3=$M$2:$M$21))) -- HTH RP (remove nothere from the email address if mailing direct) "AG" wrote in message ... Yes, because of the way my table is setup, it is confusing. You understood me correctly in saying we're looking for dates in Price/Div tables. My search range for each week is determined by A1 to A2, A2 to A3, etc. NOT A1 to C1, NOT A2 to C2, etc. (BTW, I only need to know the value from the Price Table IF a date from the Div Rate table falls within the lookup criteria.) So as an example, IF a date in the DivRate table occurs between A5 and A6 as it does (the date being 3/12) then I want to extract the info. A similar example occurs between A6 and A7 (the date being 3/17.) Again, I'm looking at dates from column A (or C) that define a week. So just as A1 to A2, A2 to A3, etc. would be a valid search range so would C1 to C2, C2 to C3, etc. Either are representative of a valid search range because I want to look within a week. Since the date of 3/12 in the Div table falls between A5 and A6 in row 6, then I need to extract its rate (.25) from the DivRate table and its price (15.94) from the DivPrice table. Similarly since 3/17 occurs between A6 and A7 in row 7 then I need to extract its rate (.484) from the DivRate table and its price (16.44) from the DivPrice table. I guess I'm not that adept in posting my thoughts; the confusing table layout doesn't help either but I really do have a rational for that characteristic!) Thanks again. I've been struggling with this problem for a week and as I said before, I just don't seem to have the skill to apply what I gleaned from the forum to my situation. "Bob Phillips" wrote: AG, Clear some confusion in my mind (BTW thanks for the clearer format data). F2: What dates should that be comparing against the dates in the Price/Div Table. I got confused by F7, F8 A5/C5 and A6/6. I had assumed that we were looking to see if any date in Price/Div table was within the dates A2-C2, and so on. -- HTH RP (remove nothere from the email address if mailing direct) "AG" wrote in message ... Thanks for the reply. As to the prediction discrepancy, my fault as my original data was incorrect for the dividend date of 2/12/99; it should have been 3/17/99. Value Sheet Date Price Date Price Shares Rate Price Shares 2/8/99 16.3 2/5/99 16.28 1000 1000 2/16/99 15.94 2/12/99 15.86 2/22/99 16.07 2/19/99 15.8 3/1/99 15.82 2/26/99 15.85 3/8/99 16.23 3/5/99 16.24 3/15/99 16.4 3/12/99 16.43 .25 15.94 15.684 1015.684 3/22/99 15.96 3/19/99 16.45 .484 16.44 29.957 1045.641 3/29/99 15.86 3/26/99 15.69 I setup a corrected sheet as you interpreted my sheet to be (with the Div Price table in M2:M33 and the Div Rate inO2:P5, the formula does pickup the Div Rate for F7 on 3/12/99 but not for F8 for 3/17/99. BTW, if the formatting doesn't show the table clearly. Can I send you a copy? I also tried constructing a similar formula for the Div Price for column G but with no luck. For column F, I need to know the formula that would extract the dividend rate from the DivTable that may occur between the dates in cell A5 and A6 (or C5 and C6) would also be an applicable lookup criterion.) For column G, I need to know the formula that would extract the price from the PriceTable that may occur between the dates in cell A5 and A6 (or C5 and C6) would also be an applicable lookup criterion.) Any help would greatly appreciate. Thanks again. "Bob Phillips" wrote: This doesn't give the results that you predict, but it is as I read the description. Assuming PriceTable is M1:N21, and DivTable is O1:P21, then rate comes from =SUMPRODUCT(--(TRANSPOSE(ROW(INDIRECT($A3&":"&$C3)))=$O$2:$O$21) ,$P$2:$P$21) and price comes from =SUMPRODUCT(--(TRANSPOSE(ROW(INDIRECT(A3&":"&C3)))=$M$2:$M$21),$ N$2:$N$21) -- HTH RP (remove nothere from the email address if mailing direct) "AG" wrote in message ... I need formulas to calculate a dividend when it occurs between 2 dates. I've tried a few options & looked through the group but I don't have the skill to put the info I found to use. Thanks for any help. I have a sheet named "Value" as setup below. I have ranges for dividend rates and daily prices as below. What I need are the formulas to extract the Div Rate for column E and the Div Price for column F. So that I extract .25 for cell F3 and 15.94 for cell G3 and 0.484 for cell F8 and G8 when they occur. The lookup values in columns A & C may not always match the dates in the lookup ranges and finding the closest match that results using a standard lookup function won't always be correct. Value Sheet Monday Friday Current Div Added Total Date Price Date Price Shares Rate Price Shares Shares 2/8/99 16.3 2/5/99 16.28 1000 1000 2/16/99 15.94 2/12/99 15.86 =I2 .25 15.94 15.684 1015.684 2/22/99 16.07 2/19/99 15.8 =I3 3/1/99 15.82 2/26/99 15.85 =I4 3/8/99 16.23 3/5/99 16.24 =I5 3/15/99 16.4 3/12/99 16.43 =I6 3/22/99 15.96 3/19/99 16.45 =I7 0.484 16.44 29.957 1045.641 3/29/99 15.86 3/26/99 15.69 =I8 DivTable set as a named range Date Rate 3/4/99 2.038 3/12/99 0.25 3/17/99 0.484 3/30/99 0.063 PriceTable set as a named range Date Price 3/3/99 15.84 3/4/99 16.02 3/5/99 16.24 3/8/99 16.23 3/9/99 16.16 3/10/99 16.34 3/11/99 16.36 3/12/99 15.94 3/15/99 16.4 3/16/99 16.41 3/17/99 16.44 3/18/99 16.58 3/19/99 16.45 3/22/99 15.96 3/23/99 15.63 3/24/99 15.75 3/25/99 15.82 3/26/99 15.69 3/29/99 15.86 3/30/99 15.63 |
#9
|
|||
|
|||
Rate result works great!
I would certainly like to know why and/or how it works. I wouldn't think SUMPRODUCTcould be used as you did and I have no idea how the "--" works either. As to the price, I think this works: VLOOKUP(OFFSET(P$2,MATCH(F3,$P$2:$P$38,0)-1,-1,1,1),$M$2:$N$38,2,FALSE) Thanks for your assistance, it is greatly appreciated. "Bob Phillips" wrote: I think I can get the rate okay, with =SUMPRODUCT(--($A2<=$O$2:$O$21),--($A3=$O$2:$O$21),$P$2:$P$21) but price is more difficult, as there are multiple dates in the Prfice table that coorelate to the week of say 1st March to 8th March. My formula adds them all up. Which one should it choose, or maybe average? If average, try =IF(SUMPRODUCT(--($A2<=$M$2:$M$21),--($A3=$M$2:$M$21))=0,0,SUMPRODUCT(--($A 2<=$M$2:$M$21),--($A3=$M$2:$M$21),$N$2:$N$21)/SUMPRODUCT(--($A2<=$M$2:$M$21 ),--($A3=$M$2:$M$21))) -- HTH RP (remove nothere from the email address if mailing direct) "AG" wrote in message ... Yes, because of the way my table is setup, it is confusing. You understood me correctly in saying we're looking for dates in Price/Div tables. My search range for each week is determined by A1 to A2, A2 to A3, etc. NOT A1 to C1, NOT A2 to C2, etc. (BTW, I only need to know the value from the Price Table IF a date from the Div Rate table falls within the lookup criteria.) So as an example, IF a date in the DivRate table occurs between A5 and A6 as it does (the date being 3/12) then I want to extract the info. A similar example occurs between A6 and A7 (the date being 3/17.) Again, I'm looking at dates from column A (or C) that define a week. So just as A1 to A2, A2 to A3, etc. would be a valid search range so would C1 to C2, C2 to C3, etc. Either are representative of a valid search range because I want to look within a week. Since the date of 3/12 in the Div table falls between A5 and A6 in row 6, then I need to extract its rate (.25) from the DivRate table and its price (15.94) from the DivPrice table. Similarly since 3/17 occurs between A6 and A7 in row 7 then I need to extract its rate (.484) from the DivRate table and its price (16.44) from the DivPrice table. I guess I'm not that adept in posting my thoughts; the confusing table layout doesn't help either but I really do have a rational for that characteristic!) Thanks again. I've been struggling with this problem for a week and as I said before, I just don't seem to have the skill to apply what I gleaned from the forum to my situation. "Bob Phillips" wrote: AG, Clear some confusion in my mind (BTW thanks for the clearer format data). F2: What dates should that be comparing against the dates in the Price/Div Table. I got confused by F7, F8 A5/C5 and A6/6. I had assumed that we were looking to see if any date in Price/Div table was within the dates A2-C2, and so on. -- HTH RP (remove nothere from the email address if mailing direct) "AG" wrote in message ... Thanks for the reply. As to the prediction discrepancy, my fault as my original data was incorrect for the dividend date of 2/12/99; it should have been 3/17/99. Value Sheet Date Price Date Price Shares Rate Price Shares 2/8/99 16.3 2/5/99 16.28 1000 1000 2/16/99 15.94 2/12/99 15.86 2/22/99 16.07 2/19/99 15.8 3/1/99 15.82 2/26/99 15.85 3/8/99 16.23 3/5/99 16.24 3/15/99 16.4 3/12/99 16.43 .25 15.94 15.684 1015.684 3/22/99 15.96 3/19/99 16.45 .484 16.44 29.957 1045.641 3/29/99 15.86 3/26/99 15.69 I setup a corrected sheet as you interpreted my sheet to be (with the Div Price table in M2:M33 and the Div Rate inO2:P5, the formula does pickup the Div Rate for F7 on 3/12/99 but not for F8 for 3/17/99. BTW, if the formatting doesn't show the table clearly. Can I send you a copy? I also tried constructing a similar formula for the Div Price for column G but with no luck. For column F, I need to know the formula that would extract the dividend rate from the DivTable that may occur between the dates in cell A5 and A6 (or C5 and C6) would also be an applicable lookup criterion.) For column G, I need to know the formula that would extract the price from the PriceTable that may occur between the dates in cell A5 and A6 (or C5 and C6) would also be an applicable lookup criterion.) Any help would greatly appreciate. Thanks again. "Bob Phillips" wrote: This doesn't give the results that you predict, but it is as I read the description. Assuming PriceTable is M1:N21, and DivTable is O1:P21, then rate comes from =SUMPRODUCT(--(TRANSPOSE(ROW(INDIRECT($A3&":"&$C3)))=$O$2:$O$21) ,$P$2:$P$21) and price comes from =SUMPRODUCT(--(TRANSPOSE(ROW(INDIRECT(A3&":"&C3)))=$M$2:$M$21),$ N$2:$N$21) -- HTH RP (remove nothere from the email address if mailing direct) "AG" wrote in message ... I need formulas to calculate a dividend when it occurs between 2 dates. I've tried a few options & looked through the group but I don't have the skill to put the info I found to use. Thanks for any help. I have a sheet named "Value" as setup below. I have ranges for dividend rates and daily prices as below. What I need are the formulas to extract the Div Rate for column E and the Div Price for column F. So that I extract .25 for cell F3 and 15.94 for cell G3 and 0.484 for cell F8 and G8 when they occur. The lookup values in columns A & C may not always match the dates in the lookup ranges and finding the closest match that results using a standard lookup function won't always be correct. Value Sheet Monday Friday Current Div Added Total Date Price Date Price Shares Rate Price Shares Shares 2/8/99 16.3 2/5/99 16.28 1000 1000 2/16/99 15.94 2/12/99 15.86 =I2 .25 15.94 15.684 1015.684 2/22/99 16.07 2/19/99 15.8 =I3 3/1/99 15.82 2/26/99 15.85 =I4 3/8/99 16.23 3/5/99 16.24 =I5 3/15/99 16.4 3/12/99 16.43 =I6 3/22/99 15.96 3/19/99 16.45 =I7 0.484 16.44 29.957 1045.641 3/29/99 15.86 3/26/99 15.69 =I8 DivTable set as a named range Date Rate 3/4/99 2.038 3/12/99 0.25 3/17/99 0.484 3/30/99 0.063 PriceTable set as a named range Date Price 3/3/99 15.84 3/4/99 16.02 3/5/99 16.24 3/8/99 16.23 3/9/99 16.16 3/10/99 16.34 3/11/99 16.36 3/12/99 15.94 3/15/99 16.4 3/16/99 16.41 3/17/99 16.44 3/18/99 16.58 3/19/99 16.45 3/22/99 15.96 3/23/99 15.63 3/24/99 15.75 3/25/99 15.82 3/26/99 15.69 3/29/99 15.86 3/30/99 15.63 |
#10
|
|||
|
|||
Your columns don't line up with mine, what cell is this formula in and what
is F3? And are column M and P both part of the price table, they don't look to be. -- HTH RP (remove nothere from the email address if mailing direct) "AG" wrote in message ... Rate result works great! I would certainly like to know why and/or how it works. I wouldn't think SUMPRODUCTcould be used as you did and I have no idea how the "--" works either. As to the price, I think this works: VLOOKUP(OFFSET(P$2,MATCH(F3,$P$2:$P$38,0)-1,-1,1,1),$M$2:$N$38,2,FALSE) Thanks for your assistance, it is greatly appreciated. "Bob Phillips" wrote: I think I can get the rate okay, with =SUMPRODUCT(--($A2<=$O$2:$O$21),--($A3=$O$2:$O$21),$P$2:$P$21) but price is more difficult, as there are multiple dates in the Prfice table that coorelate to the week of say 1st March to 8th March. My formula adds them all up. Which one should it choose, or maybe average? If average, try =IF(SUMPRODUCT(--($A2<=$M$2:$M$21),--($A3=$M$2:$M$21))=0,0,SUMPRODUCT(--($A 2<=$M$2:$M$21),--($A3=$M$2:$M$21),$N$2:$N$21)/SUMPRODUCT(--($A2<=$M$2:$M$21 ),--($A3=$M$2:$M$21))) -- HTH RP (remove nothere from the email address if mailing direct) "AG" wrote in message ... Yes, because of the way my table is setup, it is confusing. You understood me correctly in saying we're looking for dates in Price/Div tables. My search range for each week is determined by A1 to A2, A2 to A3, etc. NOT A1 to C1, NOT A2 to C2, etc. (BTW, I only need to know the value from the Price Table IF a date from the Div Rate table falls within the lookup criteria.) So as an example, IF a date in the DivRate table occurs between A5 and A6 as it does (the date being 3/12) then I want to extract the info. A similar example occurs between A6 and A7 (the date being 3/17.) Again, I'm looking at dates from column A (or C) that define a week. So just as A1 to A2, A2 to A3, etc. would be a valid search range so would C1 to C2, C2 to C3, etc. Either are representative of a valid search range because I want to look within a week. Since the date of 3/12 in the Div table falls between A5 and A6 in row 6, then I need to extract its rate (.25) from the DivRate table and its price (15.94) from the DivPrice table. Similarly since 3/17 occurs between A6 and A7 in row 7 then I need to extract its rate (.484) from the DivRate table and its price (16.44) from the DivPrice table. I guess I'm not that adept in posting my thoughts; the confusing table layout doesn't help either but I really do have a rational for that characteristic!) Thanks again. I've been struggling with this problem for a week and as I said before, I just don't seem to have the skill to apply what I gleaned from the forum to my situation. "Bob Phillips" wrote: AG, Clear some confusion in my mind (BTW thanks for the clearer format data). F2: What dates should that be comparing against the dates in the Price/Div Table. I got confused by F7, F8 A5/C5 and A6/6. I had assumed that we were looking to see if any date in Price/Div table was within the dates A2-C2, and so on. -- HTH RP (remove nothere from the email address if mailing direct) "AG" wrote in message ... Thanks for the reply. As to the prediction discrepancy, my fault as my original data was incorrect for the dividend date of 2/12/99; it should have been 3/17/99. Value Sheet Date Price Date Price Shares Rate Price Shares 2/8/99 16.3 2/5/99 16.28 1000 1000 2/16/99 15.94 2/12/99 15.86 2/22/99 16.07 2/19/99 15.8 3/1/99 15.82 2/26/99 15.85 3/8/99 16.23 3/5/99 16.24 3/15/99 16.4 3/12/99 16.43 .25 15.94 15.684 1015.684 3/22/99 15.96 3/19/99 16.45 .484 16.44 29.957 1045.641 3/29/99 15.86 3/26/99 15.69 I setup a corrected sheet as you interpreted my sheet to be (with the Div Price table in M2:M33 and the Div Rate inO2:P5, the formula does pickup the Div Rate for F7 on 3/12/99 but not for F8 for 3/17/99. BTW, if the formatting doesn't show the table clearly. Can I send you a copy? I also tried constructing a similar formula for the Div Price for column G but with no luck. For column F, I need to know the formula that would extract the dividend rate from the DivTable that may occur between the dates in cell A5 and A6 (or C5 and C6) would also be an applicable lookup criterion.) For column G, I need to know the formula that would extract the price from the PriceTable that may occur between the dates in cell A5 and A6 (or C5 and C6) would also be an applicable lookup criterion.) Any help would greatly appreciate. Thanks again. "Bob Phillips" wrote: This doesn't give the results that you predict, but it is as I read the description. Assuming PriceTable is M1:N21, and DivTable is O1:P21, then rate comes from =SUMPRODUCT(--(TRANSPOSE(ROW(INDIRECT($A3&":"&$C3)))=$O$2:$O$21) ,$P$2:$P$21) and price comes from =SUMPRODUCT(--(TRANSPOSE(ROW(INDIRECT(A3&":"&C3)))=$M$2:$M$21),$ N$2:$N$21) -- HTH RP (remove nothere from the email address if mailing direct) "AG" wrote in message ... I need formulas to calculate a dividend when it occurs between 2 dates. I've tried a few options & looked through the group but I don't have the skill to put the info I found to use. Thanks for any help. I have a sheet named "Value" as setup below. I have ranges for dividend rates and daily prices as below. What I need are the formulas to extract the Div Rate for column E and the Div Price for column F. So that I extract .25 for cell F3 and 15.94 for cell G3 and 0.484 for cell F8 and G8 when they occur. The lookup values in columns A & C may not always match the dates in the lookup ranges and finding the closest match that results using a standard lookup function won't always be correct. Value Sheet Monday Friday Current Div Added Total Date Price Date Price Shares Rate Price Shares Shares 2/8/99 16.3 2/5/99 16.28 1000 1000 2/16/99 15.94 2/12/99 15.86 =I2 .25 15.94 15.684 1015.684 2/22/99 16.07 2/19/99 15.8 =I3 3/1/99 15.82 2/26/99 15.85 =I4 3/8/99 16.23 3/5/99 16.24 =I5 3/15/99 16.4 3/12/99 16.43 =I6 3/22/99 15.96 3/19/99 16.45 =I7 0.484 16.44 29.957 1045.641 3/29/99 15.86 3/26/99 15.69 =I8 DivTable set as a named range Date Rate 3/4/99 2.038 3/12/99 0.25 3/17/99 0.484 3/30/99 0.063 PriceTable set as a named range Date Price 3/3/99 15.84 3/4/99 16.02 3/5/99 16.24 3/8/99 16.23 3/9/99 16.16 3/10/99 16.34 3/11/99 16.36 3/12/99 15.94 3/15/99 16.4 3/16/99 16.41 3/17/99 16.44 3/18/99 16.58 3/19/99 16.45 3/22/99 15.96 3/23/99 15.63 3/24/99 15.75 3/25/99 15.82 3/26/99 15.69 3/29/99 15.86 3/30/99 15.63 |
#11
|
|||
|
|||
If you post an address, I could send you my sheet if necessary as the
formatting is difficult within the context of the reply box. The formula is entered in G2 & copied down. My sheet is setup as follows: Column A is a Monday date Column B is the price on Mondays date Column C is a Friday date Column D is the price on Fridays date Column E is the current shares column Column F is the Div Rate column for which you supplied the formula Column G is the Div Price column where I entered the formula starting in row 2 & copied down. Column H is new shares received using the formula (E3*F3)/G3 Column I is the new total of shares using the formula E3+H3 Columns J:L are not used Columns M:N represent my PriceTable Columns O:P represent my DivTable So in words the formula (entered in column G) works as follows: The OFFSET(P$2,MATCH(F3,$P$2:$P$38,0)-1,-1,1,1) part of the formula performs a left lookup to find the date from my DivTable, columns O:P that matches the value from column F (your calculated value from using SUMPRODUCT) and then the VLOOKUP part finds the price from my PriceTable, columns M:N that is an exact match for the date found by the left lookup. I hope this is clear. Would you mind explaining your SUMPRODUCT formula? Thanks again! "Bob Phillips" wrote: Your columns don't line up with mine, what cell is this formula in and what is F3? And are column M and P both part of the price table, they don't look to be. -- HTH RP (remove nothere from the email address if mailing direct) "AG" wrote in message ... Rate result works great! I would certainly like to know why and/or how it works. I wouldn't think SUMPRODUCTcould be used as you did and I have no idea how the "--" works either. As to the price, I think this works: VLOOKUP(OFFSET(P$2,MATCH(F3,$P$2:$P$38,0)-1,-1,1,1),$M$2:$N$38,2,FALSE) Thanks for your assistance, it is greatly appreciated. "Bob Phillips" wrote: I think I can get the rate okay, with =SUMPRODUCT(--($A2<=$O$2:$O$21),--($A3=$O$2:$O$21),$P$2:$P$21) but price is more difficult, as there are multiple dates in the Prfice table that coorelate to the week of say 1st March to 8th March. My formula adds them all up. Which one should it choose, or maybe average? If average, try =IF(SUMPRODUCT(--($A2<=$M$2:$M$21),--($A3=$M$2:$M$21))=0,0,SUMPRODUCT(--($A 2<=$M$2:$M$21),--($A3=$M$2:$M$21),$N$2:$N$21)/SUMPRODUCT(--($A2<=$M$2:$M$21 ),--($A3=$M$2:$M$21))) -- HTH RP (remove nothere from the email address if mailing direct) "AG" wrote in message ... Yes, because of the way my table is setup, it is confusing. You understood me correctly in saying we're looking for dates in Price/Div tables. My search range for each week is determined by A1 to A2, A2 to A3, etc. NOT A1 to C1, NOT A2 to C2, etc. (BTW, I only need to know the value from the Price Table IF a date from the Div Rate table falls within the lookup criteria.) So as an example, IF a date in the DivRate table occurs between A5 and A6 as it does (the date being 3/12) then I want to extract the info. A similar example occurs between A6 and A7 (the date being 3/17.) Again, I'm looking at dates from column A (or C) that define a week. So just as A1 to A2, A2 to A3, etc. would be a valid search range so would C1 to C2, C2 to C3, etc. Either are representative of a valid search range because I want to look within a week. Since the date of 3/12 in the Div table falls between A5 and A6 in row 6, then I need to extract its rate (.25) from the DivRate table and its price (15.94) from the DivPrice table. Similarly since 3/17 occurs between A6 and A7 in row 7 then I need to extract its rate (.484) from the DivRate table and its price (16.44) from the DivPrice table. I guess I'm not that adept in posting my thoughts; the confusing table layout doesn't help either but I really do have a rational for that characteristic!) Thanks again. I've been struggling with this problem for a week and as I said before, I just don't seem to have the skill to apply what I gleaned from the forum to my situation. "Bob Phillips" wrote: AG, Clear some confusion in my mind (BTW thanks for the clearer format data). F2: What dates should that be comparing against the dates in the Price/Div Table. I got confused by F7, F8 A5/C5 and A6/6. I had assumed that we were looking to see if any date in Price/Div table was within the dates A2-C2, and so on. -- HTH RP (remove nothere from the email address if mailing direct) "AG" wrote in message ... Thanks for the reply. As to the prediction discrepancy, my fault as my original data was incorrect for the dividend date of 2/12/99; it should have been 3/17/99. Value Sheet Date Price Date Price Shares Rate Price Shares 2/8/99 16.3 2/5/99 16.28 1000 1000 2/16/99 15.94 2/12/99 15.86 2/22/99 16.07 2/19/99 15.8 3/1/99 15.82 2/26/99 15.85 3/8/99 16.23 3/5/99 16.24 3/15/99 16.4 3/12/99 16.43 .25 15.94 15.684 1015.684 3/22/99 15.96 3/19/99 16.45 .484 16.44 29.957 1045.641 3/29/99 15.86 3/26/99 15.69 I setup a corrected sheet as you interpreted my sheet to be (with the Div Price table in M2:M33 and the Div Rate inO2:P5, the formula does pickup the Div Rate for F7 on 3/12/99 but not for F8 for 3/17/99. BTW, if the formatting doesn't show the table clearly. Can I send you a copy? I also tried constructing a similar formula for the Div Price for column G but with no luck. For column F, I need to know the formula that would extract the dividend rate from the DivTable that may occur between the dates in cell A5 and A6 (or C5 and C6) would also be an applicable lookup criterion.) For column G, I need to know the formula that would extract the price from the PriceTable that may occur between the dates in cell A5 and A6 (or C5 and C6) would also be an applicable lookup criterion.) Any help would greatly appreciate. Thanks again. "Bob Phillips" wrote: This doesn't give the results that you predict, but it is as I read the description. Assuming PriceTable is M1:N21, and DivTable is O1:P21, then rate comes from =SUMPRODUCT(--(TRANSPOSE(ROW(INDIRECT($A3&":"&$C3)))=$O$2:$O$21) ,$P$2:$P$21) and price comes from =SUMPRODUCT(--(TRANSPOSE(ROW(INDIRECT(A3&":"&C3)))=$M$2:$M$21),$ N$2:$N$21) -- HTH RP (remove nothere from the email address if mailing direct) "AG" wrote in message ... I need formulas to calculate a dividend when it occurs between 2 dates. I've tried a few options & looked through the group but I don't have the skill to put the info I found to use. Thanks for any help. I have a sheet named "Value" as setup below. I have ranges for dividend rates and daily prices as below. What I need are the formulas to extract the Div Rate for column E and the Div Price for column F. So that I extract .25 for cell F3 and 15.94 for cell G3 and 0.484 for cell F8 and G8 when they occur. The lookup values in columns A & C may not always match the dates in the lookup ranges and finding the closest match that results using a standard lookup function won't always be correct. Value Sheet Monday Friday Current Div Added Total Date Price Date Price Shares Rate Price Shares Shares 2/8/99 16.3 2/5/99 16.28 1000 1000 2/16/99 15.94 2/12/99 15.86 =I2 .25 15.94 15.684 1015.684 2/22/99 16.07 2/19/99 15.8 =I3 3/1/99 15.82 2/26/99 15.85 =I4 3/8/99 16.23 3/5/99 16.24 =I5 3/15/99 16.4 3/12/99 16.43 =I6 3/22/99 15.96 3/19/99 16.45 =I7 0.484 16.44 29.957 1045.641 3/29/99 15.86 3/26/99 15.69 =I8 DivTable set as a named range Date Rate 3/4/99 2.038 3/12/99 0.25 3/17/99 0.484 3/30/99 0.063 PriceTable set as a named range Date Price 3/3/99 15.84 3/4/99 16.02 3/5/99 16.24 3/8/99 16.23 3/9/99 16.16 3/10/99 16.34 3/11/99 16.36 3/12/99 15.94 3/15/99 16.4 3/16/99 16.41 |
#12
|
|||
|
|||
Please do AG. The email address is bob dot phillips at tiscali dot co dot
uk - do the obvious. -- HTH RP (remove nothere from the email address if mailing direct) "AG" wrote in message ... If you post an address, I could send you my sheet if necessary as the formatting is difficult within the context of the reply box. The formula is entered in G2 & copied down. My sheet is setup as follows: Column A is a Monday date Column B is the price on Monday's date Column C is a Friday date Column D is the price on Friday's date Column E is the current shares column Column F is the Div Rate column for which you supplied the formula Column G is the Div Price column where I entered the formula starting in row 2 & copied down. Column H is new shares received using the formula (E3*F3)/G3 Column I is the new total of shares using the formula E3+H3 Columns J:L are not used Columns M:N represent my PriceTable Columns O:P represent my DivTable So in words the formula (entered in column G) works as follows: The "OFFSET(P$2,MATCH(F3,$P$2:$P$38,0)-1,-1,1,1)" part of the formula performs a left lookup to find the date from my DivTable, "columns O:P" that matches the value from column F (your calculated value from using SUMPRODUCT) and then the VLOOKUP part finds the price from my PriceTable, "columns M:N" that is an exact match for the date found by the left lookup. I hope this is clear. Would you mind explaining your SUMPRODUCT formula? Thanks again! "Bob Phillips" wrote: Your columns don't line up with mine, what cell is this formula in and what is F3? And are column M and P both part of the price table, they don't look to be. -- HTH RP (remove nothere from the email address if mailing direct) "AG" wrote in message ... Rate result works great! I would certainly like to know why and/or how it works. I wouldn't think SUMPRODUCTcould be used as you did and I have no idea how the "--" works either. As to the price, I think this works: VLOOKUP(OFFSET(P$2,MATCH(F3,$P$2:$P$38,0)-1,-1,1,1),$M$2:$N$38,2,FALSE) Thanks for your assistance, it is greatly appreciated. "Bob Phillips" wrote: I think I can get the rate okay, with =SUMPRODUCT(--($A2<=$O$2:$O$21),--($A3=$O$2:$O$21),$P$2:$P$21) but price is more difficult, as there are multiple dates in the Prfice table that coorelate to the week of say 1st March to 8th March. My formula adds them all up. Which one should it choose, or maybe average? If average, try =IF(SUMPRODUCT(--($A2<=$M$2:$M$21),--($A3=$M$2:$M$21))=0,0,SUMPRODUCT(--($A 2<=$M$2:$M$21),--($A3=$M$2:$M$21),$N$2:$N$21)/SUMPRODUCT(--($A2<=$M$2:$M$21 ),--($A3=$M$2:$M$21))) -- HTH RP (remove nothere from the email address if mailing direct) "AG" wrote in message ... Yes, because of the way my table is setup, it is confusing. You understood me correctly in saying we're looking for dates in Price/Div tables. My search range for each week is determined by A1 to A2, A2 to A3, etc. NOT A1 to C1, NOT A2 to C2, etc. (BTW, I only need to know the value from the Price Table IF a date from the Div Rate table falls within the lookup criteria.) So as an example, IF a date in the DivRate table occurs between A5 and A6 as it does (the date being 3/12) then I want to extract the info. A similar example occurs between A6 and A7 (the date being 3/17.) Again, I'm looking at dates from column A (or C) that define a week. So just as A1 to A2, A2 to A3, etc. would be a valid search range so would C1 to C2, C2 to C3, etc. Either are representative of a valid search range because I want to look within a week. Since the date of 3/12 in the Div table falls between A5 and A6 in row 6, then I need to extract its rate (.25) from the DivRate table and its price (15.94) from the DivPrice table. Similarly since 3/17 occurs between A6 and A7 in row 7 then I need to extract its rate (.484) from the DivRate table and its price (16.44) from the DivPrice table. I guess I'm not that adept in posting my thoughts; the confusing table layout doesn't help either but I really do have a rational for that characteristic!) Thanks again. I've been struggling with this problem for a week and as I said before, I just don't seem to have the skill to apply what I gleaned from the forum to my situation. "Bob Phillips" wrote: AG, Clear some confusion in my mind (BTW thanks for the clearer format data). F2: What dates should that be comparing against the dates in the Price/Div Table. I got confused by F7, F8 A5/C5 and A6/6. I had assumed that we were looking to see if any date in Price/Div table was within the dates A2-C2, and so on. -- HTH RP (remove nothere from the email address if mailing direct) "AG" wrote in message ... Thanks for the reply. As to the prediction discrepancy, my fault as my original data was incorrect for the dividend date of 2/12/99; it should have been 3/17/99. Value Sheet Date Price Date Price Shares Rate Price Shares 2/8/99 16.3 2/5/99 16.28 1000 1000 2/16/99 15.94 2/12/99 15.86 2/22/99 16.07 2/19/99 15.8 3/1/99 15.82 2/26/99 15.85 3/8/99 16.23 3/5/99 16.24 3/15/99 16.4 3/12/99 16.43 .25 15.94 15.684 1015.684 3/22/99 15.96 3/19/99 16.45 .484 16.44 29.957 1045.641 3/29/99 15.86 3/26/99 15.69 I setup a corrected sheet as you interpreted my sheet to be (with the Div Price table in M2:M33 and the Div Rate inO2:P5, the formula does pickup the Div Rate for F7 on 3/12/99 but not for F8 for 3/17/99. BTW, if the formatting doesn't show the table clearly. Can I send you a copy? I also tried constructing a similar formula for the Div Price for column G but with no luck. For column F, I need to know the formula that would extract the dividend rate from the DivTable that may occur between the dates in cell A5 and A6 (or C5 and C6) would also be an applicable lookup criterion.) For column G, I need to know the formula that would extract the price from the PriceTable that may occur between the dates in cell A5 and A6 (or C5 and C6) would also be an applicable lookup criterion.) Any help would greatly appreciate. Thanks again. "Bob Phillips" wrote: This doesn't give the results that you predict, but it is as I read the description. Assuming PriceTable is M1:N21, and DivTable is O1:P21, then rate comes from =SUMPRODUCT(--(TRANSPOSE(ROW(INDIRECT($A3&":"&$C3)))=$O$2:$O$21) ,$P$2:$P$21) and price comes from =SUMPRODUCT(--(TRANSPOSE(ROW(INDIRECT(A3&":"&C3)))=$M$2:$M$21),$ N$2:$N$21) -- HTH RP (remove nothere from the email address if mailing direct) "AG" wrote in message ... I need formulas to calculate a dividend when it occurs between 2 dates. I've tried a few options & looked through the group but I don't have the skill to put the info I found to use. Thanks for any help. I have a sheet named "Value" as setup below. I have ranges for dividend rates and daily prices as below. What I need are the formulas to extract the Div Rate for column E and the Div Price for column F. So that I extract .25 for cell F3 and 15.94 for cell G3 and 0.484 for cell F8 and G8 when they occur. The lookup values in columns A & C may not always match the dates in the lookup ranges and finding the closest match that results using a standard lookup function won't always be correct. Value Sheet Monday Friday Current Div Added Total Date Price Date Price Shares Rate Price Shares Shares 2/8/99 16.3 2/5/99 16.28 1000 1000 2/16/99 15.94 2/12/99 15.86 =I2 .25 15.94 15.684 1015.684 2/22/99 16.07 2/19/99 15.8 =I3 3/1/99 15.82 2/26/99 15.85 =I4 3/8/99 16.23 3/5/99 16.24 =I5 3/15/99 16.4 3/12/99 16.43 =I6 3/22/99 15.96 3/19/99 16.45 =I7 0.484 16.44 29.957 1045.641 3/29/99 15.86 3/26/99 15.69 =I8 DivTable set as a named range Date Rate 3/4/99 2.038 3/12/99 0.25 3/17/99 0.484 3/30/99 0.063 PriceTable set as a named range Date Price 3/3/99 15.84 3/4/99 16.02 3/5/99 16.24 3/8/99 16.23 3/9/99 16.16 3/10/99 16.34 3/11/99 16.36 3/12/99 15.94 3/15/99 16.4 3/16/99 16.41 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Default Dates | Excel Discussion (Misc queries) | |||
Using dates for x-axis values as string instead of creating a scale | Charts and Charting in Excel | |||
How do I get Excell to sort dates that range from 1800 to 1900's | New Users to Excel | |||
Formating Dates for production schedule | Excel Discussion (Misc queries) | |||
due dates | New Users to Excel |