I need formulas to calculate a dividend when it occurs between 2 dates.
Ive tried a few options & looked through the group but I dont 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 wont 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

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

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

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

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

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

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

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

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

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

If you post an address, I could send you my sheet if necessary as the
formatting is difficult within the context of the reply box.

The formula is entered in G2 & copied down.

My sheet is setup as follows:

Column A is a Monday date
Column B is the price on Mondays date
Column C is a Friday date
Column D is the price on Fridays date
Column E is the current shares column
Column F is the Div Rate column for which you supplied the formula
Column G is the Div Price column where I entered the formula starting in row
2 & copied down.
Column H is new shares received using the formula (E3*F3)/G3
Column I is the new total of shares using the formula E3+H3
Columns J:L are not used
Columns M:N represent my PriceTable
Columns O:P represent my DivTable

So in words the formula (entered in column G) works as follows:

The €œOFFSET(P$2,MATCH(F3,$P$2:$P$38,0)-1,-1,1,1)€� part of the formula
performs a left lookup to find the date from my DivTable, €œcolumns O:P€� that
matches the value from column F (your calculated value from using SUMPRODUCT)
and then the VLOOKUP part finds the price from my PriceTable, €œcolumns M:N€�
that is an exact match for the date found by the left lookup.

I hope this is clear.

Would you mind explaining your SUMPRODUCT formula?

Thanks again!


"Bob Phillips" wrote:

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

Please do AG. The email address is bob dot phillips at tiscali dot co dot
uk - do the obvious.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"AG" wrote in message
...
If you post an address, I could send you my sheet if necessary as the
formatting is difficult within the context of the reply box.

The formula is entered in G2 & copied down.

My sheet is setup as follows:

Column A is a Monday date
Column B is the price on Monday's date
Column C is a Friday date
Column D is the price on Friday's date
Column E is the current shares column
Column F is the Div Rate column for which you supplied the formula
Column G is the Div Price column where I entered the formula starting in
row
2 & copied down.
Column H is new shares received using the formula (E3*F3)/G3
Column I is the new total of shares using the formula E3+H3
Columns J:L are not used
Columns M:N represent my PriceTable
Columns O:P represent my DivTable

So in words the formula (entered in column G) works as follows:

The "OFFSET(P$2,MATCH(F3,$P$2:$P$38,0)-1,-1,1,1)" part of the formula
performs a left lookup to find the date from my DivTable, "columns O:P"
that
matches the value from column F (your calculated value from using
SUMPRODUCT)
and then the VLOOKUP part finds the price from my PriceTable, "columns
M:N"
that is an exact match for the date found by the left lookup.

I hope this is clear.

Would you mind explaining your SUMPRODUCT formula?

Thanks again!


"Bob Phillips" wrote:

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