Custom formula to calcualate resouce count

[vkanotra]

Hi,

I am trying to add a custom formula in a custom field in Project 2003.
Here's what formula that I'm using:

num of resources = Rnd([Work]/(([Finish] - [Start]) * 8))

Basically, I want to calculate how many resources would it take to
complete a task based on the amount of work and duration of a task.

I get 0's in the columns when I apply this formula to a custom field.
What am i doing wrong?

 

[JulieS]

Hi vkanotra,

The Rnd project function is not the "Round" function from Excel. From
Help on Rnd:
Returns a Single containing a random number.

I believe what you are looking for is a simple formula:
[Work]/[Duration]

This will calculate resource requirement needs in decimals.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional information
about Microsoft Project

 

[Gary L. Chefetz [MVP]]

VK:

Project calculates this for you when you apply a resource to a task. That's
part of the tools' job. Your job is to set the parameters correctly.
Example, you want to know how many resources it will take to finish 80 hours
of work in one week. You set the task type to fixed duration, set your
duration, go to Windows menu, and select Split. In the lower half of the
screen you can control all of these parameters. In this view, make sure that
task type fixed duration is set, select a resource from the list in the
window below, then enter your work value and click OK. The system will
calculate units at 200% meaning that you have to have two resources to make
this happen. Add more hours and you'll see the units increase.

 

[Varun Kanotra]

Thanks for the clarification on Rnd Julie. It does help to know that Rnd is
not to round.

Hi vkanotra,

The Rnd project function is not the "Round" function from Excel. From
Help on Rnd:
Returns a Single containing a random number.

I believe what you are looking for is a simple formula:
[Work]/[Duration]

This will calculate resource requirement needs in decimals.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional information
about Microsoft Project

Hi,

I am trying to add a custom formula in a custom field in Project 2003.
Here's what formula that I'm using:

num of resources = Rnd([Work]/(([Finish] - [Start]) * 8))

Basically, I want to calculate how many resources would it take to
complete a task based on the amount of work and duration of a task.

I get 0's in the columns when I apply this formula to a custom field.
What am i doing wrong?

 

[Varun Kanotra]

Gary,

I am using fixed duration in my task usage view. Also, I had added the
column to show me Assignment units which shows resource utilization %age.

I guess what I can do is divide the Assignment units by 100% to get the # of
resources or just use the Assignment units as is.

Thanks for your help. Much appreciated.

VK:

Project calculates this for you when you apply a resource to a task. That's
part of the tools' job. Your job is to set the parameters correctly.
Example, you want to know how many resources it will take to finish 80 hours
of work in one week. You set the task type to fixed duration, set your
duration, go to Windows menu, and select Split. In the lower half of the
screen you can control all of these parameters. In this view, make sure that
task type fixed duration is set, select a resource from the list in the
window below, then enter your work value and click OK. The system will
calculate units at 200% meaning that you have to have two resources to make
this happen. Add more hours and you'll see the units increase.

Hi,

I am trying to add a custom formula in a custom field in Project 2003.
Here's what formula that I'm using:

num of resources = Rnd([Work]/(([Finish] - [Start]) * 8))

Basically, I want to calculate how many resources would it take to
complete a task based on the amount of work and duration of a task.

I get 0's in the columns when I apply this formula to a custom field.
What am i doing wrong?

 

[Varun Kanotra]

