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
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
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.
John
Hi 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
- Kish,
Your use of the term "budget cost" has been an issue throughout this thread. Project has a field called Budget Cost but that field is only applicable to the Project Summary task. The "Budget Cost" you use is apparently a renamed Text field (e.g. Task Text1). Project's Budget Cost field is not stored as a string, it is stored as a cost number but it IS internally reformatted and displayed as a text field when viewed by the user in the Gantt Chart view for example.
I think you misunderstood my comment about attempting to use math relationships on Project fields that are stored as text. If doing math operations on data that is stored internally as text, that data should first be converted to numerical data (e.g. Csng, Cint, etc.) before doing the math operation. If the desire is to then display the result in text form, it may or may not need to be converted to text but normally the interpreter will work directly with numerical data when displaying in text form.
Here is a link to Project functions for custom fields:
https://support.microsoft.com/en-us/office/project-functions-for-custom-fields-in-project-desktop-7e525143-380f-4083-8d5a-3ecc6ba44f22
In many cases VBA functions will also work in custom field formulas but VBA has a much larger selection so not all VBA functions are usable in custom fields.
Previously you ask for a link for the Format function:
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/format-function-visual-basic-for-applications?f1url=%3FappId%3DDev11IDEF1%26l%3Den-US%26k%3Dk(vblr6.chm1008925);k(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue
John- Hi
Thanks for you reply and clarification. Appreciate the links you provided as well. I guess this thread can be considered as close.
Thank you so much for the prompt replies and responses.
Best Rgds
Kish