Mar 27 2021 10:31 AM
Mar 27 2021 10:31 AM
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.
Mar 27 2021 03:09 PM - edited Mar 27 2021 03:13 PM
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"
Mar 27 2021 06:39 PM
Mar 27 2021 07:23 PM
Mar 28 2021 09:20 AM
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.
Mar 29 2021 01:50 PMSolution
Mar 30 2021 08:32 AM
Mar 30 2021 10:22 AM
Mar 31 2021 05:03 AM