0

We use MS Project for work. I want to create a column that multiplies the cost of 'work' resources by 5% and 'cost' resources by 15% for each task line.

In Excel I know I could use an 'ifs' statement (If it is 'work' than x 1.05, if it is 'cost' then x 1.15, if it is neither then do nothing)

= ifs(A2="work", B2*1.05,A2="cost", B2*1.15,"")

I cannot figure out what I need to do in MS project. How do I create a calculation based on the type of resource - Do I need to make multiple columns and then sum them? or can I create a statement formula?

this is the formula I am trying, but of course I am getting errors IIf([Resource Type]="Work",[Cost]*1.05,IIf([Resource Type]="Cost",[Cost]*1.15))

thanks

0

You are getting errors because Resource attributes (e.g. Type) are not accessible to Task custom field formulas. Any queries would need to go through the task-resource assignments, and that requires vba.

A possible workaround in your case makes use of the “Resource Names” Task attribute – the list of all the resources assigned to the task. It requires some very special conditions, namely:

  • Each task has ONLY 1 resource assigned
  • Resource names or initials are intelligently coded – easiest with a prefix letter – to indicate the corresponding Resource Type. E.g. An initial “c” designates cost-type resources, like “c-Subcontract Invoice”.
  • Costs are only entered through resource assignments – i.e. no direct entries in the cost column.

If these conditions are assured, then you could use a formula like this in the Cost1 field:

=[Cost]*Switch(Left([Resource Names],1)="w",1.05,Left([Resource Names],1)="c",1.15,1,1)

(The Switch function is a streamlined alternative to nested IIf functions - like the new IFs function in Excel 2016+.)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.