Solving for Two Unknowns in Excel (Formula Question)

[ctackett6407]

Greetings Again,

I created a table to show the time it would take to complete a task based on the number of installers I have shown; however, the logic is based on 1 installer.

So if I send out 2 installers it still will calculate based on 1 installer.

1 installer - 1 hour install time

logically if I sent out 2 installers and the average install time for 1 installer is 1 hour.. then it should lessen if I have two installers.

The only algebraic express I know to solve this would depend on knowing that installer 1 completes a task in a certain amount of time and installer 2 completes their installation in a certain period of time.

Anyone have any solutions for my situation?

The number of installers is a variable in my table and when we change it .. it impacts the number of hours that are summed up.

IE .. 1 installer - 1 hour install time - 1.5 hour drive time - .5 hour lunch etc...

The drive time wouldn't get faster with 2 installers.. but the install time should lessen with the more installers.

Thanks for any replies.

I couldn't figure out how to make this work in a formula as I couldn't think of the logic behind it.

 

[steve the fish]

With experience of past jobs you should be able to create an average time it takes for each job. You can then work it out using man hours per job i.e. if a job takes on average 2 hours for 1 man its a 2 man hour job. It easy then to work out the time for however many people you send.

 

[ctackett6407]

Steve thanks for replying.

I have a feeling I've been over thinking it..

so the logic would be .. if it takes 1 person 1 hour to complete a task.. then in logic it should take 2 people .5 hour to complete the task?

 

[steve the fish]

Yes although you may wish to add a 'fudge factor' depending on the job. Sometimes two people arent half as quick as one person but essentially yes. Take the following table as an example:


[TABLE="width: 386"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]ManHours[/TD]
[TD]Drivetime[/TD]
[TD]Break[/TD]
[TD]Number of Men[/TD]
[TD]Hours Used[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]9.33[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]10.50[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]12.00[/TD]
[/TR]
</tbody>[/TABLE]

In E2 is the formula:

=(B2*D2)+(C2*D2)+(A2/D2)

This job should be done by 2 people for max efficiency.

 

[ctackett6407]

Thank you for explaining and not just giving answer, now I fully understand, thank you!

 

[steve the fish]

Thinking about it the formula should be:

=(B2*D2)+(C2*D2)+A2

which is how much you would have to pay in terms of hours.

This next one is how much time the job should take:

=B2+C2+(A2/D2)

Hope they make sense. It depends if time is more important than the hourly rate as to how many people you send i suppose.

 

[ctackett6407]

I tried this and was using it and I went to explain what I was doing and people looked at me like I was crazy, so I might be doing the wrong calculations.

Basically we want to be able to adjust any of the variables below and it show how long it would take each installer to install at the time.

See the Update Goal is figured based on the total number of possible orders * whatever percent is in the Uptake Goal field.
The # of Installers, Drive Time, Misc, Lunch, and Man Hours to Install are variables.
The # of Installs per Installer is based on (Uptake Overall Goal Total/251 (Total Working Days in 2019)) divided by # of Installers ( so the formula is...) # of
Installs Per Installer = ((Update Overall Goal Total/Total Working Days in 2019))/# of Installers

then I take the formula you had suggested

=((Man Hours to Install/# of Installers)+SUM(Drive Time+Misc+Lunch))*# of Installers Per Installer = Total Time

Am I figuring this right? I'm thinking that means if we have 45% uptake goal (5096 installs in 2019) it would mean we would need 6 installers working the schedule below .. in a 7 hour day.. to accomplish our target? or does this mean we need to send 7 installers together to complete this goal?

I'm trying to figure out if we send 1 installer to a location.. I'm just confusing myself.

I'm trying to figure out various ways to see which is optimal ..

We are trying to see if it would be best to send 1 person per install or if we send 2 people if it would be optimal.. etc..

I believe I'm spreading my confusion.

[TABLE="width: 255"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]Uptake Goal[/TD]
[TD="align: right"]45%[/TD]
[/TR]
[TR]
[TD]# of Installers[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Drive Time[/TD]
[TD="align: right"]0.5[/TD]
[/TR]
[TR]
[TD]Misc[/TD]
[TD="align: right"]0.5[/TD]
[/TR]
[TR]
[TD]Lunch[/TD]
[TD="align: right"]0.5[/TD]
[/TR]
[TR]
[TD]# of Installs per installer[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Man Hours to Install[/TD]
[TD="align: right"]1.5[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Total Time[/TD]
[TD="align: right"]7[/TD]
[/TR]
</tbody>[/TABLE]

Here is the formula I'm using for Total Time

=((Q9/Q4)+SUM(Q5:Q7))*Q8

Thinking about it the formula should be:

=(B2*D2)+(C2*D2)+A2

which is how much you would have to pay in terms of hours.

This next one is how much time the job should take:

=B2+C2+(A2/D2)

Hope they make sense. It depends if time is more important than the hourly rate as to how many people you send i suppose.