Trying to build a formula in a table to cross reference multiple data fields (Work out Loading of resources across multiple projects by month)

This one is probably a lot simpler than I think, and I'm guessing I've massively over complicated everything.
Basically I have a workbook of tables of data. The tables record details of staff, their monthly working hours, current projects, what staff are assigned to those and how many hours they have a month on each project, what percentage of their total availability that is etc

Tables: Projects: This is where I list all the projects, and who is assigned, and how many hours
Tables: Resource: This is the list of staff, and a divider to get their weekly hours (From 40 max, so set to 2 would mean their weekly hours are 20)
Tables: WorkHours: This calculates using network days how many working hours there are in a given month, and reduces that by bankholidays etc
Tables: MonthsActive: This calculates what months each project is live for
Tables: ResourceLoading: Should calculate total hours Loading for each member of staff for each month

Resource loading is where my issue is. I'm stuck trying to build any kind of formula to do this.  Last resort, I'll go VBA, but if this is possible to do in the worksheet with Formulae, I'd rather go that route.

Workbook attached.
staff-loading-look-ahead---declassi.xlsm
LVL 2
Conor_NewmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ejgil HedegaardCommented:
You can use below formula in C35.
The formula is automatically copied down the column when entered.
Select the entire column in the table C35:C47 and drag, using the square in the lower right corner, to the other columns. Don't copy.

