Share via

Spreading the values over a specific period

Anonymous
2013-03-06T18:19:15+00:00

Hi,

I am trying to find formula for blue highlighted cells in Excel.

If I enter the data in first three columns, it should spread the amount over the period mentioned automatically with the formula in blue cells.

Any help.

Pllease find below link for sample file:

https://skydrive.live.com/view.aspx?Bsrc=Share&Bpub=SDX.SkyDrive&resid=FFC61FD15D1AC8A1!142&cid=ffc61fd15d1ac8a1&app=Excel&authkey=!Al-BMhmF8w4KxLA

Thanks and regards,

Suresh

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Ashish Mathur 102K Reputation points Volunteer Moderator
2013-03-20T13:54:13+00:00

Hi,

In cell D2, enter this formula

=IF(OR(EOMONTH(D$1,0)<=$A2,EOMONTH(D$1,-1)>$B2),0,IF(TEXT(D$1,"mmyyyy")=TEXT($A2,"mmyyyy"),(30-DAY($A2)+1)*($C2/(ROUND(DAYS360($A2,$B2)/30,1))/30),IF(TEXT(D$1,"mmyyyy")=TEXT($B2,"mmyyyy"),($B2-EOMONTH(D$1,-1))*($C2/(ROUND(DAYS360($A2,$B2)/30,1))/30),$C2/ROUND(DAYS360($A2,$B2)/30,1))))

Copy and paste this down and to the right.

Was this answer helpful?

0 comments

19 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2013-03-11T15:20:49+00:00

    Ok, I will explain.

     

    First row, I am paying an amount of USD 24,000 as rent in advance for the period from 01 Jan 2013 to 31 Dec 2014 (for two years). That means it is for a period of 24 months and monthly it works out to USD 1000.  So the amount is spread over 24 months @ USD 1000 per month from Jan 2013 through Dec 2014. It is a simple calculation.

     

    Second row, the same way, I am paying an amount of USD 72000 for 9 months in advance for the period from 21 Feb to 20 Nov 2013. So the amount is spread over these nine months (calculating 30 days a month). For Feb 2013, it is for 10 days 8000/30*10 and for Nov 2013 it is for 20 days 8000/30*20 and for all other months 8000 per month.

     

    What is the formula to be used in blue cells to do the above calculations?

     

    Regards,

    Suresh

     

    Any solution?

    Regards

    Suresh

    Was this answer helpful?

    0 comments
  2. Anonymous
    2013-03-07T16:04:54+00:00

    Ok, I will explain.

    First row, I am paying an amount of USD 24,000 as rent in advance for the period from 01 Jan 2013 to 31 Dec 2014 (for two years). That means it is for a period of 24 months and monthly it works out to USD 1000.  So the amount is spread over 24 months @ USD 1000 per month from Jan 2013 through Dec 2014. It is a simple calculation.

    Second row, the same way, I am paying an amount of USD 72000 for 9 months in advance for the period from 21 Feb to 20 Nov 2013. So the amount is spread over these nine months (calculating 30 days a month). For Feb 2013, it is for 10 days 8000/30*10 and for Nov 2013 it is for 20 days 8000/30*20 and for all other months 8000 per month.

    What is the formula to be used in blue cells to do the above calculations?

    Regards,

    Suresh

    Was this answer helpful?

    0 comments
  3. Anonymous
    2013-03-07T07:58:35+00:00

    Suresh wrote:

    I am trying to find formula for blue highlighted cells in Excel.   If I enter the data in first three columns, it should spread the amount over the period mentioned automatically with the formula in blue cells.

    [....]

    https://skydrive.live.com/view.aspx?Bsrc=Share&Bpub=SDX.SkyDrive&resid=FFC61FD15D1AC8A1!142&cid=ffc61fd15d1ac8a1&app=Excel&authkey=!Al-BMhmF8w4KxLA

    I'm sorry:  I left my Vulcan mind-meld instruction book on another planet.  So I cannot read you mind.

    What exactly are the criteria for the distribution that you want to apply?

    I don't see any consistency between the first and second rows in your example.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2013-03-07T07:21:26+00:00

    You should check the programming forums instead, there you would find programmatic solutions.

    Hope one of the mvp's move it there.

    Was this answer helpful?

    0 comments