Generate lagged dummy variables

[tianyi86]

Please open the attached sample dataset and I will explain what I need help on.

I need to generate a series of variables that model for events that occurred in previous days. If you look at the spreadsheet, you will see that on row 10, U01 = 1. This occurred on 1/13/2009. I need help to generate the values for 1 Day Lag U01 - 15 Day Lag U01.

This means "1 Day Lag U01" = 1 for each row if in the row below it, U01 = 1. "2 Day Lag U01" = 1 for each row if 2 rows below it, U01 = 1. I manually entered in what this should look like in column AJ to AQ (it is only for 8 days though).

If anyone can somehow create a formula to do this I would really appreciate it. I need to do this lag model for all the binary var between C to AI on a 15 day period for 445 companies, so an explanation of how the formula works would be great.

If possible, can the formula end and restart for each company so row 1996 doesn't take on the value of row 1997, which is a different company. If this is too difficult, don't worry about it as I can just manually modify the last few rows of each company.

Thanks
Kevin