needing help with cell references

The attached worksheet contains two tables - 1 and 2.

The first table contains a history of the Ex-Dividend Dates - or Ex-Dates - of a dividend stock. The second table is a daily price history of the stock - listing the Stock Trade Date, Closing Price, Open Price, High Price, Low Price, and Volume in reverse chronological order from top to bottom.

The stock price typically drops by roughly the amount of the dividend on the Ex-Date because the intrinsic value of the company has theoretically decreased by the amount of the dividend paid out. The formula I need in column S produces the number of Stock Market Trading Days it takes for the stock price to recover from the drop in price on the Ex-Date. In column U, I need the number of Days on the Calendar that it takes for the stock price to recover from the drop in price on the Ex-Date. See the Thick Box Borders.

Doing so requires the following.

First, the Ex-Date listed in column D must be matched to the same date in column K. The price that the stock must recover from is the Close Price on the day BEFORE the Ex-Date, so that is the Price in column L on the next row below the Ex-Date.

Next, that price must be compared to the Low Prices in column O to determine the next Low Price that meets or exceeds that price. Once identified, the number of rows must be counted in order to calculate column S, and the difference in the date values in column K must be calculated in order to fill in column U.

To confirm the formula, compare the values in column S to the manual calculation I did on this one stock in column Z, and likewise compare column U to column AB. See the values marked in red on the price history table.

Thank so much for your help!

Try in S7:

Please Login or Register  to view this content.

U7:

Please Login or Register  to view this content.

DJunqueira - could you please take another look?

After playing with the formula you provided, I realized some of the answers are incorrect because there was a flaw in the way I posed the question originally. Following, I am reposting the original question with edits in ALL CAPS to clear up the adjustment to the formula I need. I would appreciate it if you could make the adjustment, as this gets beyond my skill level.

Please refer to the workbook you posted as your solution in your response.

The first table contains a history of the Ex-Dividend Dates - or Ex-Dates - of a dividend stock. The second table is a daily price history of the stock - listing the Stock Trade Date, Closing Price, Open Price, High Price, Low Price, and Volume in reverse chronological order from top to bottom.

The stock price typically drops by roughly the amount of the dividend on the Ex-Date because the intrinsic value of the company has theoretically decreased by the amount of the dividend paid out. The formula I need in column S produces the number of Stock Market Trading Days it takes for the stock price to recover from the drop in price on the Ex-Date. In column U, I need the number of Days on the Calendar that it takes for the stock price to recover from the drop in price on the Ex-Date. See the Thick Box Borders.

Doing so requires the following.

First, the Ex-Date listed in column D must be matched to the same date in column K. The price that the stock must recover from is the Close Price on the day BEFORE the Ex-Date, so that is the Price in column L on the next row below the Ex-Date.

Next, that price must be compared to the SUBSEQUENT Low Prices in column O – BEGINNING WITH THE EX-DATE - to determine the next Low Price that meets or exceeds that price. Once identified, the number of rows must be counted in order to calculate column S (AND AS A RESULT, ONE IS THE LOWEST POSSIBLE VALUE), and the difference in the date values in column K must be calculated in order to fill in column U.

THE PART I DID NOT EXPLAIN WAS THAT THE STOCK CANNOT BE SOLD ON THE SAME DATE IT IS PURCHASED, AND THE FORMULA IS TESTING THE CLOSING PRICE ON THE DAY BEFORE THE EX-DATE ON THE VERY SAME DAY AND GIVING A RESULT OF ZERO. IT DOES NOT WORK THAT WAY. HOPE THIS HELPS.

To confirm the formula, compare the values in column S to the manual calculation I did on this one stock in column Z, and likewise compare column U to column AB. See the values marked in red on the price history table.

Thank so much for your help!