none
If I have a 10% Gross Margin formula can I change one task to calculate at 15% RRS feed

  • 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

    Tuesday, September 23, 2014 6:13 PM

Answers

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]

    Tuesday, September 23, 2014 7:15 PM
    Moderator
  • Dale,

    Thank you for the quick response, I got my second project back with your help.

    Let me work on the formula.

    Again thank you for your help,

    Celia

    Tuesday, September 23, 2014 7:21 PM
  • 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)

    Tuesday, September 23, 2014 11:29 PM
  • 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.project-systems.co.nz

    Wednesday, September 24, 2014 12:13 AM
    Moderator
  • So would I, but I thought I would start with the IIF().

    I would use 1.15, 1.10 etc and use that number in the formula:

    [Cost]*[Number1]

    because it is shorter.

    Wednesday, September 24, 2014 8:37 AM
  • 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]

    Wednesday, September 24, 2014 1:10 PM
    Moderator
  • 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

    Wednesday, September 24, 2014 1:36 PM
  • You don't have the comma between the true part and the false part. Should be:

    IIF([Name]="Engineering",[Cost]*1.15,[Cost]*1.10)

    ... but do what Rod says instead.

    Wednesday, September 24, 2014 1:41 PM
  • @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

    Wednesday, September 24, 2014 2:52 PM
  • Celia,

    Telling us you received an error doesn't help much. What field are you customizing with the formula and what exactly is the formula you are trying to use? Also, what kind of error do you get?

    John

    Wednesday, September 24, 2014 8:21 PM
  • John,

    I am trying the formula Rod suggested up in the thread:

    [Cost]*(1+[Number1]).

    Now to edit the margin for individual tasks you just edit Number1.

    Thank you,

    Celia

    Wednesday, September 24, 2014 8:30 PM
  • Celia,

    Okay, but what field is that formula in and what error do you get?

    John

    Wednesday, September 24, 2014 11:27 PM
  • 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

    Thursday, September 25, 2014 12:46 PM
  • 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

    Thursday, September 25, 2014 4:39 PM
  • 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

    Thursday, September 25, 2014 6:18 PM
  • 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

    Thursday, September 25, 2014 6:40 PM
  • John,

    It worked, thank you so much.

    Celia

    Thursday, September 25, 2014 7:30 PM
  • Celia,

    You're welcome and thanks for the feedback. Since you elected to go with Rod's approach, please mark his response as the answer.

    John

    Thursday, September 25, 2014 8:22 PM
  • Aw shucks Dale........... Do you want to borrow some money or something???! :-)

    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Friday, September 26, 2014 3:39 AM
    Moderator
  • Good Morning Rod,

    Thank you for your help and support, I used the formula you suggested and it is working great.  I would like to add John did help me out as well.  :-)

    Have a great day,

    Celia 

    Friday, September 26, 2014 12:54 PM