• Status: Solved
• Priority: Medium
• Security: Public
• Views: 251

# 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
0
WooYing
1 Solution

Commented:
Hi,

pls try

Row 13 and Col 14 should be hidden

in C10 I've used =DATEDIF(C3,C4,"m")

and in E23 former E22
=SUM(INDIRECT("E13:E"&13-2+MATCH(C10-MOD(C10,12),F12:F19)))+INDIRECT("E"&13+MATCH(C10-MOD(C10,12),F12:F19))/12*MOD(C10,12)
Regards
100724982V1.xlsx
0

Commented:
Assumptions

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:
Depreciation    Months    Weighted
\$200      12      \$200
\$320      12      \$320
\$192      4      \$64
\$115      0      \$0
\$115      0      \$0
\$58      0      \$0

Total = \$584

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:
=(YEAR(C4)-YEAR(C3))*12+MONTH(C4)-MONTH(C3)

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
=MIN(MAX(\$C\$10-(A13-1)*12,0),12)

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
=E20-SUM(F13:F18/12*E13:E18)
...it will display as:
{=E20-SUM(F13:F18/12*E13:E18)}

Example file attached
- Column F is needed
- Column G is not, it's just to help explain

Partition-Date-by-Year-and-Sum.xlsx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.