Macro to create a lagged variable in microsofot excel Hello, I would like to ins

Question

Macro to create a lagged variable in microsofot excel

Hello, I would like to insert a lagged variable in my dataset. I have got more than 6 millions cases and so I am looking for macros which can create the lagged variable for me automatically.

My data is sorted first according to company ID in an descending order and then according to year in a descending order. For every firm year's cash flow, I would like to find out its previous year's cash flow. Take Company AT9010000001 for instance. Its cash flow in 2010 was 1200 and the lagged variable will be the previous year's cash flow (that is, the company's cash flows in 2009). Where the previous year's cash flow is not available for a firm year, I would like the lagged variable to be a missing value by simply leaving the related cell blank. Thanks very much.

Explanation / Answer

If you have columns like this:

A B C

ID Year CashFlow

Then just use a formula like this for the previous year's cashflow:

=IF(COUNTIFS(A:A,A2,B:B,B2-1)>0,SUMIFS(C:C,A:A,A2,B:B,B2-1),"")

then fill the column, and copy / paste values.

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.