Solved

Excel need help figuring out a formula Part II

Posted on 2014-01-23
2
202 Views
Last Modified: 2014-03-11
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
Comment
Question by:WooYing
2 Comments
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 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)

Open in new window

Regards
100724982V1.xlsx
0
 
LVL 11

Expert Comment

by:Angelp1ay
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)

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

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
=E20-SUM(F13:F18/12*E13:E18)

Open in new window

...it will display as:
{=E20-SUM(F13:F18/12*E13:E18)}

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
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question