Suggestions for formula for calculating time allocation

collinsc

Board Regular
Joined
Sep 13, 2006
Messages
230
Hi All

This is a long one/complicated one... (for me anyway!)

I have been producing a spreadsheet to show resource availability for people on work projects. i.e. what they are doing with their time, when will they become free for more work.

I have been helped in previous posts to work out the formulas used for this. (thanks!).

Now i have a list of names and projects, and time they are spending on the project within a particular month.

Now i want to report on:
a) how many people are working on the project in a particular month
b) the percentage of time spent on the project compared to time spent on other projects.

I would suggest i need to
a) create a calculation/formula of no of employees divided by number on a particular project. (word specific?)
b) create a calculation/formula of time per individual working on that project compared to others times NOT working on the project.

I know it sounds complicated and i hope i have explained it sufficiently to get some responses..!?

Thanks very much

Chris
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
It would be helpful ( for me anyway) if you could provide some data to work with and what you have already.

My first thoughts are that a pivot table might do what you are after
 

collinsc

Board Regular
Joined
Sep 13, 2006
Messages
230
Hi GorD

Thanks for responding.

I am at work at the moment, so am unable to send a copy of the spreadsheet ( i can do this- this evening..)

I am also unable to download the software to enable me to upload the sheet from work.

Is there any sort of data i could provide via this HTML box..? (for now..)

regards
 

collinsc

Board Regular
Joined
Sep 13, 2006
Messages
230
Ok, ive managed to produce an example of data...

Name Project ID Project Task SEP OCT NOV

Mark A123456 Finance Analysis 0.5 0.6 0.2
Tim B543212 Marketing Analysis 1 1 1
John ZZZZZZZ Sales Program 0.6 0.2 0.1
Harry A123456 Finance Analysis 0.5 0.6 0.3
Jimbob B543212 Marketing Analysis 1 1 1
Gary A123456 Finance Analysis 0.6 0.2 0.4

0.5 represents 50% of his time allocated, where 1 represents 100%.
Another worksheet will therefore state that in september Mark is free for 50% of that month- as he is only allocated to 0.5. And Tim will be unavailable SEP-NOV as he is allocated 100%.

For example project A123456 is being worked on by 3 people.
Firstly i want to identify- in a formula- how many people are working on A123456.
Secondly, i want to identify what percentage of resource has been allocated to that project compared to the other projects.(i.e.for SEP 0.5,0.5,0.6 compared to 1, 1 and 0.6 for other projects). So i can say "20% of our resource currently is allocated to project A123456"

Thanks......!
 

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446

ADVERTISEMENT

Not sure if this is going to help much, but if you can turn your data into a list format, rather than the tabular style.
Also it would be easier if everybodies time was allocated including non contract time.
Book1
ABCDE
1NameContractJob TypeMonthHrs
2MarkA123456Finance AnalysisSep-060.5
3TimB543212Marketing AnalysisSep-061
4JohnZZZZZZZSales ProgramSep-060.6
5HarryA123456Finance AnalysisSep-060.5
6JimbobB543212Marketing AnalysisSep-061
7GaryA123456Finance AnalysisSep-060.6
8MarkA123456Finance AnalysisOct-060.6
9TimB543212Marketing AnalysisOct-061
10JohnZZZZZZZSales ProgramOct-060.2
11HarryA123456Finance AnalysisOct-060.6
12JimbobB543212Marketing AnalysisOct-061
13GaryA123456Finance AnalysisOct-060.2
14MarkA123456Finance AnalysisNov-060.2
15TimB543212Marketing AnalysisNov-061
16JohnZZZZZZZSales ProgramNov-060.1
17HarryA123456Finance AnalysisNov-060.3
18JimbobB543212Marketing AnalysisNov-061
19GaryA123456Finance AnalysisNov-060.4
Sheet1


See next post for pivot tables
 

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
You can then use pivot tables to summarise how you like
Book1
ABCDEF
21Contract View -(you can take the names out if want)
22Count of HrsMonth
23ContractNameSep-06Oct-06Nov-06Grand Total
24A123456Gary1113
25Harry1113
26Mark1113
27A123456 Total3339
28B543212Jimbob1113
29Tim1113
30B543212 Total2226
31ZZZZZZZJohn1113
32ZZZZZZZ Total1113
33Grand Total66618
34
35This would show FREE TIME
36Sum of HrsMonth
37NameSep-06Oct-06Nov-06
38Gary60%20%40%
39Harry50%60%30%
40Jimbob100%100%100%
41John60%20%10%
42Mark50%60%20%
43Tim100%100%100%
44
45Sum of HrsMonth
46ContractSep-06Oct-06Nov-06Grand Total
47A12345638.10%38.89%30.00%36.11%
48B54321247.62%55.56%66.67%55.56%
49ZZZZZZZ14.29%5.56%3.33%8.33%
50Grand Total100.00%100.00%100.00%100.00%
Sheet1


Only problem seems to be that not all the time is accounted for so the %ages are not what you might need.
 

collinsc

Board Regular
Joined
Sep 13, 2006
Messages
230

ADVERTISEMENT

Thanks for doing this...
I will look at it in more depth when i am back in work on Tuesday.

Cheers for all your effort.
 

collinsc

Board Regular
Joined
Sep 13, 2006
Messages
230
GorD

I have had more time to look at this now- and it seems ideal!

I assume i will have to download HTMLMaker2.42 to view the formulas will i?

thanks
 

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
No ,there are no formulas - it's all done with pivot tables.

Unfortunately, I think I will have overwritten the spreadsheet I created by now.


But if you have other questions fire away, and I or someone else on the board will do their best to answer.
 

collinsc

Board Regular
Joined
Sep 13, 2006
Messages
230
Hi GorD (or anyone)

Can you please confirm that i need to download the HTML Maker?
I cant see where i need to dl it from...!/ help... again!

cheers
 

Watch MrExcel Video

Forum statistics

Threads
1,114,673
Messages
5,549,355
Members
410,910
Latest member
DessertDiva
Top