IIf formula in microsoft project custom field

%3CLINGO-SUB%20id%3D%22lingo-sub-2263383%22%20slang%3D%22en-US%22%3EIIf%20formula%20in%20microsoft%20project%20custom%20field%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2263383%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20added%20the%20following%20custom%20field%20with%20the%20formula%3A%20%5BRemaining%20Work%5D%20%2F%20%5BRemaining%20Duration%5D%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ENow%20I%20want%20to%20add%20an%20%22if%22%20condition%2C%20meaning%20if%20a%20task%20is%20100%25%20complete%2C%20that%20no%20value%20or%20the%20value%20%22n%2Fa%22%20is%20given.%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20found%20the%20IIf%20function%20is%20the%20correct%20one%2C%20but%20I%20only%20get%20errors%20with%20the%20different%20ways%20I%20have%20tried%20to%20write%20the%20formula%2C%20e.g.%3A%20IIf%20(%5B%25%20Complete%5D%20%3D%20100%2C%20%22n%2Fa%22%2C%20%5BRemaining%20Work%5D%20%2F%20%5BRemaining%20Duration%5D)%20or%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EIIf%20(%5B%25%20Complete%5D%20%26lt%3B100%2C%20(%5BRemaining%20Work%5D%20%2F%20%5BRemaining%20Duration%5D)%2C%20%22n%2Fa%22)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EDo%20you%20have%20any%20advise%20how%20to%20use%20the%20IIf%20formula%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2263846%22%20slang%3D%22en-US%22%3ERe%3A%20IIf%20formula%20in%20microsoft%20project%20custom%20field%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2263846%22%20slang%3D%22en-US%22%3ENicol860%20--%3CBR%20%2F%3E%3CBR%20%2F%3ETo%20create%20a%20formula%20that%20works%20correctly%2C%20I%20recommend%20that%20you%20use%20a%20custom%20Text%20field.%20That%20is%20because%20you%20want%20the%20%22n%2Fa%22%20text%20to%20appear%20when%20a%20task%20is%20100%25%20complete.%20Also%2C%20you%20need%20the%20formula%20to%20test%20for%20a%20milestone%2C%20as%20the%20formula%20will%20result%20in%20a%20division%20by%200%20error.%20Following%20is%20the%20updated%20formula%2C%20which%20I%20tested%20and%20confirmed%20that%20it%20works%20correctly%20in%20a%20Text%20field%3A%3CBR%20%2F%3E%3CBR%20%2F%3EIIf(%5BMilestone%5D%2C%22n%2Fa%22%2CIIf(%5B%25%20Complete%5D%3D100%2C%22n%2Fa%22%2C%5BRemaining%20Work%5D%2F%5BRemaining%20Duration%5D))%3CBR%20%2F%3E%3CBR%20%2F%3EHope%20this%20helps.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2263899%22%20slang%3D%22en-US%22%3ERe%3A%20IIf%20formula%20in%20microsoft%20project%20custom%20field%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2263899%22%20slang%3D%22en-US%22%3E%3CP%3EDear%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F112666%22%20target%3D%22_blank%22%3E%40Dale%20Howard%3C%2FA%3E%2C%26nbsp%3Bthank%20you%20very%20much%20for%20your%20help!%3C%2FP%3E%3CP%3EUnfortunately%20I%20can't%20get%20it%20to%20work%2C%20but%20do%20you%20know%20some%20resources%20(books%2C%20websites%20etc.)%20where%20I%20can%20learn%20more%20about%20how%20to%20write%20formulas%20in%20MS%20Project%3F%20I%20can't%20find%20much%20information%20online%2C%20does%20there%20just%20not%20exist%20much%20information%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20for%20your%20help!%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%2C%3C%2FP%3E%3CP%3ENicola%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2263972%22%20slang%3D%22en-US%22%3ERe%3A%20IIf%20formula%20in%20microsoft%20project%20custom%20field%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2263972%22%20slang%3D%22en-US%22%3ENicola%20--%20%22Unfortunately%20I%20cannot%20get%20it%20to%20work...%22%20does%20not%20give%20us%20enough%20information%20to%20help%20you%20further.%20Did%20you%20copy%20and%20paste%20my%20formula%20into%20a%20task%20Text%20field%3F%20If%20so%2C%20the%20formula%20should%20have%20worked%20immediately.%20What%20did%20you%20do%20in%20response%20to%20my%20last%20message%3F%20And%20if%20it%20is%20generating%20an%20error%2C%20please%20include%20one%20or%20more%20screenshots%20to%20show%20us%20what%20you%20see.%20%3CBR%20%2F%3E%3CBR%20%2F%3EAlso%2C%20to%20the%20best%20of%20my%20knowledge%2C%20the%20only%20book%20on%20Microsoft%20Project%20formulas%20is%20in%20Portuguese%2C%20and%20was%20written%20by%20a%20colleague%20of%20mine.%20Do%20you%20speak%2Fread%20Portuguese%3F%20%3A)%3C%2Fimg%3E%3C%2FLINGO-BODY%3E
New Contributor

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?

