BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Have you ever been frustrated when you can't find a suitable field for entering the data you want in a Microsoft Project plan? Well why not customise your own field and enter anything from simple text, to drop down lists, to complex calculations and even graphical indicators!
- Custom Fields in Microsoft Project
- Creating Drop Down Lists with Custom Fields
- Creating Calculated Custom Fields
- Using Functions in Custom Fields (this blog)
- Custom Fields and Summary Tasks
- Using Graphical Indicators in Custom Fields
Posted by Andrew Gould on 01 August 2011
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Using Functions in Custom Fields
In the previous article in this series we saw how to create a simple calculation in a custom field. In this article we're going to look at how to use functions to make our calculated fields much more powerful.
How Functions Work
There are lots of functions available in Microsoft Project, but they all have very similar features, as shown in the diagram below
This diagram represents the structure of every function. The numbered items shown here are described below.
- The first part of a function is its name. This will always be a whole word with no spaces or punctuation characters contained in it.
- Immediately after the function name is an open bracket, or parenthesis. The corresponding parenthesis sits at the end of the function.
- Inside the parentheses are the bits of information that a function needs to work - these are referred to as arguments. Different functions have different numbers of arguments, and some have none at all (you still need the parentheses though).
- If there is more than one argument they must be separated from each other with a comma.
The last thing you need to know about a function is that however many arguments it requires, the function only returns a single piece of information. Armed with all this information we can now have a look at an example of a useful function.
The IIF Function
The function we're going to look at is called the IIF function. Its job is to test whether a condition has been satisfied and give one answer if it has, and a different answer if it hasn't. The structure of the IIF function is shown below:
- IIF - this is the name of the function (obviously!).
- Logical test - the first argument is the condition we're testing for and should be phrased in the form of a statement that can be either true or false.
- Answer if true - the second argument is the answer we want to get if the condition we've stated is true.
- Answer if false - the third argument is the answer we want to get if the condition we've stated is false.
We're going to use the IIF function to test whether the value in the Spend Variance field (which we created in the previous article in this series) is less than zero, and if it is we'll show a message saying that we've overspent on this task. If the Spend Variance isn't less than zero we'll show a message saying that we're within budget. To start doing this we'll need to customise a text field. Set it up as shown below:
Start setting up your field with these basic settings, as described below.
- Choose to customise a Task field.
- Change the data type to Text.
- Rename the field by clicking the Rename... button.
- Click this button to create the calculation.
Now we need to insert the IIF function into the dialog box that appears.
Inserting Functions Into A Formula
To add the IIF function into our formula we can either type it in manually, or we can insert it from the menu. The diagram below shows how to insert a function from the menu:
You can insert any of the available functions using the Function menu.
- Click the Function button.
- Select the category of function you need, here we're using the General category.
- Select the function you want to insert into the formula.
When you have done this, you should see the structure of the function you have chosen in the text box.
The function will be inserted with placeholder text for each argument.
The next job is to replace the placeholder text for each argument with the actual information we want to use. The table below shows what you should change:
|Replace this...||...with this||Comments|
|expression||[Spend Variance]<0||The open angle bracket symbol means "less than". The table below shows the other symbols you can use in a logical test like this.|
|truepart||"Overspent"||All literal text in a function needs to be enclosed within a set of double-quotes.|
|falsepart||"Within budget"||The double-quotes allow the function to distinguish between words that are the names of functions or fields, and words that are just bits of text.|
The end result should be a formula that looks like this:
Make sure that you don't accidentally remove the commas!
The other symbols that can be used in a logical test are shown in the table below:
|Symbol||What it means|
|<=||Less than or equal to|
|>=||Greater than or equal to|
|<>||Not equal to|
The End Result
Click OK to finish creating your formula, and then insert the field into a table. The diagram below shows what the results should look like:
The finished set of calculated fields.
The IIF function is just one example of a function in Microsoft Project, but there are many more available, the only difficult thing is working out what they all do!
So far, all of the fields we've created have only been used to enter or calculate values for sub tasks in our project. The next thing to consider is how these fields should work for summary tasks. Read the next part of this series to find out what our options are.