Forum Discussion
Need help with solving an equation that has the variable in the equation twice
Hi,
I'm working on a spreadsheet that requires finding the following:
Maximum Capacity = Batch Size ÷ (Setup Time + Batch Size x Time per unit) where Batch size is the Variable. I want to be able to enter the given numbers into cells, and have the Variable computed so I can change the numbers for different inputs. The numbers in this so far are:
.05 = X ÷ (60+X*10)
How can I have this solved by Excel? Thank you for any help.
2 Replies
Let's say Max Capacity is in A2, Setup Time in B2 and Time per Unit in C2. We want Batch Size in D2.
The equation can be written as:
A2 = D2/(B2+D2*C2)
Multiply both sides with (B2+D2*C2):
A2*(B2+D2*C2) = D2
Evaluate the expression on the left:
A2*B2+A2*D2*C2 = D2
Subtract A2*D2*C2 from both sides:
A2*B2=D2-A2*D2*C2
Factor out D2 on the right hand side:
A2*B2 = D2*(1-A2*C2)
Divide both sides by (1-A2*C2):
A2*B2/(1-A2*C2) = D2
And there we have our formula for D2 (i.e. X):
=A2*B2/(1-A2*C2)
Enter the parameters in A2, B2 and C2 and D2 will show the Batch Size needed.
You may want to round up or down to a whole number by wrapping the above expression in ROUND.
HansVogelaarHans, Thank you so much for this help. I'm not great at math and I'm going to try this on a few different equations/examples, but plugging in that formula you gave me, the exact answer came out. So, so far it works ('m not doubting you, I'm doubting me). If I have any other problems, I'll reply again. Once again thank you for your time sir. Trying to solve this was taking me forever.
Respectfully,
Brian
