Flagging multi-week activities across calendar weeks?

dyoung

New Member
Joined
Jan 15, 2004
Messages
9
can anybody help me come up with a 'streamlined' formula to resolve this one.........

The data I am trying to analyse is effectively a large number of work activities (45k+) due to occur over the next four year period. Amongst other details each activity has a start date and duration.

In a separate table I have columns split into 52 weeks per year. What I am trying to achieve is to insert a simple 'flag' ie a number 1 against each week that an activity occurs. From this I can use the flag as a multiplier in many further calculations.

For work activities starting and finishing in the same week a simple IF or LOOKUP function can be used. How though do I get a work activity that spans say 3 weeks to input a flag into the three respective week columns of my separate table. I have managed to resolve this with lengthy arithmetical calculations however this causes memory issues with the volume of formula entries that I require.

Any bright ideas greatfully received.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Welcome!

This could be one way assuming start dates are in chronological order.
Book1
ABCDEFG
1StartdateDurationdaysStartweekEndweekWeekActivity
22004-01-0151211
32004-01-1032321
42004-02-15107931
52004-03-0112101140
Sheet2
 

dyoung

New Member
Joined
Jan 15, 2004
Messages
9
Thank you for the very quick response.....unfortunately, your solution is not exactly what I was after. Perhaps I can explain this in further detail as follows:

Each row now contains a separate work activity with a start week, an end week and a required resource. This resouce is required for each and every week that the work activity is ongoing. Ultimately I am trying to calculate the total resource required, per week, for all of the work activities.

I have so far managed to achieve this by creating a table of 52 columns (1 for each week) and using the following formula to insert a '1' in the relevant column whenever a work activity is in progress during that week:

=(IF(Activity_Start_Week<=Week_Num,1,0)*(IF(Activity_End_Week>=Week_Num,1,0)))

and thereafter using 'sumif' function to total the required resources for each activity having a '1' in the relevant week. Although this works okay it is VERY SLOW since the IF formula is present in 52 weeks * 45,000 activitys = long time!

There must be an easier way to do this but for the life of me I cannot think of it.

Any further ideas?

ps - hopefully this is clearer, I cannot download the html maker to display an example since I am using a work PC.
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Is this more like you want?
Book1
ABCDEFGHIJK
1ActStartwEndwResourceweek12345
2aaa15wwResource
3bbb17xxww13322
4ccc23wwxx11221
5ddd24yyyy01110
6eee34zzzz00110
7fff25ww
8ggg34xx
Sheet3
 

dyoung

New Member
Joined
Jan 15, 2004
Messages
9

ADVERTISEMENT

Very nearly, only 'minor' problem is that each activity has a different quantity of resource ie. activity aaa may require 2 'ww' per week whereas activity ccc requires 1 'ww' per week and activity fff 3 'ww' per week.

I am thinking this should work quite simply with the addition of another array within the sumproduct calculation........or perhaps not?
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Now formula looks like:

=SUMPRODUCT(($D$2:$D$10=$F3)*($B$2:$B$10< =G$1)*($C$2:$C$10 > =G$1),$E$2:$E$10)
Book1
ABCDEFGHIJK
1ActStartwEndwResourcequantityweek12345
2aaa15ww2Resource
3bbb17xx5ww27766
4ccc23ww1xx55665
5ddd24yy2yy02220
6eee34zz3zz00330
7fff25ww4
8ggg34xx1
Sheet3
 

dyoung

New Member
Joined
Jan 15, 2004
Messages
9
Fairwinds.......thank you.

I see that you even got your response time down to 10 mins last time round!

You are truly a Guru.

Yours humbled,
D
 

Watch MrExcel Video

Forum statistics

Threads
1,130,118
Messages
5,640,215
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top