Monthly Allocation

    Tired if this & found some online solution but none of them solve my problem.
    If A1=Date (any date like 14 March 2016)
    B1=Monthly Amount
    C1=No. Of Month (Like 5 or 8)
    & my excel has fixed Jan to Dec, 12 columns for current year.


    So i need a formula which can populate Monthly Amount from March to July (if C1 is 5, irrespective of date)

    Re: Monthly Allocation


    You have to make sure that your cells in which you store the months January through February are actually "full dates" in other words 1/1/16, etc. You can change the formatting to custom and have mmmm as the type and it will display just the month. No assuming that January start at E1 which would make Decembeer in P1, the formula you could use is.

    Code
    =IF(AND(MONTH($A$1)<=MONTH(E1),(MONTH($A$1)+$C$1)>=MONTH(E1)),$B$1,"")


    I put this in the row directly below the months in other words the second row starting at E2. I have also attached a file so you can see it in action.


    forum.ozgrid.com/index.php?attachment/68974/

    Re: Monthly Allocation


    Thanks for quick reply. Formula works great except it is taking one extra month & could not understand why?
    Even in the excel you have attached, allocation for 5 Months should end in July(March to July is 5 months), but it is taking August too. Making the total amount 6000 instead, it should be 5000

    Re: Monthly Allocation


    I do apologize. I should have caught that. the change is actually quite simple. I had to add -1 to the formula. I put it in red here.
    =IF(AND(MONTH($A$1)<=MONTH(E1),(MONTH($A$1)+$C$1-1)>=MONTH(E1)),$B$1,"")

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login