How to count back 4 weeks, skipping blank cells

**Lars53** · 02-07-2014, 06:03 PM

I am using excel to analyze stock changes. Yahoo spits out a column with dates and another with closing prices but occasionally inserts a row showing a dividend, something like this:
Date Closing price
12/30/13 120
12/23/13 118
12/16/13 119
12/9/13 117
12/2/13 115
11/25/13 116
11/22/13 dividend 0.23
11/18/25 116
11/11/13 115

In the spreadsheet, column A is date and Column E is closing price, such that the cell in column E where the dividend is listed is blank.

The problem- as I update data each week, the dividend row moves. How can I consistently figure price changes for 4 weeks and 13 weeks without getting messed up by the dividend row? Is there to auto delete the row or make the formula count down 4 cells in the price column, while skipping any cells without data?

I am attaching a sample worksheet
Thanks

**daffodil11** · 02-07-2014, 06:16 PM

What's the actual operation you are trying to perform?

Assuming the data lined up perfectly normal, what formula would you use to do what you intend to do?

You can either use arrays with criteria that negate the blank rows, or you could use a simple procedure to delete the rows.

**tngengineer** · 02-07-2014, 06:28 PM

right click anywhere in the open price column (Column B in your example), choose sort/ largest to smallest, and the dividend lines move to the top of the table. Delete them and sort again, this time by right clicking any of the date cells, select the order and your table will be as you want it without the dividend lines. Or are you looking for something more automated?