Suggestions for formula for calculating time allocation

collinsc

Board Regular
Joined
Sep 13, 2006
Messages
240
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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
 
Upvote 0
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
 
Upvote 0
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......!
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

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