Using formulas in custom fields

Suppose that your manager tells you that part of your evaluation in project management depends on the accuracy of your cost estimates. Under these circumstances, you may want to monitor the tasks for which actual cost exceeds baseline cost. You can set up a custom field to help you easily identify those tasks. Follow these steps to do so:

1. Choose ToolsOCustomizeOFields. Project displays the Customize Fields dialog box, shown previously in Figure 23-1.

2. Select either Task or Resource, and then open the Type list box and choose the type of field that you want to customize. For this example, I'll choose Task and Cost because I want to compare task cost values.

Tip The type of field that you select from the Type list matters in a different way when you're creating a formula instead of a value list. If you select the wrong type when * creating a value list, you can't set up the appropriate values for the list. However, if you select a type that doesn't match what you're trying to calculate in a formula, Project lets you create the formula but displays ERROR in the custom field column on the sheet. For example, suppose that you want to calculate a cost and you select Date from the Type list. Project still permits you to create the formula, but because the formula doesn't make sense, you see the message ERROR when you display the custom field column.

3. To provide a meaningful name for the code, click the Rename button and type the new name. You can't use any name that Project is already using; in the example, I named the field Difference. Then, click OK to redisplay the Customize Fields dialog box.

4. Click the Formula button to display the Formula for dialog box. Figure 23-5 shows the formula that I set up for this example.

Bcws Formula
Figure 23-5: This dialog box lets you define the values that you want Project to display in the pick list during data entry.

5. Create a formula in the text box by selecting fields or functions. To select a field, click the Field button; Project displays a list of field categories. Select the appropriate field category, and Project displays a list of the available fields, as shown in Figure 23-6. To select a function, follow the same process by clicking the Function button.

Actuai Cost Aetna] Overtime Cost ACWP

Baseline Cost t ecwr

BCWS Cost

Cost Variance CP I

cv% EAC

Enterprise Custom I :is 1 t

Enterprise Project Custom Cart ► Fixed Cost Overtime Cost Remaining Cost Remaining Overtime Cast SPI SV

TCP I

Figure 23-6: Select a field or a function to include in the formula.

In Appendix C, you find three tables. Table C-1 contains a list of all available Task fields, and Table C-2 contains the same information for Resource fields. Table C-3 lists the functions that you can include in a formula as well as a description of each function's purpose.

6. To make a calculation, use the operators that appear above the Field and Function buttons.

Tip If you've created this formula in another project, you can import the formula from the Global template (assuming that you saved the formula in the Global tem-* plate), or you can open the project that contains the formula before you create the formula in the new project. When you click the Import Formula button, Project displays a list of available templates or open Project files. Select the appropriate location, field type, and field name. Then click OK.

Formula

Cost ►

Edit fornida

Date >

Dlfferen

Duration *

