Percentage Calculation Based On Months & Dates

  • Hi all,



    Here's what I want to do:


    I want to know how to base a formula upon a specific recurring month, and another formula on all other months. One formula for every June, or every January. A second formula for every other month.


    For example, I want to recognize 50% of yearly revenue in January, but (50%/11) of yearly revenue in all other months. But I want this to repeat depending on how long the contract is. So EACH january it would use formula 1, and every other month, formula 2.


    Thanks so much!!


    - Jeff

  • Re: Formula Based On Specific Recurring Month


    Quote

    For example, I want to recognize 50% of yearly revenue in January, but (50%/11) of yearly revenue in all other months. But I want this to repeat depending on how long the contract is. So EACH january it would use formula 1, and every other month, formula 2.


    Since you didn't post a sample workbook or provide much detail, here is a possible solution based on assumptions:


    Cell A1 = yearly total
    Cell B1 = Date
    Cell C1 = IF(MONTH(B1)<2,A1*.5,(A1*.5)/11)

  • Re: Percentage Calculation Based On Months &amp; Dates


    Ok, I've uploaded an excel spreadsheet to give you an idea of what I'd like to be done.



    Where it says, "Revenue recognized at these dates" I would like a formula in the grayed boxes which places the correct revenue under those months according to the starting date of the contract, the contract length, and the amount of revenue per year.



    Thanks very much,


    - Jeff

  • Re: Percentage Calculation Based On Months &amp; Dates


    Try this in your H3 and copy across to the right:


    =IF(MONTH($B$2)=MONTH(H$1),$A$2*0.5,($A$2*0.5)/11)

    -------------
    [FONT="Comic Sans MS"]Averil Pretty[/FONT]

  • Re: Percentage Calculation Based On Months &amp; Dates


    Quote

    ah yes, i didn't know about that function. thanks.


    Which function do you mean? I supplied both of them to you in my initial post, you only had to adapt them to your needs.

Participate now!

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