One of the changes in Microsoft’s Project 2000 is in the way Task Priority is represented. In 98, there were text-based priority values. You were given 10 choices and that was it. In 2000, you get a numerically based system that gives you 1000 choices. This gives you greater control over priorities as well as how certain tasks relate to each other (for leveling, for example). The tough part is that many people, including some IT pros, got used to looking at the text-based values in 98. We all know what this means; now your users will be upset that there is a change in the new version of the software, and they’ll want you to fix it. Luckily, one of the other new features in 2000 can help fix the problem.

Custom field formulas
Project 2000’s new custom field formula feature will let you create a ”dummy” priority field that will display the old 98 priorities based on the value of the ”real” priority field. Simply insert a custom text field (Text1, for example). Then right-click on it and select Customize Fields from the menu. In this next dialog box, click on the Formula button. You can then paste in one of the following formulas.

This first formula will replicate exactly the mapping between the 98 and 2000 values. (See Table A below.) While this formula is technically correct, it cannot take advantage of the numbers in between the exact map values. For example, a priority of 501 would result in a value of NA with this formula because the formula uses the “=” operator, and 501 does not equal any of the values in this formula.
IIf ([Priority] = 1000, “Do Not Level”, IIf ([Priority] = 900,”Highest”, IIf ([Priority] = 800, “Very High”, IIf ([Priority] = 700, “Higher”, IIf ([Priority] = 600, “High”, IIf ([Priority] = 500, “Medium”, IIf ([Priority] = 400, “Low”, IIf ([Priority] = 300, “Lower”, IIf ([Priority] = 200, “Very Low”, IIf ([Priority] = 100, “Lowest”, “NA”))))))))))

Table A: Priority Mapping from 98 to 2000
2000 Value 98 Value
1000 Do Not Level
900 Highest
800 Very High
700 Higher
600 High
500 Medium
400 Low
300 Lower
200 Very Low
100 Lowest

This next formula will be a little more forgiving. It will, for example, make Text1 equal “Do Not Level” if the Priority field is equal to 1000. If Priority is greater than 900, but less than 1000, then Text1 will equal “Highest.” Any Priority less than 100 would get the value “Lowest” in Text1. This is a more realistic value calculation because it is more flexible.
IIf ([Priority] = 1000, “Do Not Level”, IIf ([Priority] > 900, “Highest”, IIf ([Priority] > 800, “Very High”, IIf ([Priority] > 700, “Higher”, IIf ([Priority] > 600, “High”, IIf ([Priority] > 500, “Medium”, IIf ([Priority] > 400, “Low”, IIf ([Priority] > 300, “Lower”, IIf ([Priority] > 100, “Very Low”, IIf ([Priority] > 0, “Lowest”, “NA”))))))))))

Both of these formulas are good examples of what you can do with the new custom field functionality in Project 2000 and how this feature can be used to solve real problems by basically adding your own new features.

They can also both be customized to have your own text inserted instead of the Project 98 standards. The second formula can also be adjusted so that the tolerances match your own preferences. It is your project; make the formula your own.

The IIF Function is your friend
The IIF function is a very powerful tool that can be used with the custom field formula functionality because it lets you evaluate other field values and specify the outcome of the formula depending upon those other values. The syntax for this function is as follows:
IIf( expression, truepart, falsepart )

where Expression represents the fields you want to evaluate([Work] > 50 for example),Truepart represents the value returned by the function if the expression is True, and Falsepart represents the value returned by the function if the expression is False.

For example, if you entered the formula below, the function would return the value “True”:
IIf(2+2=4, “True”, “False”)

However,  IIf(2+2=5, “True”, “False”) would return the value “False.”

The formulas I discussed above that deal with the Priority field use “nested” IIF functions to evaluate multiple possible values in one formula. Creating nested IIF functions can be a bit tricky, but once you get the hang of it, they are very powerful. The key is to start at one “end” of the possible values and work your way to the other end.
IIf ([Priority] = 1000, “Do Not Level”, IIf ([Priority] > 900, “Highest”, IIf ([Priority] > 800, “Very High”, IIf ([Priority] > 700, “Higher”, IIf ([Priority] > 600, “High”, IIf ([Priority] > 500, “Medium”, IIf ([Priority] > 400, “Low”, IIf ([Priority] > 300, “Lower”, IIf ([Priority] > 100, “Very Low”, IIf ([Priority] > 0, “Lowest”, “NA”))))))))))

In this formula, I started at the top, 1000, and worked down to 0. The first IIF evaluates the value of the Priority field, and if it equals 1000, then it should return “Do Not Level.” Then in the “Falsepart” argument of the function, I placed another IIF to make the Falsepart dependent on another evaluation of the Priority field. I continued to place new IIF’s in the Falsepart argument until I got to the last IIF, where I placed “NA” in the Falsepart. By doing this, Project will evaluate the Priority field against each of these nested criteria until one of them is true. If none of the values are true, then it will return NA. (In the case of this formula, it will never be NA because the Project 2000 Priority field must be a number between 1 and 1000.)

The samples I have given show the IIF as the entire formula itself, but the result returned from an IIF function can also be used as part of another. For example, I could use it in the following formula:
2 * IIf ([Priority] > 500, 2, 0)

Here, the field would equal 4 if the Priority field was greater than 500, and it would equal 0 if Priority was less than 500. This can also be done with the concatenation of text strings as shown below.
“This task ” & IIf ([Milestone] = True, “is”, “is not”) & ” a Milestone”

If the task was a milestone, the formula above would return: “This task is a milestone.” But if Milestone was False, then it would return, “This task is not a milestone.”

Rate this article!

What do you think of Brian’s Project 2000 tip? Will it help you support Microsoft Project 2000 more effectively? Post a comment or write to Brian Kennemer and share your opinions.