where to type values in Project to obtain an Excel-like behaviour? RRS feed

  • Question

  • Hi,

    I've been using Excel to manage my projects and now I want to switch to Project, but I am a bit

    confused. I cannot find a way to accomplish the same task I did on Excel, so I hope you could help


    I have a "paint objects" task. A client says: "I want 10000 objects to be painted in 30 days"; I know

    that each one of my resources can paint 150 objects/day (it's their single productivity); the

    question is: how many resources should I assign to my task, to remain under the 30 days constraint?

    In Excel it's a simple matter of writing my known data into some cells to obtain the result,

    something like 10000/150/30=2.222, rounded up to 3 resources. The Excel worksheet clearly shows my

    resource's productivity and my request for 3 resources is well motivated. Moreover, my company can

    look at the data and decide to do something to increase the productivity of a small quantity in order

    to have the work done with only 2 resources.

    If I want to use Project, how can I obtain the same result? That is, where should I type my data?
    I could THINK "10000 objects 150 per day are 66,6 days", so I would create a "paint" task 66,6 days

    long, I would assign a resource to it, to find out that the task has a work of 532,8 hours. Then I

    would make the task "fixed work" (so also "effort driven") and I would change the task duration from

    66,6d to 30d. As a result, my resource would be overallocated and showing (from the resource usage

    view) the "% of assignment" I would read for it a value of 222%, that is my resource should be 2,2

    physical persons. Next I could resolve the overallocation editing the resource data and write 300% to

    its max units, meaning that there are 3 people.

    The real question is: is there a way to store somewhere the 10000 and 150 values (of the example) so

    that it is Project that calculates the initial task duration? I need my resource's productivity to be

    well visible (and, hopefully, customizable) so that my company (=my boss) can understand why I am

    asking for 3 resources and eventually change those values to do fine-tuning.

    Is it something I can accomplish using Custom Fields with Formulas? But if true, how? :)

    Thank you very much for all the support you would give me.



    Monday, February 18, 2013 9:36 PM

All replies

  • Filippo,

    Honestly, for this type of scenario, I'd stick with Excel. Why exactly do you want to use Project? What you are doing is more of a calculation than a scheduling exercise.

    However, the process you used for Project is a pretty good approach but I think it can be refined a little. Project does it's scheduling by using what is referred to as the work equation.

    Duration = Work/Units or Units = Work/Duration

    You have the duration - 30 days. You indirectly have the work content - 10000/150*8 (assuming a normal work day of 8 hours). What you want to determine is the units - the number of resources. There is no automatic way to integrate the work content equation directly into one of Project's fields so it will either need to be manually calculated or you can use an extra field to do the calculation. For example, if you customize the Number1 field with the equation 10000/150*8, the Number1 field will calculate the work hours needed for the task. You could even break it down further by using two additional number fields to hold the objects data (i.e. 10000) and the rate data (i.e. 150) and then use those in the Number1 field formula. That way you only have to enter the basic data and the Number1 field will calculate the work for you.

    Okay, now set up a resource with the default 100% max units. Don't worry about the overallocation because that's not really relevant for your scenario, at least not as you describe it. Your main goal is to determine the total number of resources needed to accomplish the task. If the overallocation bothers you, you can set the resource max units to 1000% or some large number that will allow for multiple full time equivalents.

    Next set up your task as fixed duration. Enter the duration of 30d. Assign the resource. Enter the work content in the Work field. Switch to the Resource Usage view and add the Peak field. It will display a value of 222% which tells you that 2.22 full time equivalents are needed to complete the task.

    As I said, maybe you should just stick with Excel for this type of planning.


    Tuesday, February 19, 2013 2:35 AM
  • Hi, thank you for your kind reply. My goal was to clearly show the reason of my choices, that is the number of resources used. Is there a way to automatically fill the Work field of a task with the value of a custom field (the Numer1 you described)? So that changing its value will update the Peak of the resource?


    Tuesday, February 19, 2013 8:31 PM
  • Wednesday, February 20, 2013 12:28 AM
  • Nino,

    You didn't answer my question about why you want to use Project. Based on the type of scenario you describe, I see no reason to try and make Project work like Excel.

    The Work field can be updated from the custom field in two ways. One is to use a paste link (definitely non-preferred) and the other is via VBA as Rod described (preferred).


    Wednesday, February 20, 2013 2:05 AM
  • Hi John,

    I generally used Excel to do this kind of estimates and to create a Gantt-like scheduling, very raw, indeed. I just wanted to move to Project in order to have all the good stuff that comes after the initial scheduling, such as project status monitoring, cost analysis and so on. The problem is that my boss (or who approves my budget) wants to see clearly the reason of those costs, and have the last word playing around with rate data and other quantities. Excel allows him to do it, Project not at all. Project is far more powerful but may also result a lot more complicated to understand; if I switch to it to do better, but my projects will be refused I wouldn't be happy... :)


    Wednesday, February 20, 2013 2:41 AM
  • Nimo,

    Okay, now that makes some sense. Yes, for someone, (e.g. your boss), who hasn't had any experience with Project, it can be very difficult to make your case. And as you are discovering, attempting to do all the background processing to come up with the data Project needs to create a schedule is best done outside of Project itself.

    Here's what I suggest. Keep your Excel spreadsheet for generating the raw data. It will be easier to use and your boss will be more comfortable. Import that data, (i.e. task name, estimated duration, estimated work and perhaps a start date for each task), into Project using an import map. As you and your boss get more comfortable with a dual application approach, you can refine the data transfer process but keep in mind that Project is a schedule calculator so don't give it redundant information (e.g. don't import duration, start and finish dates, Project calculates the finish date based on duration and start date).

    Good luck.


    Wednesday, February 20, 2013 3:47 PM