Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
The Perfect Mix
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
The Perfect Mix
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
removing zeroes almost perfect | Excel Worksheet Functions | |||
how many perfect score | Excel Discussion (Misc queries) | |||
How to fit each cell into a perfect square? | Excel Discussion (Misc queries) | |||
Making the perfect sort. | Excel Discussion (Misc queries) | |||
looking for my perfect algorithm | Excel Discussion (Misc queries) |