=SUMPRODUCT((Projects[[Resource]:[Resource]]=ResourceLoading[[#Headers],[Staff 2]])*(Projects[[Start Date]:[Start Date]]<=ResourceLoading[[#This Row],[Month]:[Month]])*(Projects[[Finish Date]:[Finish Date]]>=ResourceLoading[[#This Row],[Month]:[Month]])*Projects[[Monthly Hours]:[Monthly Hours]])

Open in new window

staff-loading-look-ahead-declassi.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Christopher Jay WolffWiggle My Legs, OwnerCommented:
I certainly don't know what I'm talking about but I thought I'd speak up since I found a link.

This site has several conceptual methods for you to consider.  Please correct me if I'm wrong, but this is a large undertaking and the question covers a lot of ground.  I am sending this link to give you ideas.  Maybe it will help you get started and assist with generating specific questions, Maybe it will help you more easily see ways to do what you want to do.  Then again, with all your excel experience, maybe this is old info.

I started out thinking, I'll ask what type of analysis is needed and so forth, but you may change your answers after reading the link.  It covers functions like DAVERAGE, DCOUNT, DCOUNTA, creating histograms, and Progress S-curves.  I wonder what you think of it.

http://www.toolboxforplanning.com/search/label/Excel%20Tutorial

By the time you review it, maybe someone who does a lot of this will be responding to you here.
0
PortletPaulfreelancerCommented:
I wouldn't dream of doing this in Excel

(I'm biased however, I have used commercial off-the-shelf PM systems)

I would capture the necessary data into a database (e.g. Access, SQL Server, MySQL, Oracle)

The data tables designed to "third normal form"
(basically: so you only have to enter new data once)

Then I would use SQL for the logic required to produce the information sought.

Spreadsheets are tremendous at so many things, but they do not replace databases for things of this nature in my view.

other options:

Look for an existing off-the-shelf solution, many exist. Often these are available cheaply as cloud based systems. There might be open source solutions.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PortletPaulfreelancerCommented:
Here are 3 sample tables and data (Projects, Reources, and ProjectResources). In the ProjectResources table I have introduced an Allocation Start/End and an FTE. The assmption being that a person might be on a project for less than the project duration, and during that time they may be full time to the project, or part-time to the project. With this table you could also have the same person full time for (say) a month, then 20% allocated for 3 months etc.

In addition to those tables I have partially built a "calendar table" based on an article by Jim Horn this table is an essential tool in helping us with "working days" and how many hours are allocated into the future.
CREATE TABLE Projects
    (
     id int identity primary key, 
      [ProjectName] varchar(14), [StartDate] datetime, [FinishDate] datetime)
;
    
INSERT INTO Projects
    ([ProjectName], [StartDate], [FinishDate])
VALUES
    ('Test Project 1', '2015-10-01 00:00:00', '2015-12-31 00:00:00'),
    ('Test Project 2', '2015-09-01 00:00:00', '2015-12-31 00:00:00'),
    ('Test Project 3', '2015-09-01 00:00:00', '2015-12-31 00:00:00'),
    ('Test Project 4', '2016-01-01 00:00:00', '2016-03-31 00:00:00')
;

CREATE TABLE Resources
    (
     id int identity primary key, 
      [Resource] varchar(7), [WeeklyHours] decimal(4,2))
;
    
INSERT INTO Resources
    ([Resource], [WeeklyHours])
VALUES
    ('Staff 1', 1.07),
    ('Staff 4', 1.07),
    ('Staff 2', 2.00),
    ('Staff 3', 1.07)
;

CREATE TABLE ProjectResources
    ([ProjectID] int, [ResourceID] int, [FTE] decimal(4,2), [AllocStart] date, [AllocEnd] Date)
;
    
INSERT INTO ProjectResources
    ([ProjectID], [ResourceID], [FTE])
VALUES
    (1, 1, 1.0),
    (1, 2, 0.5),
    (2, 1, 1),
    (2, 2, 1),
    (2, 3, 1),
    (3, 1, 1),
    (3, 3, 1),
    (3, 4, 1),
    (4, 1, 0.5),
    (4, 2, 0.5)
;

CREATE TABLE days (
   PKDate date NOT NULL PRIMARY KEY CLUSTERED, 
   -- Years
   calendar_year smallint,
   -- Quarters 
   calendar_quarter tinyint, 
   calendar_quarter_desc varchar(10), 
   -- Months
   calendar_month tinyint, 
   calendar_month_name_long varchar(30), 
   calendar_month_name_short varchar(10), 
   -- Weeks
   calendar_week_in_year tinyint, 
   calendar_week_in_month tinyint,  
   -- Days
   calendar_day_in_year smallint, 
   calendar_day_in_week tinyint,  -- The first of the month 
   calendar_day_in_month tinyint, 
   mdy_name_long varchar(30), 
   mdy_name_long_with_suffix varchar(30), 
   day_name_long varchar(10), 
   day_name_short varchar(10), 
   -- Continuous Y/M/D, starts with the first day = 1 and keeps going.  Used for various dateadd functions.
   continuous_year tinyint,
   continuous_quarter smallint, 
   continuous_month smallint, 
   continuous_week smallint, 
   continuous_day int, 
   -- Custom
   description varchar(100), 
   is_weekend tinyint,      -- Tinyint and not bit so you can add the 1's. 
   is_holiday tinyint,      -- Tinyint and not bit so you can add the 1's. 
   is_workday tinyint,      -- Tinyint and not bit so you can add the 1's. 
   is_event tinyint)        -- Used to indicate any special event days. 
GO

-- Create the table, with dates ranging from 2010 to 2020.  Change to suit your needs.
Declare @dt_start date = '2015-01-01', @dt_end date = '2020-12-31', @total_days int, @i int = 0
SELECT @total_days = DATEDIFF(d, @dt_start, @dt_end) 

WHILE @i <= @total_days
   begin
   INSERT INTO days (PKDate) 
   SELECT CAST(DATEADD(d, @i, @dt_start) as DATE) 

   SET @i = @i + 1
   end
   
 ;
 
 -- These values can be generated with single SQL Server functions
UPDATE days
SET 
   calendar_year = YEAR(PKDate), 
   calendar_quarter = DATEPART(q, PKDate),
   calendar_month = DATEPART(m, PKDate), 
   calendar_week_in_year = DATEPART(WK, PKDate), 
   calendar_day_in_year = DATEPART(dy, PKDate), 
   calendar_day_in_week = DATEPART(Weekday, PKDate),
   calendar_day_in_month = DATEPART(d, PKDate),
   day_name_long = datename(weekday, PKDate)

-- These values need either logic, customization in functions, or customization based on client needs.
UPDATE days
SET 
   is_weekend = CASE DATEPART(weekday, PKDate) WHEN 1 THEN 1 WHEN 7 THEN 1 ELSE 0 END, 
   calendar_quarter_desc =  'Q' + CAST(calendar_quarter as char(1)) + ' ' + CAST(calendar_year as char(4)), 
   calendar_month_name_long = DATENAME(m, PKDate) + ' ' + CAST(calendar_year as CHAR(4)),
   mdy_name_long = DATENAME(m, PKDate) + ' ' + CAST(calendar_day_in_month as varchar(2)) + ', '  + CAST(calendar_year as CHAR(4)),
   day_name_short = LEFT(datename(weekday, PKDate),3)
   
-- Month name:  The first three letters of the month.
UPDATE days
SET calendar_month_name_short = LEFT(DATENAME(month, PKDate),3), is_holiday = 0
;  

Open in new window

record some dates as holidays which will be removed (along with weekends) from allocaton calculations
-- record "bank holidays" these are some UK dates from http://www.telegraph.co.uk/history/11622952/bank-holiday-facts.html
UPDATE days
SET is_holiday = 1
where PKDate IN (
  '20150101','20150403','20150406','20150504','20150525','20150831','20151225','20151228'
 ,'20160101','20160325','20150328','20150502','20150530','20150829','20151226','20151227'
 )

Open in new window

With the 4 tables and data, we can combine these to calculate forward load, which I have done per project, per resource , per month (& it could be done per week or quarter for example). The query I used:
select
     p.id
   , p.ProjectName
   , r.Resource
   , pr.fte
   , d.calendar_year
   , d.calendar_month
   , format(max(coalesce(pr.AllocStart, p.StartDate)),'yyyy-MM-dd') AllocFrom
   , format(max(coalesce(pr.AllocEnd, p.FinishDate)),'yyyy-MM-dd') AllocTo
   , sum(8 * pr.FTE) AllocHrs
from ProjectResources pr
inner join Projects p on pr.projectid = p.id
inner join Resources r on pr.resourceid= r.id
inner join dbo.days d on d.pkdate between coalesce(pr.AllocStart, p.StartDate) and coalesce(pr.AllocEnd, p.FinishDate)
                     and d.is_weekend = 0 and d.is_holiday = 0
group by
     p.id
   , p.ProjectName
   , r.Resource
   , pr.fte
   , d.calendar_year
   , d.calendar_month
order by 
     p.id
   , p.ProjectName
   , r.Resource
   , pr.fte
   , d.calendar_year
   , d.calendar_month
;

Open in new window

and the result of that query for the sample data is:
| id |    ProjectName | Resource | fte | calendar_year | calendar_month | AllocHrs |
|----|----------------|----------|-----|---------------|----------------|----------|
|  1 | Test Project 1 |  Staff 1 |   1 |          2015 |             10 |      176 |
|  1 | Test Project 1 |  Staff 1 |   1 |          2015 |             11 |      168 |
|  1 | Test Project 1 |  Staff 1 |   1 |          2015 |             12 |      168 |
|  1 | Test Project 1 |  Staff 4 | 0.5 |          2015 |             10 |       88 |
|  1 | Test Project 1 |  Staff 4 | 0.5 |          2015 |             11 |       84 |
|  1 | Test Project 1 |  Staff 4 | 0.5 |          2015 |             12 |       84 |
|  2 | Test Project 2 |  Staff 1 |   1 |          2015 |              9 |      176 |
|  2 | Test Project 2 |  Staff 1 |   1 |          2015 |             10 |      176 |
|  2 | Test Project 2 |  Staff 1 |   1 |          2015 |             11 |      168 |
|  2 | Test Project 2 |  Staff 1 |   1 |          2015 |             12 |      168 |
|  2 | Test Project 2 |  Staff 2 |   1 |          2015 |              9 |      176 |
|  2 | Test Project 2 |  Staff 2 |   1 |          2015 |             10 |      176 |
|  2 | Test Project 2 |  Staff 2 |   1 |          2015 |             11 |      168 |
|  2 | Test Project 2 |  Staff 2 |   1 |          2015 |             12 |      168 |
|  2 | Test Project 2 |  Staff 4 |   1 |          2015 |              9 |      176 |
|  2 | Test Project 2 |  Staff 4 |   1 |          2015 |             10 |      176 |
|  2 | Test Project 2 |  Staff 4 |   1 |          2015 |             11 |      168 |
|  2 | Test Project 2 |  Staff 4 |   1 |          2015 |             12 |      168 |
|  3 | Test Project 3 |  Staff 1 |   1 |          2015 |              9 |      176 |
|  3 | Test Project 3 |  Staff 1 |   1 |          2015 |             10 |      176 |
|  3 | Test Project 3 |  Staff 1 |   1 |          2015 |             11 |      168 |
|  3 | Test Project 3 |  Staff 1 |   1 |          2015 |             12 |      168 |
|  3 | Test Project 3 |  Staff 2 |   1 |          2015 |              9 |      176 |
|  3 | Test Project 3 |  Staff 2 |   1 |          2015 |             10 |      176 |
|  3 | Test Project 3 |  Staff 2 |   1 |          2015 |             11 |      168 |
|  3 | Test Project 3 |  Staff 2 |   1 |          2015 |             12 |      168 |
|  3 | Test Project 3 |  Staff 3 |   1 |          2015 |              9 |      176 |
|  3 | Test Project 3 |  Staff 3 |   1 |          2015 |             10 |      176 |
|  3 | Test Project 3 |  Staff 3 |   1 |          2015 |             11 |      168 |
|  3 | Test Project 3 |  Staff 3 |   1 |          2015 |             12 |      168 |
|  4 | Test Project 4 |  Staff 1 | 0.5 |          2016 |              1 |       80 |
|  4 | Test Project 4 |  Staff 1 | 0.5 |          2016 |              2 |       84 |
|  4 | Test Project 4 |  Staff 1 | 0.5 |          2016 |              3 |       88 |
|  4 | Test Project 4 |  Staff 4 | 0.5 |          2016 |              1 |       80 |
|  4 | Test Project 4 |  Staff 4 | 0.5 |          2016 |              2 |       84 |
|  4 | Test Project 4 |  Staff 4 | 0.5 |          2016 |              3 |       88 |

Open in new window

& now that I have used the dbms to generate the numbers I could use a spreadsheet to transpose the data into monthly columns instead of rows for presentation and probably some graphs.

Working demonstration: http://sqlfiddle.com/#!6/54345/4
0
Conor_NewmanAuthor Commented:
Exactly what I was looking for! Thank you.
0
Conor_NewmanAuthor Commented:
@Paul Maxwell, Definitely a database is the ultimate goal, but I have to generate a report from this for Monday, so need to do it manually in Excel right now, but your solution above is excellent, and would have been a nother question in a few weeks. Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.