Custom Column Calculation

Question

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

Answer 1

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+.)