Forum Discussion
Referencing a NULL field - Customized Column
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
- 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
11 Replies
- 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.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.
John
- 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
