Dear Tom:


Many thanks for the help. Can I push the envelop a little further. Suppose we
now get the perfect mix on the first pass could there be a way to change the
values that were in the A1;E1 ,(in a different location) truncating the
extremes, ie a1 and e1 and apply this over again to columns h2:j2 etc until I
squeeze toward the center. Let me put it this way:

A manufacturer produces shirts sizes Xs,S,M,L,XL,XXl,XXXl. Suppose in a style
he considers the perfect distribution as 5,10,20,25,25,20,10,5 (could be any
mix). Once I have the perfect mix I want to
narrow down the number of set s I can get as I squeeeze the upper and lower
values.
I set a new distribution ideal on sizes s,m,l,xl,XXl then next pass on
M,L,Xl.


I don't know if I'm clear here but let me explain what I am driving at. We
ofter have to look at inventories for lenders of last resort and they want to
know their exposure on inventory. The middle sizes of the distribution are
alsways easy to sell and the extremes more difficult therefore we like to
eastablish difficult styles in an inventory that would be hard to sell. That
is why I started with the perfect dozen idea. It works great but I'm
wondering about producing other ideal mixes from the balances.

Holpe this is not too confusing.

Any ideas

Much appreciated


Ron

Tom Ogilvy wrote:

In F2 put in the formula:
=ROUNDDOWN(MIN(A3:E3/$A$1:$E$1),0)
enter with Ctrl+shift+enter rather than just enter since this is an array
formula

In G2 put in this formula

=A2-($F2*A$1)
and drag fill it to the left to cell K2

then select F2:K2 and drag fill down the column

--
Regards,
Tom Ogilvy

"Ronald Cayne" wrote in message
...
Help Please!

Columns A2:E5000 Holds Quantities on hand. A1:E1 contain the ideal mix.


Wante the number of ideal mixes in column F and the number of units left
over in columns G:K


What is the best calculation method to perfore the above.


eg A1:E1 1,2,4,4,1 (ideal dozen)

A2:E4

400,200,600,742,300
200,400,555,860,34

Ron