Complicated sum
This problem is very simple once you understand the algorithm. There is an
implied requirement to use the least number of number of bobines. So you
want to to take the weight and divide by the maximum weight. This gives you
only three diffferent cases
Let W = Weight of order
1) The weight is less than the minimum wieght.
Solution: 1 bobine of the ordered weight W.
2) You divide the weight by the max weight and analize the remainder. If
the remainder is greater than the minimum you have your solution
Solution: Number of bobines is N = Int(W/400) + 1
Int is the integer part of the division.
You will have N bobines of 400 and
1 bobine of weight mod(W,400) where mod is the remainder
of the
division W/400
3) The same as case 2 except the remainder is less than the minimum. In
this case you take 100 off the last roll and then add mod(W,400)
Solution : Number of bobines is N = Int(W/400) + 1
You will have N-2 bobines of 400
You will have 1 bobine of 400 - 100 = 300
You will have 1 bobine of 100 + mod(W,400)
"Patrick Molloy" wrote:
I can't get my head around this I'm afraid.
We need an algorithm. That is, a sequence of logical steps ...
You're very close to this project, so it woudl help maybe if you took a step
back and started as if we're total beginners - which we are ;)
thanks
"LiAD" wrote:
Morning,
I have a problem which I have tried to post on the functions forum but no
answer. Its difficult to explain so Im not sure if people are understanding
what Im after.
In order to make copper wire it is wound onto bobines which can support a
maximum weight of 400kg. The machine that makes the bobines is very fast so
if it can avoided its better not to make bobines smaller than 100kg, however
if we must we can. If a customer needs 600kg of wire they will get a bobine
of 400kg and another of 200kg. To make it more difficult each product can be
based on several sub compositions. First of all Ill explain it for the
products made from single components if thats not understood then the full
solution will be completely lost.
Single components-
What I would like is a formula/macro that can look through the data and
group the products that are the same. In col A of the sheet named (Bobines)
I have my product list (A,B,C) in col B I have the individual weights of the
bobines required. In col C,D,E I would like to generate the results the
individual weights of the bobines to be produced. The sequence is
match the products, sum the weights then if:
- the sum is less than 400kg continue to add weights until the limit of 400
is reached
- the sum (or a component) is over 400kg continue adding until the orders
can be split into 100kg< bobine weight < 400kg.
- if no sum is possible just write the weight of the bobine as per the
original customer order.
- Where weights are being added to the next bobine just write nothing (blank
or ) as a result
- write the cumulative weights in the cell adjacent to the
last bobine that was added
(Just for ref these bobines are not actually sold then are then fed into
other processes so they will be resplit into the original orders further down
the line after additional work).
Results
Product Customer Wt Bob 1 Bob 2
B 390 390
B 90 90
C 400 400
C 90 90
A 420 ---
A 90 400 110
D 600 ----
D 90 400 290
B 50 ----
B 100 150
Multiple components
The added difficulty here is that the formula/code needs to look in several
places to produce the result as it can match with the product in col A OR col
C AND continue as long vertically as it wants until it reaches the limits of
100kg < xx < 400kg. In total there are four positions in which to match
between consecutive rows (A-A, A-C, C-A and C-C).
Results
A 100 --- --- --- ---
A 100 --- --- --- ---
A 50 A 75 --- --- A 425
B 150 B 55 --- --- --- ---
B 175 C 65 B 380 --- ---
B 55 C 75 B 55 --- ---
C 125 D 85 C 200 D 85
B 20 A 50 B 20 A 50
In both cases only items that are adjacent on the list should be grouped
irrespective of weights, (hence why the last group of A&Bs are not grouped
with the first lot).
It is the second case of multiple components that I need to get to as a
working result.
Is this iterative procedure possible?
I guess a macro is the easiest way to go otherwise itll be a never ending
list of IF formulas.
Thanks
LiAD
|