Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# Excel need help figuring out a formula Part II

Posted on 2014-01-23
Medium Priority
241 Views
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
Question by:WooYing

LVL 53

Accepted Solution

Rgonzo1971 earned 1500 total points
ID: 39805725
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

LVL 11

Expert Comment

ID: 39805766
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
###### Suggested Courses
Course of the Month10 days, 5 hours left to enroll