+ Reply to Thread
Results 1 to 4 of 4

Distribute quarterly cost values to months based on start finish date of the activity

  1. #1
    Registered User
    Join Date
    10-23-2012
    Location
    perth
    MS-Off Ver
    Excel 2007
    Posts
    3

    Distribute quarterly cost values to months based on start finish date of the activity

    HI,
    I am new member in this forum. I have been trying to find a formula calculating monthly cost for an activity from the quarterly cost figures considering start finish date of the activity. but no luck. I will be grateful if someone can help!

    here is the requirement;
    Activity start finish dates varies can be from beginning of the quarter to end of the quarter or from middle of the quarter to middle of next quarter. Need to distribute quarterly values evenly to the months.
    For example in below table, activity A, excel formula should help distribute Q1=60 to months Jan, Feb, and March evenly.(each month should be 20); while Activity B, q1 value-20 should be divided equally between Feb and March (nothing for Jan since start date is Feb), and q2 value-40 should be distributed to April and May equally.
    here is the attachment...

    Start Finish Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec q1 q2 q3 q4
    Cost of activity A 1/01/2013 31/03/2013 60 0 0 0
    Cost of activity b 1/02/2013 31/05/2013 20 40 0 0
    Last edited by alaturka; 10-23-2012 at 10:56 AM.

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: Distribute quarterly cost values to months based on start finish date of the activity

    hi alaturka, welcome to the forum. pls see if the attached file helps. i converted your dates in D1:P1 for easier formulating. you should consider doing that for dates. i then format it to show like you did earlier. although it shows as "Jan", see that the formula bar shows 1/1/2013

  3. #3
    Registered User
    Join Date
    10-23-2012
    Location
    perth
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Distribute quarterly cost values to months based on start finish date of the activity

    thank you so much! this is working. i really appreaciate it

  4. #4
    Registered User
    Join Date
    10-23-2012
    Location
    perth
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Distribute quarterly cost values to months based on start finish date of the activity

    HI benishiryo, when i wanted to extent the formula to Oct 2012, Nov 2012, Dec 2012 to split the data from Q4 2012,
    dragging the formula into these sells did not work. I have changed the cell values in the formula to capture this period, but again no luck.
    heer is my new attachment, with Oct 2012, Nov 2012, Dec 2012 to and Q4 period value added. Can you help me to capture this period.
    Also, can you shortly explain how this formula works-although i was thinking it was self experissive initialy, but obviously i must be missing something.
    thanks!

    IF(AND(G$1>=$B2,G$1<=$C2),INDEX($T2:$W2,CEILING(MONTH(G$1)/3,1))/SUMPRODUCT(--(OFFSET($G$1,0,CEILING(COLUMNS($G$1:G$1),3)-3,,3)=ROW(INDIRECT($B2&":"&$C2)))),0)
    Attached Files Attached Files
    Last edited by alaturka; 10-23-2012 at 09:09 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1