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
|