Working with Formulas in Microsoft Project

Microsoft Project allows you to create and use formulas to customize task fields, calculate values, and derive insights from your project data. This article provides a guide on working with formulas in Microsoft Project, with practical examples.

What Are Formulas in Microsoft Project?

Formulas in Microsoft Project are expressions that calculate values for custom fields based on existing data. They use basic arithmetic, logical operators, and built-in functions to create dynamic calculations.

Creating a Formula

To create a formula in Microsoft Project:

  1. Go to the "Project" menu and select "Custom Fields."
  2. Choose the type of field (Task, Resource, or Project) and the data type (e.g., Text, Number, or Date).
  3. Click "Formula" and enter your formula in the editor.
  4. Click OK to apply the formula.

Example Formulas

Here are some example formulas and how to use them in Microsoft Project:

1. Calculating Total Cost

To calculate the total cost of a task, including fixed and resource costs:

Total Cost = [Fixed Cost] + ([Work] * [Standard Rate])

Steps:

  1. Create a custom number field called "Total Cost."
  2. Enter the formula: [Fixed Cost] + ([Work] * [Standard Rate]).
  3. Apply it to your tasks to display the total cost.

Example: A task with a fixed cost of $100, 20 hours of work, and a resource rate of $50/hour will have:

Total Cost = 100 + (20 * 50) = $1100

2. Calculating Slack Time

Slack time, or float, indicates how much time a task can be delayed without impacting the project finish date:

Slack = [Late Finish] - [Early Finish]

Steps:

  1. Create a custom number field called "Slack."
  2. Enter the formula: [Late Finish] - [Early Finish].
  3. Apply it to tasks to calculate slack time.

Example: A task with a Late Finish of 10 days and an Early Finish of 8 days will have:

Slack = 10 - 8 = 2 days

3. Flagging Over-Budget Tasks

To flag tasks exceeding their budget, use a formula to set a Yes/No flag:

Over Budget = [Actual Cost] > [Baseline Cost]

Steps:

  1. Create a custom flag field called "Over Budget."
  2. Enter the formula: [Actual Cost] > [Baseline Cost].
  3. Apply it to tasks. Over-budget tasks will be flagged as "Yes."

Example: A task with an Actual Cost of $1200 and a Baseline Cost of $1000 will return "Yes."

4. Calculating Task Progress

To calculate task progress as a percentage:

Progress (%) = ([Actual Duration] / [Duration]) * 100

Steps:

  1. Create a custom number field called "Progress."
  2. Enter the formula: ([Actual Duration] / [Duration]) * 100.
  3. Apply it to tasks to display progress as a percentage.

Example: A task with an Actual Duration of 5 days and a total Duration of 10 days will have:

Progress = (5 / 10) * 100 = 50%

Practical Example

Consider the following scenario for a construction project:

Task Name       | Fixed Cost | Work (hrs) | Standard Rate | Total Cost
--------------- | ---------- | ---------- | ------------- | ----------
Foundation      | $500       | 40         | $25/hour      | $1500
Superstructure  | $1000      | 60         | $30/hour      | $2800
Roofing         | $300       | 20         | $20/hour      | $700

The Total Cost formula calculates values automatically for each task.

Conclusion

Formulas in Microsoft Project provide powerful customization options for project planning and monitoring. By using custom fields and expressions, you can calculate metrics like cost, progress, and slack time to gain better insights into your project performance.




Advertisement