How to Display Blank field using custom field

[KR]

Hi

Have 2 questions with regard to Ms Project custom field usage.

1. I have a very simple question but I don't seem to be able to get it right.

I am trying display a blank field on a new custom column of type number using custom field.  Below is the formula I am using:

IIf(([Budget Cost]<>""),Round(([Budget Cost]/186000)*100),"")

I am checking the Budget Cost col and if is not empty then do the maths shown about basically finding some percentage value and rounding the final number.

If Budget Cost is empty/blank then I just want to leave it blank as well do nothing with it.  So for the false part I simply said "" it gave an error showing #ERROR I guess why this so - because the new custom field is of a type number and I am trying to store an empty string.

So I even replace the false part with [Budget Cost] meaning if it is empty just copy the empty field into my new custom col. Still it gave me #ERROR

I tried replacing with NULL also did not work. For now I have replace the false part with number 0 and it work fine but I have 0 displayed for those Budget Cost cells with blank field. This is very ugly and I dont like this. 

I just want to display an empty field/ blank...How do I that,  

2. Also one side question how do a get sum of a row of numbers...at the moment as u can see in my formula I am manually inserting the sum of the Budget Cost column as  $186,000 to  get my percentages. Is there a function, built in field etc that I can use?

Any help my appreciated.

Rgds

KR

[#2]

Well, you're right in that the error occurs because you are trying to write a string ("") into a custom Number field. However, even if you change the formula to write a zero, the end result will ALWAYS be zero because the Budget Cost field is only applicable to the Project Summary task so no task field will ever have a budget cost. You cannot show a "blank" in a Number field, but you can use a Text field to show nothing.

On your second question, you can get the sum of a row of numbers by creating a custom field that is the sum of each element in the row (e.g, Number1 + Number2 + etc.). However, I think what you perhaps are asking is how to get the sum of a column of numbers (e.g. sum up the Number1 field). For the latter, you simply check the option "Calculation for task and group summary rows" and select "sum"

[#3]

Hi John
Thank you for your prompt reply. Appreciate your response.

1. I agree Budget Cost can only be applied to the Project summary. But what I have done is that I created a Table based on Resource Usage and in that table I have a Budget Cost col which displays all the Budget cost for each resource item. However some cells in this column can be blank. Then I created a custom field to calculate the percentages based on my earlier formula I showed. That's where my question is about for those blank cells I want to ignore calculating the percentage.

2. Yes I am asking for the sum of numbers in a column. Referring again to my Budget Cost col in my Resource Usage table I want to sum that column. I am aware that you can sum the summary by selecting an option if there is a group filter set where u will get a summary. In my resource usage table the display has no summary just pure cost items in every columns and the first col are the different resources names as defined in my Resource Sheet.

Hope my reply gives more clarity on my questions.
Look forward to your reply.
Rgds
KR

[#4]

Okay so are you saying you created a custom field in the Resource Usage view that you are calling "budget cost" but it is not Project's Budget Cost field? If so, then that would have been good to know at the onset.

What is this "table" you created? Is it a lookup table? What is the field name (i.e. Project name not your custom name)?

A screen shot would be really helpful. Remember, I'm not inside your head so I can't see what you are doing or what you are thinking, you have to explain in in terms as you would to a third party. Maybe you can sense my frustration.

When you customize a field in a Usage view (Task or Resource), that formula applies ONLY to the resource or task data, NOT to the assignment data. You cannot create a custom field formula for assignment rows. However, you can use VBA to create a macro that will calculate assignment row data and can also roll it up to the resource or task rows.

[#5]

@John-project 

dear John

My humble apologies for  getting you frustrated over this issue that I am trying to explain. I will provide the screenshot as well hopefully this will help. I have create a View of my report and I will explain what I want to do.

I have a col Budget Cost which is NOT a custom field it gives all the Budget allocation as defined in the Resource Sheet.  %Budget is the custom field which is now of type Text (as suggested in your earlier reply so that I can display blank/empty cell for those cells that I do not have any budget values during my percentage calculation. This is working the way I want now.

In order to calculate % tages I need to know the total of the Budget Cost column which is $34,500 + $38,000 + $70,400 + $28,100 + $15,000 = $186,000. I am now manually inserting $186,000 in the formula in my custom field to get the %Budget figures.

My question is how can I get this col sum of Budget Cost to avoid hard-coding $186,000 in the custom field.

Lastly, your last statement  "You cannot create a custom field formula for assignment rows. However, you can use VBA to create a macro that will calculate assignment row data and can also roll it up to the resource or task rows." - is they any youtube video that I can watch to understand what you meant here or any article that I can read or any example that I look at and understand this.

Once again appreciate you taking  a lot your time in looking at this issue I have.

Rgds

KR

[#6]

@KR 

IT would have been easier if you had presented this data at the beginning.

I think I figured out a way to get what you want but I'm having some issues getting it to work. It will take me a couple of days.

John

[#7 · Accepted solution]

Okay I've got something that appears to work.
1. In the Resource Usage or Resource Sheet customize the Resource Text1 field with this formula and check the option to "use formula" for task and group summary rows::
IIf([Budget Cost]<>"",round([Number1],1) & "%","")

2. This macro will populate the Resource Number1 field with the desired percentage data
Sub CustomBud()
Dim Den As Single
Dim CFF As String
Den = Round(ActiveProject.ProjectSummaryTask.BudgetCost)
CFF = "iif([budget cost]<>"""",[budget cost]/" & Den & "*100,0)"
CustomFieldSetFormula FieldID:=pjCustomResourceNumber1, Formula:=CFF
CustomFieldPropertiesEx FieldID:=pjCustomResourceNumber1, Attribute:=pjFieldAttributeFormula, _
summarycalc:=pjCalcFormula, GraphicalIndicators:=False, AutomaticallyRolldownToAssn:=False
ViewApply Name:="resource sheet"
ViewApply Name:="Resource Usage"
End Sub

This was challenging.
John

[#8]

Hi John

Thanks a lot for the solution. This awesome! I guess if things don't work the normal way we can always do some programming to solve it. I am not very familiar with VB and I got your code to work in my project file. I need to understand some of the variables that you used which are MS Project dependent. Is there a site I can go to see all possible MS Project's variables that I can use to manipulate data?

One last thing I need to know is that if I want to display all my fixed cost items that are assigned to some Tasks ( fixed cost column) on the same report I attached previously (which is more of a resource level cost information) is that possible? Is there a work around?

Or is it a rule that we cannot combine Resource level data with Task level data period!
Once again thanks a lot for your help. Much appreciated.
Best Rgds
KR

[#9]

What you really need to understand is the Project Object model. You might look at the following:
https://docs.microsoft.com/en-us/office/client-developer/project/project-home?redirectedfrom=MSDN

To answer your other questions, with VBA you can combine any Project (e.g. project, task, resource, assignment) data to form a report. Depending on thr format sometimes it is possible to create the report in Project, as I did for you in this scenario, but normally I find it much more flexible to export Project data to Excel to create virtually any custom formatted report.
John

[#10]

Hi John

Thanks again. Until I hit another problem I will post another discussion. Until then take care.
Cheers!
Stay Safe