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
|