Calc cost for main resources without using Std Rate & Cost Rate Ta

[Kevin]

I believe I am very close to a solution to correctly calculating cost for our
resources without using the Std Rate and Cost Rate table. (We are using the
Std Rate and Cost Rate for billing as we need to adjust the billing rate on
the fly at any given time within a project but cost remains constant by
assigned resource and I would like to store the cost associated to a resource
in the global resource pool).

The process I am trying to use for this is creating two formula fields in
the Gantt Chart using enterprise number 1 and enterprise number 2 that
accounts for every Resource.

Formula Details: Using the field enterprise number1 and entering
"IIf(InStr(1,[Resource Names],'BrianL'),65,0) " to give each resource a cost
value in the Gantt Chart. To compute the other half of the formula I am
using the enterprise number 2 field and take the result of enterprise number
1 and does the "([Work]*[Enterprise Number1])/60" part. This works perfect
for one resource although I am not successfully getting it to work with
multiple resources.

For multiple resources the only formula I am changing is enterprise number 1
which now is (IIf(InStr(1,[Resource Names],'BrianL'),65,0) And
IIf(InStr(1,[Resource Names],'AlanW'),25,0))). The problem is that for
multiple resources this formula only seems to be adding the work total for
all assigned resources and it is displaying it in a negative number (i.e.->
If AlanW and BrianL's cumulative work total is 13 hours the second formula
will display -13 for the result instead of (($65 * 10 hours)/60) + ($25 * 3
hours)/60)).

Does anyone have any ideas for me as I believe the issue is in the second
formula? Thanks!

 

[John]

I believe I am very close to a solution to correctly calculating cost for our
resources without using the Std Rate and Cost Rate table. (We are using the
Std Rate and Cost Rate for billing as we need to adjust the billing rate on
the fly at any given time within a project but cost remains constant by
assigned resource and I would like to store the cost associated to a resource
in the global resource pool).

The process I am trying to use for this is creating two formula fields in
the Gantt Chart using enterprise number 1 and enterprise number 2 that
accounts for every Resource.

Formula Details: Using the field enterprise number1 and entering
"IIf(InStr(1,[Resource Names],'BrianL'),65,0) " to give each resource a cost
value in the Gantt Chart. To compute the other half of the formula I am
using the enterprise number 2 field and take the result of enterprise number
1 and does the "([Work]*[Enterprise Number1])/60" part. This works perfect
for one resource although I am not successfully getting it to work with
multiple resources.

For multiple resources the only formula I am changing is enterprise number 1
which now is (IIf(InStr(1,[Resource Names],'BrianL'),65,0) And
IIf(InStr(1,[Resource Names],'AlanW'),25,0))). The problem is that for
multiple resources this formula only seems to be adding the work total for
all assigned resources and it is displaying it in a negative number (i.e.->
If AlanW and BrianL's cumulative work total is 13 hours the second formula
will display -13 for the result instead of (($65 * 10 hours)/60) + ($25 * 3
hours)/60)).

Does anyone have any ideas for me as I believe the issue is in the second
formula? Thanks!

Kevin,
Maybe I'm missing something but why not put a single formula in a spare
cost field on the Resource Sheet of the resource pool? You can either
hard code each resource's rate into the formula or set up a spare cost
field from which to grab that resource's cost.

If necessary the data can be transferred to the Gantt Chart view in a
spare cost field with a simple VBA macro (see FAQ 37 on our MVP website
at, http://www.mvps.org/project/faqs.htm).

Hope this helps.
John
Project MVP