[Actual

Flay ►

ID/Cude ►

Number ►

P reject »

jtJJ

Text ►

Insert:

Work ►

tfcp |

7. In the Formula for dialog box, click OK. Project warns you that it will discard any information that was previously stored in the custom field and replace the information with the calculated values based on the formula.

8. In the Customize Fields box, click OK to save the formula and redisplay the Customize Fields dialog box.

9. (Optional) Assign the formula to summary rows.

If you click OK at this point, Project calculates a value for the formula. You can see the value if you display the column for the custom field, as shown in Figure 23-7. Based on the formula that I created, positive values represent tasks where Actual Cost exceeded Baseline Cost — and technically, my Difference column is nothing more than the Variance column of the Cost table.

Custom field

Custom field

Figure 23-7: When you display the column for the Difference custom field, you see the result of the formula.

Suppose that you don't want to eyeball figures to find the problem tasks. You can insert icons to represent positive and negative values (and make the job of identifying the problem tasks much easier) by following these steps:

1. In the Custom Fields dialog box, highlight the custom field that you created and click the Graphical Indicators button, as shown in Figure 23-8.

Customize Tie Ids il

Custom Outlre codes

Customize Tie Ids

Custom Outlre codes

(* Task C Resource

Type : | Cost

d

Difference (Costl)

Cost2

Cost3

Cost4

Cost5

Costé

Cost?

CostS

Cost9

CostlO

Irrtport custom Field. ..

Irrtport custom Field. ..

Cetculflticn Fur task and group summary rows

P Wone p Rclkjp ^ Values to dsplay <* Data C Graphical InrScatcfs..

Graphical Indicators button

Figure 23-8: Click this button to assign icons to custom fields.

2. Choose the type of row to which you want to assign an indicator: Nonsummary, Summary, or Project summary.

3. In the Test for section, set up the test that Project should use. In each column, you can choose from lists. In the Value(s) column, you can compare the formula result to the value of another field or to a numeric value, as shown in Figure 23-9. In my example, if the result of the formula is greater than 0, I want to see a red flag, because the actual cost of the task exceeds the baseline cost. If, however, the actual cost is less than or equal to the baseline cost, things are fine, so I want to see a happy face (you can find other indicator choices in the Image list).

Tip If you've set up graphical indicators in another project, you can import the criteria from either the Global template (assuming that you saved the formula in the * Global template) or from another open project. When you click the Import Indicator Criteria button, Project displays a list of available templates or open Project files. Select the appropriate location, field type, and field name. Then click OK.

Graphical Indicators for "Difference"

Indcatcr criteria for

^ortummary rows; C Summary rows

C Project summary p .-jc-.' | "iher •■ ... r.t-r , i, , i-.i ulnar .. I >'

GJ Row | Copy Row | Paste Row | Insert Row | Delete Row [

^ortummary rows; C Summary rows

C Project summary p .-jc-.' | "iher •■ ... r.t-r , i, , i-.i ulnar .. I >'

GJ Row | Copy Row | Paste Row | Insert Row | Delete Row [

is greater than

Test for 'Difference'

Vaiue(s)

Image

N

is greater than

0

r

is less than or equei to

0

©

To disptey graphical indicators in place of actual data values, specify the v-sius range for each indicator and the image to lisplay. Tests are applied in the order listed and processing stops at the first successful test,

17 Show data values ri ToolTjps

Move jJ

To disptey graphical indicators in place of actual data values, specify the v-sius range for each indicator and the image to lisplay. Tests are applied in the order listed and processing stops at the first successful test,

17 Show data values ri ToolTjps

Import Indicator Criteria.., | | OK j Cancel j

Figure 23-9: Set up the test that Project should perform on the formula's result and the indicator to display, based on the test results.

4. To see the mathematical results of the formula in a ToolTip when you point the mouse at the indicator, select the Show data values in ToolTips check box.

5. Click OK to redisplay the Customize Fields dialog box.

6. Click OK again to redisplay your project.

If necessary, display the column for the custom field. Right-click the column that you want to appear to the right of the custom field, and choose Insert Column. Then, select the custom field. When Project displays the column, it will contain an indicator, as shown in Figure 23-10. If you selected the Show data values in ToolTips check box, you can point the mouse at an indicator to see the results of the formula. (The ToolTip hides the icon on-screen; otherwise, I would show you the ToolTip.)

Figure 23-10: The custom field column contains an indicator.

Was this article helpful?

0 0
Project Management Made Easy

Project Management Made Easy

What you need to know about… Project Management Made Easy! Project management consists of more than just a large building project and can encompass small projects as well. No matter what the size of your project, you need to have some sort of project management. How you manage your project has everything to do with its outcome.

Get My Free Ebook


Responses

  • SISKO
    How to show an addition equation in custom fields projects?
    1 year ago
  • Melba Lightfoot
    Can you add a formula to a msproject field?
    6 months ago
  • markus
    How to read formulas in ms project custom fields?
    6 months ago
  • lotta tuukkanen
    How to create custom fields in word with a formula?
    6 months ago
  • amina amanuel
    How to create formula field in project microsoft?
    5 months ago
  • drogo
    Can you use multiple formulas in a custom field in ms project?
    3 months ago

Post a comment