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.

Try this. It seems to work according to my understanding of what you want.

Here's the formula:

=COUNTIF(B$3:B$9,A10)+IF(SUMPRODUCT(--(ISNUMBER(SEARCH(A10,B$3:B$9))))-COUNTIF(B$3:B$9,A10)0,1,0)/IF(SUMPRODUCT(--(ISNUMBER(SEARCH(A10,B$3:B$9))))=0,1,SUMPRODUCT(--(ISNUMBER(SEARCH(A10,B$3:B$9)))))

And here's a screencap to verify that the results are what you're looking
for:

http://img373.imageshack.us/img373/5...rojects5cs.jpg

Biff

"Biff" wrote in message
...
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.

Hey Biff - this is a really cool formula!! Thanks for the thought into this

Just so I understand what it is doing (referencing your screenshot):
My understanding of the Proj1 number (b10) is that the total would be 2.5
(.5 of Kevin's time, 0.5 of Lisa's time, 0.5 of Sam's time and 1 of Bill's
time). However this formula gets me 1.25.

Similarly Proj 2 in B11 should be 1 (0.5 of John's time, 0.5 of Lisa's time)
and Proj3 (b12) should be 1.5 (0.5 of Kevin's time, 0.5 of John's time and
0.5 of Sam's time)

Am I missing something in the formula?

"Randhir" wrote in message
...
Hey Biff - this is a really cool formula!! Thanks for the thought into
this

Just so I understand what it is doing (referencing your screenshot):
My understanding of the Proj1 number (b10) is that the total would be 2.5
(.5 of Kevin's time, 0.5 of Lisa's time, 0.5 of Sam's time and 1 of Bill's
time). However this formula gets me 1.25.

Similarly Proj 2 in B11 should be 1 (0.5 of John's time, 0.5 of Lisa's
time)
and Proj3 (b12) should be 1.5 (0.5 of Kevin's time, 0.5 of John's time
and
0.5 of Sam's time)

Am I missing something in the formula?

Well, I guess I didn't understand afterall!

Your explanation above makes it clear. Hmmm...... this isn't going to be
complicated, it's going to be extremely complicated!!!

Let's see if I can figure it out. No guarantees!

Biff

Ok, I'm taking the easy way out on this!

Use a helper column (which you can hide if you don't want to see it) and it
becomes very easy:

Screencap:

http://img101.imageshack.us/img101/4...ntproj23lt.jpg

Biff

"Biff" wrote in message
...
"Randhir" wrote in message
...
Hey Biff - this is a really cool formula!! Thanks for the thought into
this

Just so I understand what it is doing (referencing your screenshot):
My understanding of the Proj1 number (b10) is that the total would be 2.5
(.5 of Kevin's time, 0.5 of Lisa's time, 0.5 of Sam's time and 1 of
Bill's
time). However this formula gets me 1.25.

Similarly Proj 2 in B11 should be 1 (0.5 of John's time, 0.5 of Lisa's
time)
and Proj3 (b12) should be 1.5 (0.5 of Kevin's time, 0.5 of John's time
and
0.5 of Sam's time)

Am I missing something in the formula?

Well, I guess I didn't understand afterall!

Your explanation above makes it clear. Hmmm...... this isn't going to be
complicated, it's going to be extremely complicated!!!

Let's see if I can figure it out. No guarantees!

Biff

Biff this is awesome!! Thanks a ton - a bunch of people I asked for help told
me it could not be done, but you proved them wrong!! This works beautifully.

You're welcome. Thanks for the feedback!

BTW, the formula in column C can be further simplified to:

=IF(B3="",0,1/(LEN(B3)-LEN(SUBSTITUTE(B3,",",""))+1))

I think I used Sumproduct because I still had "arrays" on my mind!

Biff

"Randhir" wrote in message
...
Biff this is awesome!! Thanks a ton - a bunch of people I asked for help
told
me it could not be done, but you proved them wrong!! This works
beautifully.