Answered by:
If I have a 10% Gross Margin formula can I change one task to calculate at 15%
Question

I am working in MS Project 2010 Pro, I have set a column with a custom field to a 10% Gross Margin formula, is there a way I can change one task to a 15% Gross Margin formula?
I am also having a problem opening two project schedules at once, for some reason when I open the second schedule it's like I lose the schedule.
Thank you for all the help,
Celia
Answers

I would be inclined to go for a more flexible approach as given there is one exception there are bound to be others later! I would enter the margin in a Number field like Number1 (.1 for 10% and .15 for 15% etc.) then use that number field in the formula 
[Cost]*(1+[Number1]).
Now to edit the margin for individual tasks you just edit Number1.
Rod Gill
Author of the one and only Project VBA Book
www.projectsystems.co.nz Marked as answer by Dale Howard [MVP]MVP, Moderator Friday, September 26, 2014 1:20 PM
All replies

Celia 
Regarding your first question, you would have to rewrite the formula to trap for the one task with the 15% gross margin. You would have to begin the formula with an IIF statement, testing for Task Name = name of task with higher margin. If the IIF statement returns a True value, then enter the formula for the 15% gross margin. If the IIF statement returns a False value, then enter the formula for the 10% gross margin.
Regarding your second question, I have never run into this problem before. Have you tried using Alt + Tab to see if the first project is still open? You can also try clicking the View tab to display the View ribbon, and then clicking the Switch Window pick list button in the Window section of the ribbon to switch from one project to another.
Hope this helps.
Dale A. Howard [MVP]


Here is a bit more detail for your formula.
You would put this in a custom Cost field, say Cost1.
Insert the Cost1 field in the Entry table.
Right click the heading of that column and choose Custom Fields from the menu.
Rename the field to, say, "Price".Click on the formula button and write the formula.
Say the name of the task you want to trap for the 15% is "Make Widget".
Say the margin on all others is 10%.
Your formula is:IIF([Name]= "Make Widget", [Cost] * 1.15, [Cost] * 1.10)

I would be inclined to go for a more flexible approach as given there is one exception there are bound to be others later! I would enter the margin in a Number field like Number1 (.1 for 10% and .15 for 15% etc.) then use that number field in the formula 
[Cost]*(1+[Number1]).
Now to edit the margin for individual tasks you just edit Number1.
Rod Gill
Author of the one and only Project VBA Book
www.projectsystems.co.nz Marked as answer by Dale Howard [MVP]MVP, Moderator Friday, September 26, 2014 1:20 PM


Celia 
I absolutely LOVE the solution that Rod is proposing. What his approach offers you is the flexibility to specify a cost margin value for every task, as needed. Since you already have one task that does not follow the 10% margin standard, my guess is that there will be more of those exceptions, perhaps many more. So, if you use a custom Number field to hold the margin value, the simple formula Rod proposes will solve the problem nicely. And his approach also offers you the opportunity to change the margin value on any task as needed.
Many years ago, before I became an MVP, Rod Gill was my hero (and still is) because he was kind to people in the user forums and because he always gave such good answers to the questions that people asked. His response to your problem is proof positive of that! :)
Hope this little bit extra helps.
Dale A. Howard [MVP]

Good Morning to all,
@Trevor I tried the formula but I am doing something wrong, forgive me but I am new and practicing with formulas, I truly appreciate all the help ya'll are giving me. @Rod I will try your way as well.
I tried to attach the error but it is telling me I am not able to attach until they verify who I am  not sure what that means but it is telling me "the formula contains syntax error or contains a reference to an unrecognized field or function name.
IIF([Name]="Engineering",[Cost]*1.15[Cost]*1.10)  the second "Cost" is highlighted as the error.
Dumb question but is there a formulas 101 I can refer to for help?
This is a great forum, it has helped me tremendously.
Have a blessed day,
Celia


@Trevor I got the formula to take your way and I understand what Rod has suggested as well so I am trying his approach. @Rod my apologies but I am using the Number1 custom field box but I am receiving an error will you please help me with the formula I am using a 10% Gross Margin (divided by 0.90) & 15% Gross Margin (divided by 0.85)
Again many thanks,
Celia




Good Morning John,
I am using the number2 field and the error I am receiving is number2 is highlighted in yellow, I am sure it's because I am not writing the formula correctly.
I am need to change a few task to different % Gross Margins than the overall schedule, ex:
The whole project is at a 10% Gross Margin but task 15 needs to be at a 15% Gross Margin and task 145 needs to be at a 20% Gross Margin
Can you help me with the formula?
Thank you for your help,
Celia

Celia,
Take a look at the following and see if that helps. Note, if you want to show a $ you will not be able to use the Number2 field for your formula, instead you should use a text field (e.g. Text1). Then the formula would look like this:
Text1="$" & [Cost]*(1+[Number1])
John
 Edited by John  Project Thursday, September 25, 2014 4:42 PM typo

John,
Yes it does help me, my problem was not adding the second column.
It is giving me the following error:
The formula cannot be saved it creates a circular dependency with "?%GrossMargin(Text7)"field.
Text7="$"&[Cost]*(1+[Number2]
What am I doing incorrectly?
Thank you,
Celia

Celia,
You do realize that "Text7=" is not part of the formula, right? The actual formula in the Text7 field is:
"$" & [cost]*(1+[Number1])
Also, don't forget the closing parenthesis, I see it's missing from your formula.
And, are you using Text7 for something else, like maybe a lookup table? If so, you need to use a different text field that doesn't already have some customization associated with it.
John



Aw shucks Dale........... Do you want to borrow some money or something???! :)
Rod Gill
Author of the one and only Project VBA Book
www.projectsystems.co.nz 