btw.....when you apply the formula you mentioned below, the system does not
recognize the field [Duration]. Apparently, there is are custom duration
fields in the selection list of fields but nothing to store duration, which
is bizarre (unless i'm not seeing it).

Hi vkanotra,

The Rnd project function is not the "Round" function from Excel. From
Help on Rnd:
Returns a Single containing a random number.

I believe what you are looking for is a simple formula:
[Work]/[Duration]

This will calculate resource requirement needs in decimals.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional information
about Microsoft Project

Hi,

I am trying to add a custom formula in a custom field in Project 2003.
Here's what formula that I'm using:

num of resources = Rnd([Work]/(([Finish] - [Start]) * 8))

Basically, I want to calculate how many resources would it take to
complete a task based on the amount of work and duration of a task.

I get 0's in the columns when I apply this formula to a custom field.
What am i doing wrong?

 

[Varun Kanotra]

Julie,

You were right. Work/Duration did work. What I was doing wrong was that I
had the "Resource" box checked in the custom formula window instead of task.
I applied the custom formula to the task and it worked!!

Awesome! Thanks a lot!

Hi vkanotra,

The Rnd project function is not the "Round" function from Excel. From
Help on Rnd:
Returns a Single containing a random number.

I believe what you are looking for is a simple formula:
[Work]/[Duration]

This will calculate resource requirement needs in decimals.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional information
about Microsoft Project

Hi,

I am trying to add a custom formula in a custom field in Project 2003.
Here's what formula that I'm using:

num of resources = Rnd([Work]/(([Finish] - [Start]) * 8))

Basically, I want to calculate how many resources would it take to
complete a task based on the amount of work and duration of a task.

I get 0's in the columns when I apply this formula to a custom field.
What am i doing wrong?

 

[JulieS]

You're most welcome Varun, and thanks for the feedback.


Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional information
about Microsoft Project

Julie,

You were right. Work/Duration did work. What I was doing wrong was
that I
had the "Resource" box checked in the custom formula window instead of
task.
I applied the custom formula to the task and it worked!!

Awesome! Thanks a lot!

Hi vkanotra,

The Rnd project function is not the "Round" function from Excel.
From
Help on Rnd:
Returns a Single containing a random number.

I believe what you are looking for is a simple formula:
[Work]/[Duration]

This will calculate resource requirement needs in decimals.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information
about Microsoft Project

Hi,

I am trying to add a custom formula in a custom field in Project
2003.
Here's what formula that I'm using:

num of resources = Rnd([Work]/(([Finish] - [Start]) * 8))

Basically, I want to calculate how many resources would it take to
complete a task based on the amount of work and duration of a task.

I get 0's in the columns when I apply this formula to a custom
field.
What am i doing wrong?

 

[JackD]

Varun,

Sounds like you have already solved this problem, but if you need a round
function you can always use the INT function which returns the integer
portion of a number. You can also use MOD to determine whether you want to
round up or down. Int(5.6) would return 5. Mod(5.6) would return .6 so you
could construct an iif statement to evaluate the mod value and round up if
it is greater than some threshold you set.

An alternative to Int is to use the integer division operator which is a
backslash \ instead of a forward slash. This is the same as using the int
function on the results of division. I have an explanation here:

http://zo-d.com/blog/archives/programming/vba-integer-division-and-mod.html

-Jack Dahlgren

Julie,

You were right. Work/Duration did work. What I was doing wrong was that I
had the "Resource" box checked in the custom formula window instead of
task.
I applied the custom formula to the task and it worked!!

Awesome! Thanks a lot!

Hi vkanotra,

The Rnd project function is not the "Round" function from Excel. From
Help on Rnd:
Returns a Single containing a random number.

I believe what you are looking for is a simple formula:
[Work]/[Duration]

This will calculate resource requirement needs in decimals.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional information
about Microsoft Project

Hi,

I am trying to add a custom formula in a custom field in Project 2003.
Here's what formula that I'm using:

num of resources = Rnd([Work]/(([Finish] - [Start]) * 8))

Basically, I want to calculate how many resources would it take to
complete a task based on the amount of work and duration of a task.

I get 0's in the columns when I apply this formula to a custom field.
What am i doing wrong?

 

[Steve House]

If you wish you can go to the Tools Optins menu and set the units for
assignments to be decimal numbers rather than percentages. This is a global
setting but it will show "300%" as "3" for example.
--
Steve House [Project MVP]
MS Project Trainer & Consultant
Visit http://www.mvps.org/project/faqs.htm for the FAQs

Gary,

I am using fixed duration in my task usage view. Also, I had added the
column to show me Assignment units which shows resource utilization %age.

I guess what I can do is divide the Assignment units by 100% to get the #
of
resources or just use the Assignment units as is.

Thanks for your help. Much appreciated.

VK:

Project calculates this for you when you apply a resource to a task.
That's
part of the tools' job. Your job is to set the parameters correctly.
Example, you want to know how many resources it will take to finish 80
hours
of work in one week. You set the task type to fixed duration, set your
duration, go to Windows menu, and select Split. In the lower half of the
screen you can control all of these parameters. In this view, make sure
that
task type fixed duration is set, select a resource from the list in the
window below, then enter your work value and click OK. The system will
calculate units at 200% meaning that you have to have two resources to
make
this happen. Add more hours and you'll see the units increase.

Hi,

I am trying to add a custom formula in a custom field in Project 2003.
Here's what formula that I'm using:

num of resources = Rnd([Work]/(([Finish] - [Start]) * 8))

Basically, I want to calculate how many resources would it take to
complete a task based on the amount of work and duration of a task.

I get 0's in the columns when I apply this formula to a custom field.
What am i doing wrong?