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
|