10 Replies
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.

Dear @Dale Howard, 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? :)

@Dale Howard,
Ismet Kocaman has a book on Project formula creation. It's not free and I don't know the price but I believe it is similar to Rod Gill's book on Project VBA. The link for Ismet's book is:

http://www.ismetkocaman.com/Formulas/eBook.html 

@Dale Howard Yes, I copied your formula and also tried typing it in and picking the fields & operators, but it gives me an error message (see screenshot attached).

 

The difficulty is that it does not indicate where the error is, I tried if a simple IIf formula works, but not even that I can get to work.

 

So far only the formula [Remaining Work]/[Remaining Duration] has worked on its own (as a number type).

 

With the IIf formula I want to achieve that the project managers see when a task is complete, so they don't need to pay attention to these tasks when they assess how much work still is to be done in the remaining time for a task. So it does not necessarily need to show "n/a" it could also show something else (like a number or a flag)

I have a different user name because I switched to my work computer
Nicola -- Thanks for sharing the screenshot. That DOES help. I just tested my formula in Project Online Professional (the M365 version of Microsoft Project) and it does work as designed. Therefore, I would recommend you do the following:

1. Launch Microsoft Project and connect to your Project Online system.
2. Open a project that is in flight. You do not need to check out the project.
3. Click Project > Custom Fields.
4. Select the first available task Text field and rename it to the field name of your choice.
5. Click the Formula button for the new Text field.
6. Copy the formula from my previous post in this forum, and then paste it into the Formula dialog.
7. Click the OK button. You SHOULD NOT get an error at this point.
8. In the Custom Fields dialog, click the Add Field to Enterprise button.
9. In the Add Field to Enterprise dialog, DO NOT change the name of the field. Leave the name as it currently appears and then click the OK button. You should see a confirmation that the field was added successfully to Project Web App.
10. Click the OK button to close the confirmation dialog and then click the OK button to close the Custom Fields dialog.
11. Launch your preferred web browser and then navigate to your organization's Project Web App.
12. In the upper right corner of the page, click the Settings button (it looks like a gear wheel) and then select the PWA Settings item.
13. On the PWA Settings page, click the Enterprise Custom Fields and Lookup Tables link.
14. On the resulting page, click the name of the new Text field you just imported.
15. Remove the text that is inside of the parentheses characters, along with the parentheses characters, such as (Text1) for example.
16. Click the Save button.

Completing the above process should resolve your problems. Let us know if this works.

Thank you @Dale Howard ! It worked perfectly! Thank you very much for your step by step instructions!

 

Do you also know how I can adjust the formula so the summary tasks and parent tasks have an "n/a" as well? (on the picture these are the bold tasks "Phase 1" and "Planungsphase 1")

 

 

NikkyE2404_0-1620046719106.png

 

NickkyE2404 --

Sure do. Open the project in question, then display the Custom Fields dialog. Select the custom Text field in which you created the formula. In the Calculation for Task and Group Summary Rows section of the dialog, select the Use Formula option. Click the OK button. Hope this helps.
Thank you John, for your support. I appreciate it very much.