I am trying to use Excel to do my manpower allocation and planning. So I have
a column of employees and a row of months. I then put in the project name
that each is working on in a given month.

then at the bottom, I just sum the number of people working on a particular
project in a month and do a simple calculation between the number of
available people and the number who are allocated. This works beautifully IF
each person only works on one project in a month. As we know, this is an
unreleastic assumption!

What I would like is for me to put in multiple project names in a cell and
for the 2nd table to be smart - i.e. if I put in Project A & B for John, then
it should count John as 0.5 for each A & B not 1 for each A&B. Similarly if
John is working on 3 projects, then it should count only 0.33 for each
project John is working on.

I know I could put all this data into a database type table in XL, but I
like the ease of use and visual appear of this approach!

Any suggestions would be much appreciated!

Randhir

Hi!

This would be a piece of cake if you were to put multiple project names in
multiple cells.

Then it could be something along these lines:

=1/COUNTIF(Range,"*")

If you put multiple project names in a single cell then you'd have to
delimit them with maybe a comma or space then you could count those
delimiters:

A1 = Proj1, Proj2, Proj3

=1/(LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1)

Biff

"Randhir" wrote in message
...
I am trying to use Excel to do my manpower allocation and planning. So I
have
a column of employees and a row of months. I then put in the project name
that each is working on in a given month.

then at the bottom, I just sum the number of people working on a
particular
project in a month and do a simple calculation between the number of
available people and the number who are allocated. This works beautifully
IF
each person only works on one project in a month. As we know, this is an
unreleastic assumption!

What I would like is for me to put in multiple project names in a cell and
for the 2nd table to be smart - i.e. if I put in Project A & B for John,
then
it should count John as 0.5 for each A & B not 1 for each A&B. Similarly
if
John is working on 3 projects, then it should count only 0.33 for each
project John is working on.

I know I could put all this data into a database type table in XL, but I
like the ease of use and visual appear of this approach!

Any suggestions would be much appreciated!

Randhir

Hi Biff,

Thanks for your quick response. The multiple values are in the same cell, so
your second suggestion works well. However if I am trying to sum across a
range, I am stuck. I am not sure if there is a way to put this formula into
another one to get it to function across a range. For example:

Jan Feb Mar
Kevin Proj1, Proj2 Proj1, Proj2 Proj1, Proj2
John Proj1 Proj1 Proj1
Paul Proj1, Proj2, Proj3 Proj1, Proj2 Proj1, Proj2

Proj1 1.8 1.8 1.8
Proj2 0.8 0.8 0.8
Proj3 0.3 0.3 0.3

Thanks,

Randhir

"Randhir" wrote in message
...
Hi Biff,

Thanks for your quick response. The multiple values are in the same cell,
so
your second suggestion works well. However if I am trying to sum across a
range, I am stuck. I am not sure if there is a way to put this formula
into
another one to get it to function across a range. For example:

Jan Feb Mar
Kevin Proj1, Proj2 Proj1, Proj2 Proj1, Proj2
John Proj1 Proj1 Proj1
Paul Proj1, Proj2, Proj3 Proj1, Proj2 Proj1, Proj2

Proj1 1.8 1.8 1.8
Proj2 0.8 0.8 0.8
Proj3 0.3 0.3 0.3

Not sure how you arrived at those figures. See if this screencap helps:

http://img164.imageshack.us/img164/4918/sample9it.jpg

Biff

Sorry the table did not come through the way I intended. The screenshot was a
good idea, so here is the table I tried to create:
http://static.flickr.com/75/166250309_a9648c0f7c_o.jpg

What I am trying to do is find out how many resources Proj1 is consuming.
Since Kevin is working on Proj1 and Proj2 in Jan, I am assuming it is 0.5
resources and since John is full time on it, the total resources for Project
1 is 1.5. I am now trying to find a way to automate this using your earlier
suggestion.

One clunky way is to create a parallel table structure using your formula
and then sum it up in the way I want it (a6:d7), but if there are other
simpler and more efficient ways to get there, I would love to learn about
them.

Ok, I think I understand what you want now. People per project rather than
projects per person which is what I was doing.

Hmmm...... this is going to be rather complicated. Let me see what I can do.

Biff

"Randhir" wrote in message
...
Sorry the table did not come through the way I intended. The screenshot
was a
good idea, so here is the table I tried to create:
http://static.flickr.com/75/166250309_a9648c0f7c_o.jpg

What I am trying to do is find out how many resources Proj1 is consuming.
Since Kevin is working on Proj1 and Proj2 in Jan, I am assuming it is 0.5
resources and since John is full time on it, the total resources for
Project
1 is 1.5. I am now trying to find a way to automate this using your
earlier
suggestion.

One clunky way is to create a parallel table structure using your formula
and then sum it up in the way I want it (a6:d7), but if there are other
simpler and more efficient ways to get there, I would love to learn about
them.