Referencing a NULL field - Customized Column

[Kish27]

Hi

I am trying to reference a null field but I am not getting right. I am trying to display "%" after my calculation. So I have a number column called Budget Cost. I created a Text11 column and had a customized formula in it. The logic I want is If there is a value in the Budget Cost column calculate the percentage and concatenate "%" at end. If Budget Cost is empty is don't do anything leave it empty.

My formula in the Tex11 column is as follows:

format([Budget Cost]/186000*100,"#.00") & " %"

I need something like below:-

iif ([Budget Cost] <> ?????, format([Budget Cost]/186000*100,"#.00") & " %", "")

what do I put in ???? ...I have tried NULL, I have tried "" nothing works I keep getting #ERROR (shown in red below) it just should be empty.

Or the above syntax is not correct.

Appreciate any help.

Best Rgds

[#2 · Accepted solution]

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

[#3]

Kish --

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.

[#4]

@Dale_HowardMVP 

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

[#5]

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.
John

[#6]

@John-project 

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

[#7]

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-7e...

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-v...

John

[#8]

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

[#9]

Kish27,
You're welcome and thanks for the feedback.
John

[#10]

Hi John
I was going to tag one of your replies as the best response I noticed Dale has done so. I would like tag another response as best response but don't seem to know how to tag this. I was looking for option in the reply I cannot see how this is done. Any idea how this is done.
Rgds
Kish

[#11]

Kish --

I untagged one of John's responses. Feel free to task whichever response you believe is the best answer.

[#12]

Hi Dale
It was the same one you tagged. Hence Ire- tag the same response as the best answer from John. I thought each discussion thread could have more than one best response tag which is not the case. Thanks anyway for untagging and also thanks also for your responses in this discussion. Help much appreciated.
rgds
kish