Jan 26 2022 06:56 AM
Jan 26 2022 06:56 AM
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.
Jan 26 2022 08:19 AMSolution
Jan 26 2022 09:07 AM
Jan 27 2022 01:48 AM
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.
Jan 27 2022 06:58 PM
Jan 28 2022 07:25 PM
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?
Jan 29 2022 07:46 AM
Feb 02 2022 05:09 AM
Feb 02 2022 08:04 AM
Feb 02 2022 08:24 AM
Feb 03 2022 01:51 AM