Forum Discussion
IIf formula in microsoft project custom field
I added the following custom field with the formula: [Remaining Work] / [Remaining Duration]
Now I want to add an "if" condition, meaning if a task is 100% complete, that no value or the value "n/a" is given.
I found the IIf function is the correct one, but I only get errors with the different ways I have tried to write the formula, e.g.: IIf ([% Complete] = 100, "n/a", [Remaining Work] / [Remaining Duration]) or
IIf ([% Complete] <100, ([Remaining Work] / [Remaining Duration]), "n/a")
Do you have any advise how to use the IIf formula?
- Nicol860 --
To create a formula that works correctly, I recommend that you use a custom Text field. That is because you want the "n/a" text to appear when a task is 100% complete. Also, you need the formula to test for a milestone, as the formula will result in a division by 0 error. Following is the updated formula, which I tested and confirmed that it works correctly in a Text field:
IIf([Milestone],"n/a",IIf([% Complete]=100,"n/a",[Remaining Work]/[Remaining Duration]))
Hope this helps.- Nicol860Copper Contributor
Dear Dale_HowardMVP, thank you very much for your help!
Unfortunately I can't get it to work, but do you know some resources (books, websites etc.) where I can learn more about how to write formulas in MS Project? I can't find much information online, does there just not exist much information?
Thank you very much for your help!
Kind regards,
Nicola
- Nicola -- "Unfortunately I cannot get it to work..." does not give us enough information to help you further. Did you copy and paste my formula into a task Text field? If so, the formula should have worked immediately. What did you do in response to my last message? And if it is generating an error, please include one or more screenshots to show us what you see.
Also, to the best of my knowledge, the only book on Microsoft Project formulas is in Portuguese, and was written by a colleague of mine. Do you speak/read Portuguese? 🙂