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
|