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

1. ## 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

2. ## 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. ## 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. ## 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)

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

#### 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