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
|