Formula Help

Glen Coventry

Hi. I am a project manager for a design build company and I am creating a template to use to forcast the labour required on a job that automatically decreases based on the percentage complete.

 

I am wanting to add a formula to one of my sheets that will automatically populate up to 4 column cells based on an option chosen from a dropdown menu. 

 

The drop down will show which specific team members are required to complete a specific task ie: a Carpenter and an Apprentice out of a list of about 8 options. Once that is chosen I want the columns for Carpenter and Apprentice to be updated with the duration value multiplied by the percentage still to be completed with the column cells for the team members being left blank

 

These values are then totalled at the bottom of the sheet to give me a time value to complete the project which I then convert to a dollar value which is monitored against the budget

 

Thanks in advance

 

Glen.

Christian Wells

Hi Glen,

 

How about a column for each trade where you enter the number of trades people.  Add another column with the rates for each trade (hide this column maybe)

 

then calculate....the Remaining Labour and Remaining cost.  See the attached screenshot.  The formulas are below; (For row 6)

 

Remaining labour

=([Apprentice Chippie]6 + Chippie6) * Duration6 * (1-[% Complete]6) 

 

Remaining cost

=([Apprentice Chippie]3 * Duration3 * (1-[% Complete]3) * [Aprentice Chippie Rate]3) + (Chippie3 * Duration3 * (1-[% Complete]3) * [Chippie Rate]3)

 

Expand it out for further columns and tradies on your project.

 

 

A bit cumbersome, but should do the trick

 

Good luck.

Glen Coventry

Thanks Christian... That may work. I may not have been clear however so I will have another go...

 

I have included a screen shot of what structure I have to date... What I do when I build a schedule is I look at each task and choose what labour confiruration will be required to complete that task.

 

What I would like is for each of the columns for Site Foreman, Carpenter etc to be updated based on that selection showing the value in the duration column and then multiplied by the % still to be completed to give me a total at the bottom which shows day etc required to complete the project

 

At the moment I have to manually enter that value into each of the PM, Site Foreman columns

 

What I am trying to do is build a template that foolproof for the other PM's to use where I can lock these formulas off once correct

 

Thanks again

 

Glen

 

 

 

Travis

To make sure I understand,

 

You select an option from Resource Allocation column, then the corresponding column (PM, Site Forman, etc) should calculate Duration * (100% - % Complete)? 

 

For PM, use something like this:

 

=IF([Resource Allocation]1 = "PM", Duration1 * (1 - [% Complete]1))

Glen Coventry

Perfect, Thanks Travis

 

And one last question... As there is the choice in that list of PM on his own or the PM and SF (PM,SF) how do I add that to the above example ( Sorry, very little excel formula experience )

 

Cheers

Travis

You could use a FIND function to locate a string in the cell. Try this:

 

=IF(FIND("PM", [Resource Allocation]1) > 0, Duration1 * (1 - [% Complete]1))

Glen Coventry

Perfect... Thanks heaps