Attempting to make a formula/algorithm based on weighted averages to find how much equipment we need to maintain.
I have an excel spreadsheet I have put together holding a bunch of data. I'm hoping to come up with a best guess ratio to achieve/maintain of 3 different pieces of equipment that each run a specific process.
Process/Equipment 1 runs 18 hours a day, 5 days a week.
Process/Equipment 2 and 3 run 24 hours a day, 6 days a week.
Process 3 will take 24 hours regardless of the part.
Part 1 runs 49 batches a year.
Process 1 takes 30 hours to complete a batch.
Process 2 takes 8 hours to complete a batch.
Part 2 runs 13 batches a year.
Process 1 takes 30 hours to complete a batch of parts.
Process 2 takes 8 hours to complete a batch.
Part 3 runs 20 batches a year.
Process 1 takes 15 hours.
Process 2 takes 14.5 hours.
Part 4 runs 23 batches a year.
Process 1 takes 15 hours.
Process 2 takes 14.5 hours.
Part 5 runs 33 batches a year.
Process 1 takes 34 hours.
Process 2 takes 7.5 hours.
I'm trying to get a weighted average for Process 1 and 2 time based on how many batches of each part we run a year. It is complicated by how long each process runs a week. This is what I am trying so far using the data above.
Process 1 Average (weighted) = ((30×49)+(30×13)+(15×20)+(15×23)+(34+33)) ÷ (49+13+20+23+33) = 26.2826087
Process 2 Average (weighted) = ((8×49)+(8×13)+(14.5×20)+(14.5×23)+(7.5×33))÷(49+13+20+23+33) = 9.905797101
I'm not sure how to account for time though. I thought about taking the time the processes run (respectively 80, 144, and 144 hours per week) and dividing by how many hours there are in a week to get a factor to multiply the weighted averages by.
80÷168 = 0.5357142857
144÷168 = 0.8571428571
Process Weighted Average × Time Factor Adjustment
Process 1 = 26.2826087 × 0.5357142857 = 14.0799689461
Process 2 = 9.905797101 × 0.8571428571 = 8.490683229
Process 3 = 24 × 0.8571428571 = 28
So in this scenario would call for a ratio of around 1:0.6:2 (approximately) for processes 1, 2, and 3 respectively correct? Am I doing this right? I'm not a mathmagician, I would just rather have a computer do this for me and usually I can figure it out how to make a formula for this type of thing in excel or python or javascript or something, but I don't really have another way to check this specific one. Hopefully I have described what I'm trying to do well enough to be understood, if not please let me know if I can do anything to make it more clear. Thank you all in advance for any help you can provide!

You need to make your question a bit more clear. Can you clarify what you mean by parts and processes here? What is a batch? You should state your problem assuming that other know absolutely nothing about your problem and try to make it clear what you mean by all those words.

I work in a factory that produces parts, many different kinds, but that doesn't really matter. A batch is the amount of parts made, the final process they go through determine the size of the batch. That is why there are multiple durations of time listed for varying parts. The first process makes the parts, the second process prepares that batch of parts for the 3rd process, the final process has the same time duration and I don't believe that will change.

What does "Part 1 runs 49 batches a year" mean? Does it mean that the factory makes 49 batches of part 1 in a year?

Yes. I am using that to weight the time to process a batch of parts through process 1 and 2. So making 49 batches of part 1 a year will have its 30 hour time to process count more towards the average of all than part 3's average of 15 hours, because we would only make 20 batches per year.

This problem is very vaguely formulated, and because of that may not be answered properly.
Answer
 The questioner was satisfied and accepted the answer, or
 The answer was disputed, but the judge evaluated it as 100% correct.
 answered
 182 views
 $30.00
Related Questions
 Trying to solve this system of simultaneous equations. A solution with work shown would be appreciated.
 Solve this problem using branch and bound algorithm.
 Root of $x^2+1$ in field of positive characteristic
 Solving for two unknown angles, from two equations.
 Reverse this equation/function (2d to isometric)
 True or false
 Fields and Galois theory
 Sinusodial graph help (electrical)