I could really use some expertise here. I've attached a dataset, also below, which is appended with new data every month, for 12 months. As of now, I have the last month of previous year, for comparison, and Jan-Mar. For each unique "Identifier", I need to calculate the month-over-month variance of Amt2.
(i.e. JAN Amt2 divided by 2018DEC Amt 2, FEB Amt2 divided by JAN Amt2, etc) I have to keep in mind that the formula must adjust to the number of months currently within the dataset, and still process successfully with new months added. Your help is greatly appreciated!
| CONTROL | Identifier | #Mths Loaded | Earnings Mth | Effective End Date | Mth# | Amt1 | Amt2 | Amt3 | Month over Month Variance |
| 2018DEC12345678Canada | 12345678 | 4 | 2018DEC | 2019-01-31 | 201812 | 7520.32 | 7520.32 | 7520.32 | |
| JAN12345678Canada | 12345678 | 4 | JAN | 2019-02-28 | 201901 | 6432.01 | 0.00 | 6432.01 | |
| FEB12345678Canada | 12345678 | 4 | FEB | 2019-03-31 | 201902 | 6432.01 | 6432.01 | 0.00 | |
| MAR12345678Canada | 12345678 | 4 | MAR | 2019-04-30 | 201903 | 8445.63 | 6483.85 | 10000.00 | |
IF [Row+1:IDENTIFIER]=[IDENTIFIER] THEN [Row+1:Amt2]/[Amt2]) ELSEIF [Row+2:IDENTIFIER]=[Row+1:IDENTIFIER] THEN [Row+2:Amt2]/[Row+1:Amt2] ELSEIF [Row+3:IDENTIFIER]=[Row+2:IDENTIFIER] THEN [Row+3:Amt2]/[Row+2:Amt2] ELSEIF [Row+4:IDENTIFIER]=[Row+3:IDENTIFIER] THEN [Row+4:Amt2]/[Row+3:Amt2] ELSEIF [Row+5:IDENTIFIER]=[Row+4:IDENTIFIER] THEN [Row+5:Amt2]/[Row+5:Amt2] ELSEIF [Row+6:IDENTIFIER]=[Row+5:IDENTIFIER] THEN [Row+6:Amt2]/[Row+5:Amt2] ELSEIF [Row+7:IDENTIFIER]=[Row+6:IDENTIFIER] THEN [Row+7:Amt2]/[Row+6:Amt2] ELSEIF [Row+8:IDENTIFIER]=[Row+7:IDENTIFIER] THEN [Row+8:Amt2]/[Row+7:Amt2] ELSEIF [Row+9:IDENTIFIER]=[Row+8:IDENTIFIER] THEN [Row+9:Amt2]/[Row+8:Amt2] ELSEIF [Row+10:IDENTIFIER]=[Row+9:IDENTIFIER] THEN [Row+10:Amt2]/[Row+9:Amt2] ELSEIF [Row+11:IDENTIFIER]=[Row+10:IDENTIFIER] THEN [Row+11:Amt2]/[Row+10:Amt2] ELSEIF [Row+12:IDENTIFIER]=[Row+11:IDENTIFIER] THEN [Row+12:Amt2]/[Row+11:Amt2] ELSE "" ENDIF
