Search Data to find how much workers are Utilized

[dualhcsniatpac]

To start, what I think I am looking for is a array function but I may be wrong.

I have essentially 7 Ranges that are involved with this function.
The first 6 Rows involve entering data about the technician and what percentage of work they will do for a certain task. (Ex. Tech 1 : 30% ; Tech 2 : 40% ; Tech 3 : 30%)

The last row is calculated by using time study data to show overall utilization of any worker(s) that is needed for the build.

Example of the spreadsheet
Tech .....% ......Tech ......% ......Tech ......% ......Utilization
..1 .......30% ......5 ........10% ......11 ......60% ......300%
..4 .......20 ........3 ........70 ..........1 .......10 ..........100

(Sorry about the horrible Formatting)

What I need a formula to do is search row 1,3,5 and if any contain the corresponding Technicians number, SUM (corresponding row 2,4,6)*(% Utilization)

For example in the above information if I was looking for how utilized Tech 1 is, the formula would return the calculation 30%*300%+10%*100%. For this question we can just refer to each row as Range "Row1", "Row2", ect.

Please let me know if I am leaving anything out.

 

[Dryver14]

I dont think you want what you think you want.

The first thing is to give consideration to each task and what the task consists of.

For instance if 4 men have to move 100 balls then you would assume a formula would bring up that each man would do 25% however in the working world this is never the case although the task is normally complete to 100%.

I'm not even going to ask about 300% of something.

In essence you need to allocate a certain pointage to each task and that will generate the percentage per person,

Also I doubt this will give you any true findings as if task 1 is small someone may recieve a value of 100% aagainst a larger job where harder work is involved may only generate 10% per person.

I am rambling I know.

 

[dualhcsniatpac]

Dryver,

Thank you for your reply.

I almost added stuff in there about all of that but didnt, especially about the 300%.

There is obviously more to it than what I wrote. To try not to confuse anyone, the "300%" refers to the following example. I have an input for how many of a product that I need. Say I need 4 bolts by Friday. The sheet calculates how many work days I have to complete that task and divides it by the amount of time it takes to complete that task. If 1 task takes 2 days and I have 2 days to complete 2, I need 200%. So essentially the 300% represents that I need 3 full workers to complete that task by Friday.

Inputs into that are Due Date, Amount of product and Time Data (How long a task takes)

The horrible looking spreadsheet i made in the first post is all Input data, not calculated. For the most part, most of the tasks will be done by 1 Technician. I wanted to have a little flexibility.

I hope that was not horribly confusing.

David S.

 

[Dryver14]

that makes more sense.

I still think you are going about it wrong though, you are probably best sticking to the hours worked and dividing that into days eg 16 hrs is 2 days this will be far easier to calculte than percentages which in essence mean nothing unless assigned a value.

to say 30% times 300% is the same as X times y, without a number to reference the formula will not work.

so for instance x workers tasks = 33 hrs of a 40hr week then you can work out that his utilisation rate is 82.5%

 

[dualhcsniatpac]

Yea you are probably right. That did cross my mind when I was thinking about this part of the sheet. I wasnt sure if excel was going to calculate it.
I think my question still stands though if I decide to change it to hours needed to complete the task and then have it calculate it as a percentage in the formula.

 

[dualhcsniatpac]

On research I came up with this.

=SUM((Row1="1">0)*(Row2*Row7))

Only problem is I get a #VALUE! Error on Row1. Not sure why.

Example of error:

=SUM((#VALUE!="1">0)*(Row2*Row7))

 

[dualhcsniatpac]

I found out why it gave me an error. For array functions I found that instead of "Enter" you must hit "CTRL-SHIFT-ENTER"

Bad news it still doesnt work like I would like it too.

Any suggestions?

 

[dualhcsniatpac]

OK I have found the solution myself.

I used an array function

{=SUM((Row1=1)*Row2*Row7)} **** For just adding the one row.

The errors I was getting resulted from merging cells together. The array was looking at all cells included in the Range and not just the first row. (Ex. Range "X" = A1:B3 would return {x,x;x,x;x,x} for corresponding cells {A1,B1;A2,B2;A3,B3}