Suggestions for formula for calculating time allocation

[collinsc]

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

 

[GorD]

Down loading the HTML maker would be a good idea for future postings, but it won't help show formulas from my postings above.

Have a quick read up on pivot tables and if you need help then ask away.


There is a "sticky" posting immediatley prior to the first posting on the excel questions forum, explaining about the HTML maker

 

[collinsc]

Oh ok...
no formulas!! oh...
ive never used a pivot table before... how then does the 2nd ssheet you have done (above) get data from the first ssheet?
(ive never used pivot tables before)

 

[collinsc]

Thanks, ill have to read up about pivot tables i guess!
cheers

 

[collinsc]

GorD
I have read up a bit on pivot tables- although not on XP.

a few Q !

1. In your pivot table- how have you worked out the % on sum of hours? do you have to specify % as a format...

2. The way my excel ss is set up- when i create a pivot table the field list includes EVERY month "sep 06" "oct 06"... Is that right?

You have put the months in a column, whereas mine is in rows- all across the top til dec 07. and then times are allocated dependant on task, person and project...


is this overcomeable?

Cheers

 

[GorD]

Hi again,

Just to clarify my earlier posting showed 3 separate pivot tables all working of the same base data,

In the second table the numbers are just formatted as %ages. Right click in the pivot table, then field settings and you should see a number option.

In the 3rd table it's slightly different ( from memory). If you right click one of the data items and again select field settings you should see an options button. If you select that, about half way down the menu you should see a show data as option. This allows you various ways of displaying the data ( %age of row, %age of column etc) Can't remember which I used.

Could you expand on Q2 a bit as I'm not sure I follow

 

[collinsc]

Thanks GorD.

You had ALL the months in one column, and i had a seperate column for each month- i wondered if it made a difference.

I was trying to do a massive pivot table showing all the months- but i may aswell do a pivot table on amonthly basis "as and when" and then perhaps i could do a quarterly one.

Cheers

 

[collinsc]

Hi again GorD !
I am getting to grips with this pivot tabl'in
I am stuck though on the project % calculation. How did you do it?

As a pivot: I have the names down the left, the prj no across the top and the data is the months details... I have set it to % however- this shows the % of prj 'distribution' i.e. on ss above- gary is working on 3 prj's so it shows as 33% under each prj. EVEN THOUGH one orj might be allocated as 0.1 and the other as 0.8.

So its a % of their prj split rahter than % of resourced time to the prj. some how i need to get the resourced figures involved.

Any suggestions!?!?

thanks

 

[collinsc]

GorD

Scrap that- think ive just done it!

I changed the field setting to "sum" % rather than "count" %....

sound about right!?

 

[GorD]

Sounds pretty good. Have you ever managed to download that HTML maker and you could let us see what you have?

 

[collinsc]

GorD

I have posted another question on Pivot Tables (im not sure if you are around to answer it).

Thanks so much for all of your help- ive nearly completed my task.