cumulative sum based on range

W

wongsol

New Member
Joined
Oct 17, 2007
Messages
5
Hello:

I'll try to explain this the best way I can.

I've been asked to do a resource matrix based on complexity of work and duration of a project.

Complexity would be values of 0-5. Duration would be based on a start date and an end date. I think the WEEKNUM function may work out here [e.g. week 9 (start) week 45 (end); the difference would be 36 (weeks)].

I would need to take the start week and populate the count from week 9 all the way through to week 45..run a loop through the data and continue to populate each cell for each week that there is activity (or duration/difference between the two dates).

So if there is a project that starts week 1 and ends week 5 and another project that starts week 2 and ends week 4...the table should look like this...

For complexity 0 and PM Joe; week1=1, week2=2 week3=2, week4=2, week5=1.

Any help would be greatly appreciated, I'm going bald :)
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
And ... what is the question?
Before going bald could you explain the problem in a "for dummies" manner? Something like: "I have this data organised like this and I need to get this kind of result by processing the data in the following way"... Maybe you have all data in a sheet and you want the results in another (or several other) sheet(s), or maybe you need a macro with a dialogue, or a wizard, or a set of formulas, or... who knows (?). I understand that for complexity 0 the table should look like week1=1 and so on, but I don't understand who's PM Joe (is PM standing for "Prime Minister", or is it "Post Meridian", not mentioning "Post Mortem"... ?). The only thing I'm sure I understood from your explanation above is that 45-9=36 (speaking of weeks, of course) - still looking for that "cumulative sum based on range" and still can't find it except in the title of your post (even without a mirror at hand I have the same feeling about baldness... not sure if this can be of any help)
 
Upvote 0
I have this organized like this...:)

A column that specifies a PM (Project Manager), column that specifies complexity (0-5), a column that specifies the start work week and another column that specifies end work week (9 and 45 respectively).

So I want to loop through this data sheet and find (for example) Complexity 1; PM "Joe"; look at the start week and end week (9-45 resp) and put this in to another sheet that would reflect the "week1=1, week2=2 week3=2, week4=2, week5=1" cumulative total...

..so the results page would have Week 1 (B1), 2 (C1), 3(D1) etc across row 1 as the Header..
Column A would have 0-5 (A2,A3,A4,A5 etc..); the other array would find PM="Joe".

So with criteria for the search complexity 0; PM=Joe, from the source page = 2 projects that match one with start week = 1, end week = 5 and the other match start week = 2, end week =5 my result page should look like B2=1, C2 =2, D2=2, E2=2, F2=2 with A2 as my indicator for "0" complexity and B1-B53 values as 1-52 (indicating the week number)...so I need to know how to accomplish this result.

I hope I've explained it well enough.

SW
 
Upvote 0
I'm not sure I understood exactly what you want, but here's a sample of what I could understand.
In Sheet1 there is a table like this:

<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
ABCD
1PMComplexityStart WeekEnd Week
2Joe015
3Anna016
4George2312
5Anna1511
6Joe025
7John3124
8George2415
9Joe126
10John4230
11Joe149
Sheet1


</body></html>

In Sheet2 there is this table for PM Joe:

<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
ABCDEFGHIJK
1Joe12345678910
201222200000
310112221110
420000000000
530000000000
640000000000
750000000000
Sheet2


</body></html>

There can be more tables in Sheet2 (for each PM), or there can be more sheets with tables like this (one sheet/PM), but anyway, the formula written in cell B2 of this example can be copied across the whole Joe's table and of course the week number can go up to 52.

Hope I could be of some help.
 
Upvote 0
Thank you gecs.

This is awesome! Exactly what I was looking for!

Thank you so much and I apologize for my lack of explaination and confusion.

Thank you again!!
 
Upvote 0
You're welcome. I also have to apologize for wasting forum space due to my lack of experience with Excel jenie which led to the unnecessary long list of formulas to be displayed.

Sometimes confusion leads to unexpected good results if we find the patience to clear it up a bit. Anyway it was fun writing my first reply to this topic. I hope you didn't take it otherwise than a joke about "going bald" :)
 
Upvote 0
No offence taken...I have a full set of hair, getting the greys but it was bound to come.

Thanks again for the quick response and aid.

SW
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
How can I allocate task across multiple departments and periods?
Replies
0
Views
75
charltondav
C
C
  • Question
Revenue Allocation by Week
Replies
3
Views
246
ChetanPuri
C
W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
16
Views
1K
phiero21
P
I
  • Question
Weekly Task tracker
Replies
5
Views
299
Icecoldstar
I
S
  • Question
Send automated emails based on dates
Replies
3
Views
713
somesoldiers
S

Forum statistics

Threads
1,214,798
Messages
6,121,630
Members
449,041
Latest member
Postman24

Share this page

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
Back
Top