Custom Field Calc

[Cota]

I need to create a custom field to divide task Work by a
resource custom Number 1 field.

In the custom field formula area, Project gives an error
when I write: [Work]\[Number 1]. I'm assuming it's
because I'm dividing a task field by a resource field.

Is there a workaround?

Thanks.

 

[John]

Cota,
Yes. Probably the easiest way is to copy the Resource Number1 field over
to the Task Number1 field. Then the formula should work fine. However,
why do you want to divide Task Work by a value that is likely valid only
for a single resource? Even if you have one resource assigned to each
task, a simple copy operation won't get you there. The Resource Number1
value will need to be judiciously copied to the appropriate Task Number1
field. Certainly not an impossible task but a VBA macro is probably the
best approach. If you are not sure how to transfer information from a
Resource field to a Task field using VBA, take a look at FAQ 37 - custom
fields in tables. It is located on the MVP webpage at:
http://www.mvps.org/project/faqs.htm

Hope this helps.
John

 

[Cota]

I have fixed resources. The fixed resources produce work
at a fixed rate I have an allotted amount of work. What
I am trying to accomplish is, if I change the resource,
the rate at which the work is performed changes as well.

Example: Resource 1 works at 1,000 units/hr. Resource 2
works at 2,000 units/hr. I have 20,000 units of work to
accomplish. If resource 1 is the only resource available,
the work will be accomplished in 20 hours. If resource 2
becomes available, and I wish to change the assignment of
the work to Resource 2, the work should be accomplished
in 10 hours. I don't want to have to go into every
project line I change to reassign the Resource Units
(which would accomplish the same goal), because I could
end up spending hours doing nothing but recalculating
units. I don't need to estimate work rates, as they are
fixed. These resources are available and producing 24
hours per day. The work required is also a fixed, and is
imported from a table of Tasks.

If there is an alternate means of doing this, I am wide
open to suggestions. I am just now being introduced to MS
Project as a tool to assist in managing and posed this
question to my instructor. It just seemed to me that the
duration, in my instance, could be calculated, instead of
estimated, based on the resource fixed capability to
produce at a given rate.

 

[John]

Cota,
I better understand what you are trying to do. However, I don't know of
any way to reassign resources without being proactive. One way or
another you will have to address each task whose profile needs to be
changed. No simple formula in a custom field can do that.

The only alternate approach I can see is to develop an algorithm that
emulates the decision process for changing resource assignments. For
example, resource 2 is a premiumn resource that is only needed to
perform tasks that have certain parameters and resource 2 is only
available if a quota of X number of units is met in a certain time
frame. It can get pretty hairy but it is feasible. However, the only
practical way to accomplish this is through VBA and that may turn out to
be more effort than making the assignment adjustments manually.

Not sure if this helps.

John

 

[Steve House]

It almost sounds like you're trying to schedule equipment rather than
resources. Resources, in Project, are usually people, "skill packages" if
you like, and tasks are the physical activities, the work packages, they
carry out to create the project deliverables. Work is *not* units of
production, at least not directly. Rather it is the man-hours of labour
that the resource uses, a measure of energy expended, to create those units
of production. So in your example, R1 completes the required deliverable
using 20 man-hours of work while R2 completes the same deliverable burning
10 man-hours of work. I wonder if that is actually a valid scenario or if
there is a confusion between time and rate? If Bob and John are skilled
widget waxers (and if they're not they have no business being on the task in
the first place) it seems like when they work 100% they should each turn out
those 20k units in about an equal amount of time. I'm guessing, of course,
but could it be the reason that one appears to work faster than the other is
that the "slower" of them has other things going on that command part of his
attention that the "faster" one does not? If that's the case, resource
assignment units will account for the difference and adjusting them will
adjust your duration as you need. The *duration* is the amount of time that
it will take the assigned resource to burn up the required man-hours at the
rate he's actually able to manage. For illustration, let's say your best
resource, working at 100% of his capacity, can do the 20k widgets in 10
hours. So the base rate of widget production that 100% effort represents is
2k/man-hour and thus the required work to produce 20k widgets is 10
man-hours - very likely more-or-less independent of who the person is. If
you can get them full time without other responsibilities either one could
probably do it in about 10 hours. So you estimate the duration at 10 hours,
assign either R1 or R2, doesn't matter, at 100% and Project uses the
identity W=D*U to populate the work field with 10 man-hours. In other
words set up your initial schedule with the assumption of full-time
equivalents. Now if it turns out the resource you've selected is not
actually available to devote his full work efforts to the task at hand,
reduce his assignment units accordingly. R2 is actually only available for
half of his work day? Reduce his units to 50% (don't remove and reassign
him or someone else at 50%) and the duration required to do the 10 man-hours
will expand to 20 hours just as you want. The other resource is going to do
be doing it instead? Use the "Replace Resource" tool in the Resource
Assignment dialog box to switch to the other guy, then increase or decrease
the assignment units to fit his actually availability. Project will again
recalc duration.

Hope this helps