WooYing
asked on
Excel need help figuring out a formula Part II
Thank you all for your help, I downloaded a Excel file off MS site which seems to be helpful but just like in my last question I like to include a month tab where I can add a value (C3 and C4). Then in C10 would be the difference in C3 and C4 in months. And then E22 would be the value of in months but it gets a little tricky, as we all know in a year has 12 month and if I take 18 months. It will have to take the value of year 1 depreciation and 6 month of year 2 base of the value of C10. This should occur in any month I chose, so for 28 months it be year 1 and year 2 depreciation value plus 4 months of year 3. Hope that makes sense, please see attach file and thank you.
100724982.xlsx
100724982.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'm assuming that the depreciation in col E is the total for the year and if you have say 4 months within that period the total is 4/12 of that value?
e.g. 28 months of depreciation:
I think you then want cell E22 to contain the remaining? i.e. $1000 - $584 = $416?
Solution
Put this formula into C10 to calculate the total number of months:
Open in new window
Add this to F13 (and copy down to F18):
It subtracts the number of years for that row with any left over months cropped into the 0-12 range by MIN/MAX
Open in new window
Finally use an array formula to sum col E * col F:
You must hit Ctrl+Shift+Enter instead of just Enter every time you enter/edit an array formula
Open in new window
...it will display as:Open in new window
Example file attached
- Column F is needed
- Column G is not, it's just to help explain
Partition-Date-by-Year-and-Sum.xlsx