Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Custom Field Formulas not responding correctly 1

Status
Not open for further replies.

piedpiper11

Technical User
Apr 16, 2013
34
US
Hi all,

I'm having trouble getting custom field formulas to work consistently. Of the four I've created, only one works right, and I can't see any difference in what I've done for the other three.

Here's the situation: I am running a project (MS Project Pro 2013 on a Win7 system) that has to account to its sponsor with a detailed breakdown of all labor costs including Direct Labor (DL), Fringe Benefits (FB), Internal Overhead (IOH), and General & Adminstrative (GA). For each Resource, I have to start with, and have entered their Standard Rate/hr, and from that number and with a little math, all four of the sub rates can be determined. I have set up four custom fields in the Resource Sheet (using fields Number1 ... Number4). The multipliers are all the same for all resources. The FB multiplier is 25%, and the IOH and GA multipliers are both 7% each (the latter two multiply on the DL+FB rates combined, not just simply the DL rate). So to give a simplified example, if the Direct Labor rate for a Resource is $100.00/hr, the FB is $25.00/hr, and the IOH and GA rates are each $8.75/hr ($125 * 7%); and that all adds up to $142.50, which is what I start with in the Standard Rate. My budgeting process starts with the Standard Rate number, and I'm using the custom fields and formulas to work backwards to the other four numbers for each Resource. The Standard Rate is the total cost to the project, but there are required budget forms which also must show the DL/FB/IOH/GA breakdowns.

The first of the four pic attachments is a screen shot from my system showing the Resource Sheet and all the Work-type resources. You can see the four custom fields, and their formulas all result in the correct rates showing for each Resource. For example, the Number1 field (now called "DL Rate") has a formula "[Standard Rate]/1.14/1.25", which is the math needed to work back from the known Standard Rate.

The problem is that when I go to the Cost Table view in the Gantt Chart, I can't get 3 of 4 fields to show the resulting Totals in each Task. See the second of the four pic attachments. Total DL is the only one accurate (circled in green); the other three are wrong (circled in red)

In the Custom Fields dialog (see the third of four pics), the Cost1 field is being used for "Total DL". The formula in "Total DL" is "[Number1]*[Work]" and it's result in the Cost Table is correct. The formula in "Total FB" is "[Number2]*[Work]" and it just results in a zero for that field.

What am I missing? I can't fathom why three of the fields would show zeros, when the first one works fine, and they are all constructed in the same manner.

Screen Shot URLs
1) 2) 3) 4)

Scott
 
The Number1 field in the Resource Sheet is not the same as the Number 1 field in the Task view. Add the Number1, Number2, Number3, and Number4 fields to the Task Sheet and what values do they show? Each resource has a different DL rate - so I don't know how you can multiply work (total work on the Task) * a single rate to get the correct value. Really what you need to calculate is the cost per resource per task - and that would be calculating assignment values.

What would be more accurate is to calculate costs on a resource basis by calculating Work * the rate in the resource Sheet. Work on the Resource Sheet = total work on the project.

Julie
 
Ok, I see what you're saying: it sounds like Custom fields are limited only to their original context. I.e. the Number1 custom field in Resources isn't available in the Task area, and the Number1 custom field in the Task area isn't available in the Resources area. And I can understand your suggestion that I keep the DL/FB/IOH/GA calculations in the Resource area, and total them up across the whole project by resource. But I also need to see the DL/FB/IOH/GA totals *by task* - and particularly, I need to see them rolled up to the Level 1 tasks (where my seven formal Tasks in the project are shown). Is there really no way to get a Resource custom field accessible in the Task area, and vice versa?

Scott
 
I've created a simplified MPP file to study the problem and I think I can see part of the difficulty with my original approach: While the Standard Rate/hr field easily comes over to the Task Cost table on a *per resource* basis, I'm unaware of any other fields from Resource which also do that.

As seen on this screen shot:

I created the custom field in the Task table and told it to modify the cost (in this case by adding 25 cents to each task's cost). This is the Task Usage view, so it's broken out By Resource. The Cost field gives you the breakout (green circle), but it cannot show the breakout in the "Cost with FB" field (red circle), which is the custom (Number1) field I created.

My takeaway at the moment is that MS Project has limited the fields which can cross-over from the Resource to Task areas. Of course, I'm probably overlooking something, so I'd appreciate comments on whether my current conclusion is correct. Ultimately I'd really rather be able to specify some Resource-Rate-related variables, and have them all cross over to the task on a per-Resource basis. E.g., no zeros where the red circles are showing in the screen shot above.


Scott
 
You are correct - there are no cost or number fields which "cross" from the Task side (Gantt chart view, Task usage view) to the Resource side or the reverse. The Task Usage view show tasks and Assignments - not resources. Unfortunately, you cannot write formulas at the assignment level.

VBA is an option as both Rod and Andrew noted to your post on the TechNet site. You could also employ the additional cost rate tables for resources, change the rate table per assignments, and copy/paste the calculated values to Cost1, Cost2, Cost 3 etc.
 
After much gnashing of teeth, I finally decided to stop trying to divy out the sub-components of a Resource's standard rate in MSP. For reporting purposes, the data has to wind up in Excel anyway, and I'm just letting Excel back out the sub-rates based on formulas located there. The easiest solution as far as Project goes turns out to be exporting the data in a Pivot table out to Excel using the Assignment Usage option in Visual Reports Exports, which allows an association of Resource and Task data. Thanks for all the feedback. As I often find, one's initial assumptions about how to solve a complex problem wind up hiding the best/easiest solution, which doesn't become apparent until you've banged your head against several walls.

Scott
 
Apparent and Microsoft Project are not two phrases commonly used in the same sentence. Glad you've sorted it out - and sorry about the headache.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top