multiple value lookups

[Max98Perez]

please help a desperate financial analyst out! i have a data table with
three columns: date, num of shares, and dividend per share. i want to create
a simple calculator that performs a sumproduct of num_shares times
dividend_per_share for a specified date range. the idea is to have an inputs
section where you type in the beginning date and the end date and excel
performs a sumproduct for the date range specified. excel's built in lookup
formulas are not helpful b/c in this model excel needs to reference all dates
within the specified date range and write the corresponding cash flows in an
output sheet. can anyone help?

Thanks!

 

[T. Valko]

Try this...

A1:A100 = dates
B1:B100 = number of shares
C1:C100 = dividend

E1 = start date
F1 = end date

=SUMPRODUCT(--(A1:A100>=E1),--(A1:A100<=F1),B1:B100,C1:C100)

 

[Max98Perez]

Thanks Valko,

What if I want to instruct excel to output in a separate worksheet a column
with the dates in the range and the corresponding dividends in that date
range?

Example of data table (semi colon denotes new column):

Date ; Dividends Per Share

6/01/03 ; 1.00
7/01/03 ; 1.25
8/01/03 ; 1.30
9/01/03 ; 1.32
10/01/03 ; 1.30

beg date = 6/1/03
end date = 9/1/03

i would like excel to output the following in a new worksheet:

6/1/03 ; 1.00
7/1/03 ; 1.25
8/1/03 ; 1.30
9/1/03 ; 1.32

can you give me a function or set of instructions that tells excel to
perform this computation?

thanks,

Max

 

[T. Valko]

Assuming the dates are all unique as is demonstrated in your sample data.

Dates = Sheet1!A2:A10
Div = Sheet1!B2:B10
Start = Sheet2!A2
End = Sheet2!B2

Enter this array formula** on Sheet2 C2:

=IF(ROWS(C$2:C2)<=SUM((dates>=start)*(dates<=end)),SMALL(IF((dates>=start)*(dates<=end),date),ROWS(C$2:C2)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Enter this formula on Sheet2 D2:

=IF(C2="","",SUMIF(dates,C2,div))

Select both C2 and D2 and copy down until you get blanks.

Format the C2:Cn as DATE

 

[Max98Perez]

Valko

Thanks again for helping me out! Quick question: for some reason the formula
is not working and I think it may have to do with a typo in the fomula (or so
I think!) There is a reference to "date" without an "s" in the SMALL
section of the array function and I honestly don't know the purpose of this
term. Could you double check the array function and let me know if there are
any typos?

Thanks!!!

Max

 

[T. Valko]

for some reason the formula is not working and

I think it may have to do with a typo

Ahh, yes, you are correct. There is a typo with a missing "s".

I honestly don't know the purpose of this term.

In the formulas, "dates", "div", "start" and "end" are just placeholders for
your actual range references. Some folks think formulas written this way are
easier to read. Personally, I prefer to use the actual ranges references.

Just replace those placeholder terms with your actual range references and
don't forget to array enter the "long" formula!

 

[Max98Perez]

Thank you my friend!

Best regards,

Max

Ahh, yes, you are correct. There is a typo with a missing "s".


In the formulas, "dates", "div", "start" and "end" are just placeholders for
your actual range references. Some folks think formulas written this way are
easier to read. Personally, I prefer to use the actual ranges references.

Just replace those placeholder terms with your actual range references and
don't forget to array enter the "long" formula!

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thank you my friend!

Best regards,

Max