# 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!

Andrewm

10

## Answer

**Answers can be viewed only if**

- The questioner was satisfied and accepted the answer, or
- The answer was disputed, but the judge evaluated it as 100% correct.

Erdos

4.6K

The answer is accepted.

Join Matchmaticians Affiliate Marketing
Program to earn up to 50% commission on every question your affiliated users ask or answer.

- answered
- 527 views
- $30.00

### Related Questions

- How do you prove that when you expand a binomial like $(a+b)^n$ the coefficients can be calculated by going to the n row in Pascal's triangle?
- Construction Estimate
- Is the $\mathbb{C}$-algebra $Fun(X,\mathbb{C})$ semi-simple?
- Induced and restricted representation
- Prove that language L = {a^p ; p is prime} isn't regular using Myhill-Nerode theorem.
- Let $R$ be an integral domain and $M$ a finitely generated $R$-module. Show that $rank(M/Tor(M))$=$rank(M)$
- Confused on this graph question, not sure how to reduce it to linear and It looks too wonky to draw a best fit line, probably won't take long
- Let $z = f(x − y)$. Show that $\frac{\partial z}{\partial x}+\frac{\partial z}{\partial y}=0$

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.