The Perfect Mix

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

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" <rcayne@videotron.ca> wrote in message
news:42551390.905B4DA8@videotron.ca...
> 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
>
>
>

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" <rcayne@videotron.ca> wrote in message
> news:42551390.905B4DA8@videotron.ca...
> > 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
> >
> >
> >

I understand some of what you are saying, but not what result you want or
what the specific rules are.

When you squeeze down, have you already subtracted the items from the first
mix and working with the remainder?

Maybe send me a small sample workbook with some explanation of what you
want.

twogilvy@msn.com

--
Regards,
Tom Ogilvy

"Ronald Cayne" <rcayne@videotron.ca> wrote in message
news:425B26E7.B7214899@videotron.ca...
> 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" <rcayne@videotron.ca> wrote in message
> > news:42551390.905B4DA8@videotron.ca...
> > > 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
> > >
> > >
> > >
>