CODEWITHSUNDEEP
exceldateexcel-formulaworksheet-functionarray-formulas
user3333198
user3333198

Reputation: 105

INDEX/MATCH for closest value to a certain date

In sheet "Dividends" I have a table with dividends sorted by daily dates. E.g, columns A and B contain the following entries:

6/14/2015    
6/13/2015    
6/12/2015    0.045
6/11/2015
6/10/2015

This means that the stock paid a dividend of 0.045 on 6/12/2015.

In another sheet "AdjClose", I have a table with weekly dates and stock prices, e.g.

6/15/2015   1.23
6/8/2015    1.24
6/1/2015    1.06
5/30/2015   1.10
5/23/2015   1.22
5/16/2015   1.20

I would now compute the yield, where I divide the dividend by the stock price that is closest to the date of the dividend payout, but smaller than that date.

The result should be: 

0.045/1.24

How could I do this? Many thanks for any input.

Upvotes: 0

Views: 2515

Answers (2)

ZygD
ZygD

Reputation: 24366

Not the best looking one, but an option:

{=B3/INDEX(AdjClose!$B$1:$B$6,MATCH(MAX((AdjClose!$A$1:$A$6<=Dividends!A3)*AdjClose!$A$1:$A$6),AdjClose!$A$1:$A$6,0))}

Option #2 - much better, as there are no repeating ranges:

{=B3/INDEX(AdjClose!$B$1:$B$6,MATCH(1,--(AdjClose!$A$1:$A$6<=Dividends!A3),0))}

Upvotes: 1

gudal
gudal

Reputation: 337

Following 4 named ranges for simplicity in code: "Dividends": DividendDates (column A); DividendsPaid (column B) "AdjClose": StockDate (column A); StockPrice (column B)

try (in column C in "Dividends":

{=INDEX(StockPrice;MATCH(MAX(IF((StockDates<=A1);StockDates));StockDates;0))}

Assuming that the dividend date for which you want to find the adjusted stock price is in cell A1.

And copy down for each dividend date. This will give you the stock price of the day closest to, but before, you dividend date (or on the date if it is the same). Then just devide by your dividend for that day (either in the formula I wrote, or in a separate column. Always good to show your calculations steps, so it is easier to follow.

And remember to press CTRL + SHIFT + ENTER when you enter the formula, not just ENTER, as it is an array formula.

EDIT: Also, you need to change the ; to , if that is the formula separator of your language.

Upvotes: 1

Related Questions