Forum Discussion
Referencing a NULL field - Customized Column
- kish27,
Sorry but something doesn't quite make sense. You say you have a custom number column called Budget Cost, but number fields will always have a default value of zero. Are you sure you don't mean your Budget Cost field is a custom Text field?
Project does have a Budget Cost field but it is only applicable to the Project Summary Task.
Assuming your "Budget Cost" field is Text2, the following formula should give what you want:
IIf([Text2]<>"",format([Text2]/186000*100,"#.00") & " %","")
Hope this helps.
John
Allow me to add to the excellent answer from my esteemed colleague, John. It appears you are not referencing the built-in Budget Cost column in Microsoft Project, as that field only allows you to enter a value at the Project Summary Task level (Row 0 or Task 0). In what type of field did you create this custom field? If you are using a custom Number field, then your formula would be as follows:
IIf ([Budget Cost] <> 0, format ([Budget Cost]/186000*100,"#.00") & " %", "")
If you are using a custom Text field, then you should use the formula that John offers you. Please let us know and we will try to help you.
Hi
Thanks guys for responding. Looks like it is how I defined Budget Cost either as Text or Number. But when I checked Budget Cost it is not a customized column it was inserted using MS Project "Add New Column" it is already there. So to test both yours and John's expression, I created two columns %Budget Number (which is a number column) and %Budget Text (which is a text column). Then I inserted the following expressions as formula given by you guys as follows:
%Budget Number I had -> IIf([Budget Cost]<>0,format([Budget Cost]/186000*100,"#.00") & " %","")
%Budget Text I had -> IIf([Budget Cost]<>"",format([Budget Cost]/186000*100,"#.00") & " %","")
The results as shown below:
Looks like Budget Cost is treated as text column as the expression given by John works. If thinking Budget Cost is a number column it shows entries with #ERROR. But it got the summary level number correct.
One small slight issue here is with John's expression I need to tweak the format a bit to solve if numbers have a leading zero. For example if you watch the bottom portion of the output .43% and .32% should be displayed as 0.43% and 0.32% respectively. Is there a link or documentation on how I can tweak this format of "#0.00" to something that will resolve my issue above.
Best Rgds
Kish
- Kish27,
You actually answered your own question. Your format statement uses "#.00" as the format which of course will not provide a leading zero. But the last line of your post mentions a format of "#0.00" which WILL provide a leading zero.
One caution about using math with text fields. In this particular case it works but the compiler will not always interpret text such that a math equation can be performed.
The reason you got an error when you thought you tried Dale's method is because (according to your screen shot) the "Budget Cost" column is clearly a Text field and the formula is trying to compare that to zero, hence the error.
JohnHi John
Thanks for your reply. Yes indeed changing the format to #0.00 indeed was what I wanted.
Regarding the warning that mixing text and number calculation may sometimes give problems I guess we need to convert the number to text using some function such as cstr. Maybe below expression would be safer and better as it also works: Below converts maths part ([Budget Cost]/186000 *100) to a string. ( Budget Cost is provided by MS Project which I gather is a text field)
IIf([Budget Cost]<>"",format(cstr([Budget Cost]/186000*100),"#0.00") & "%","")
Is there a document which describes on what functions we can use in the formula ? Or can I use any Visual Basic functions in the formula in the customized field?
Best Rgds
Kish
