Excel help - auto populating cells based on conditions
Discussion
I have a tricky conundrum to solve in MS Excel. I am stuck and would like to throw the question out there for assistance I have not been otherwise able to find a solution to.
I am working on an increasingly complex finance modelling spreadsheet. An easy example of what I am trying to do could be:
An asset is purchased at say £10,000, and will be depreciated over 5 years. In column A, we could put the £10,000 an in column B, the 5 years. We will assume the asset has 0 value after the 5 years, and the asset is purchased in month 4 of a year.
Now, what I am trying to do: The asset depreciates at 10,000/60 per month (i.e. purchase cost divided by 60 months). I want the spreadsheet to read the information already put in, then populate columns to the left from month 4 to month 64 with that value - automatically. I am therefore asking the Excel sheet to work out a value, then populate the next n cells with that value, with n being the variable number of months. After n rows have been filled in, the remaining rows are to be left blank (say column for month 65 onwards).
The model I am building is for 84 months.
Is this even possible in Excel? I know there are about 450 commands in Excel, some of which are very clever, but I usually manage with simple operators, percentages, and conditional formatting of the If, then, Else make up.
Any help gratefully received from Excel experts out there!
B
I am working on an increasingly complex finance modelling spreadsheet. An easy example of what I am trying to do could be:
An asset is purchased at say £10,000, and will be depreciated over 5 years. In column A, we could put the £10,000 an in column B, the 5 years. We will assume the asset has 0 value after the 5 years, and the asset is purchased in month 4 of a year.
Now, what I am trying to do: The asset depreciates at 10,000/60 per month (i.e. purchase cost divided by 60 months). I want the spreadsheet to read the information already put in, then populate columns to the left from month 4 to month 64 with that value - automatically. I am therefore asking the Excel sheet to work out a value, then populate the next n cells with that value, with n being the variable number of months. After n rows have been filled in, the remaining rows are to be left blank (say column for month 65 onwards).
The model I am building is for 84 months.
Is this even possible in Excel? I know there are about 450 commands in Excel, some of which are very clever, but I usually manage with simple operators, percentages, and conditional formatting of the If, then, Else make up.
Any help gratefully received from Excel experts out there!
B
Example
Cell for initial purchase cost A1
Cell for depreciation period A2
Cell for Monthly depreciation A3 with the formula =SUM(A1/B1)
Start your Columns at B1, with the first Month at Purchasing Price =SUM(A1), then the next column for Month 2 would be =SUM(B1-A$2$) to deduct the depreciation from the previous month total. You can then copy that formula across all the next relevant columns so each column will deduct the depreciation sum from the preceding months total. The $ after A and 2 anchor the sum to that column, but allow the first part of the sum to shift with the columns.
Cell for initial purchase cost A1
Cell for depreciation period A2
Cell for Monthly depreciation A3 with the formula =SUM(A1/B1)
Start your Columns at B1, with the first Month at Purchasing Price =SUM(A1), then the next column for Month 2 would be =SUM(B1-A$2$) to deduct the depreciation from the previous month total. You can then copy that formula across all the next relevant columns so each column will deduct the depreciation sum from the preceding months total. The $ after A and 2 anchor the sum to that column, but allow the first part of the sum to shift with the columns.
Guys - thank you!
I would have been a codebreaking failure in WWII buy you have presented two solutions that my brain would not have possibly ever conceived. I will give them a go and see how I fair/
I have a similar conundrum for model things like creditor payments into a cashflow forecast. Depending on whether payments are within 30,60,or 90 days - so we can look at the effects of these optimistically and prudently. I.e., the user can select 30, 60 or 90 days from a drop down, and it pus the payments received into (say) column for month 2, month 3 or month 4 (or 1, 2, or 3 months on - shifting to the left all of the time).
Thanks all!
I would have been a codebreaking failure in WWII buy you have presented two solutions that my brain would not have possibly ever conceived. I will give them a go and see how I fair/
I have a similar conundrum for model things like creditor payments into a cashflow forecast. Depending on whether payments are within 30,60,or 90 days - so we can look at the effects of these optimistically and prudently. I.e., the user can select 30, 60 or 90 days from a drop down, and it pus the payments received into (say) column for month 2, month 3 or month 4 (or 1, 2, or 3 months on - shifting to the left all of the time).
Thanks all!
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff



