lovemakingsheets
New Member
- Joined
- Nov 30, 2017
- Messages
- 1
I'm trying to build a worksheet to help me calculate the lifetime costs of various vehicle ownership strategies. (Finance, Lease, Buy with cash, Buy Used, etc.)
I've got the whole sheet working, except that I'd like to be able to modify a value in a single cell (Length of Ownership) and have it populate the data throughout the sheet correctly.
ie. Right now, with the Buy with Cash section, I have all sorts of baseline values, like purchase price, annual insurance, etc. But I have to manually place values every 10 years to indicate a sale/new purchase, and the accompanying costs.
Say A1 = Length of Ownership = 10
A2 = Purchase Price = 10000
A3 = Transfer Tax = A2*.07
Row 4 = Purchase Costs
Row 5 = Transfer Tax
Columns B to Z = incrementing years (2017, 2018, 2019, etc.)
In cell B4 I put "=A2"
In cell B5 I put "=A3"
Now what I want is a formula that I can place in the rest of the columns in Row 4 and 5, that will correctly fill it in. Based on the Length of Ownership value the formula would keep the next 9 rows blank, then at the 10th row, would either reference the value in Column A or Column B. So every 10 columns the values would show up.
Thanks if you have any ideas! I've been playing with OFFSET but can't figure out a way to make it recursive, and I've been wondering about using COLUMN or something, but again, haven't made progress. I tried some IF statements to try and increment based on the value in A1 but no go.
I've got the whole sheet working, except that I'd like to be able to modify a value in a single cell (Length of Ownership) and have it populate the data throughout the sheet correctly.
ie. Right now, with the Buy with Cash section, I have all sorts of baseline values, like purchase price, annual insurance, etc. But I have to manually place values every 10 years to indicate a sale/new purchase, and the accompanying costs.
Say A1 = Length of Ownership = 10
A2 = Purchase Price = 10000
A3 = Transfer Tax = A2*.07
Row 4 = Purchase Costs
Row 5 = Transfer Tax
Columns B to Z = incrementing years (2017, 2018, 2019, etc.)
In cell B4 I put "=A2"
In cell B5 I put "=A3"
Now what I want is a formula that I can place in the rest of the columns in Row 4 and 5, that will correctly fill it in. Based on the Length of Ownership value the formula would keep the next 9 rows blank, then at the 10th row, would either reference the value in Column A or Column B. So every 10 columns the values would show up.
Thanks if you have any ideas! I've been playing with OFFSET but can't figure out a way to make it recursive, and I've been wondering about using COLUMN or something, but again, haven't made progress. I tried some IF statements to try and increment based on the value in A1 but no go.
