Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel need help figuring out a formula Part II

Posted on 2014-01-23
2
Medium Priority
?
239 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 52

Accepted Solution

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

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

688 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