I'm currently trying to transform the data to calculate depreciation, so it will loop until the Ending Net Book Value is 0 or close to it. I saw people using multi-use formula, but that was with periods already present. Not sure if this would require a macro instead.
Assumptions being made in the dataset
1. Amortization remains the same throughout the years
2. Ending Net Book Value = Beg Net Book Value - Amortization
Currently, the data is the following
Year | Asset | Beg Net Book Value | Amortization | End Net Book Value |
2034 | Pencil | 5000 | 500 | 4500 |
2034 | Eraser | 2000 | 400 | 1600 |
2034 | Mouse | 1000 | 1000 | 0 |
Dataset to be transformed to
Year Asset Beg NBV Amortization End NBV
2034 Pencil 5000 500 4500
2035 Pencil 4500 500 4000
2036 Pencil 4000 500 3500
2037 Pencil 3500 500 3000
2038 Pencil 3000 500 2500
2039 Pencil 2500 500 2000
2040 Pencil 2000 500 1500
2041 Pencil 1500 500 1000
2042 Pencil 1000 500 500
2043 Pencil 500 500 0
2034 Eraser 2000 400 1600
2035 Eraser 1600 500 1100
2036 Eraser 1100 500 600
2037 Eraser 600 500 100
2038 Eraser 100 100 0
2034 Mouse 1000 1000 0
Thanks in advance!
Solved! Go to Solution.
My bad, I was just trying to create sample data. The solution works thanks!
No worries - glad it worked @CFuser87 !
@CFuser87 using batch macro
Just a Multi Row Formula to add 1 to the year would do the trick @CFuser87 - one sec
Amazing, thank you again! Just learned about the new Tile tool!