coditional? If? diffrent
Posted by Squirrel on March 19, 2001 8:49 AM
Okay I have yet seen Dave and Mark stumped, and I have gained alot from reading your answers, and some I still do not understand but I am working on a tool to make my life simple at work. here goes:
(milk plant production)
12 Machines (worksheet for each machine)
Col G contains product no# ( 1-2002 unsorted)
Col I contains actual units run if any
3 products we will call "flush" Buttermilk=90 min. and choclate=30min and Juices run after dairy products=30 min.
What would I use to look at the acutal units after the "Flush and return the total min. on a page named EFF.
Keep in mind that if 1 or 5 products run after each of the 3 products I only need to count 1 flush time for each and "0" if no product is run after.
If you need an example tell me how to get it to you.
Thanks in Advance, Squirrel
Posted by Mark W. on March 19, 2001 9:16 AM
Squirrel, are you trying to determine an optimal
product mix to minimize production time? If so,
this looks like a problem well suited for Solver.
Are you familiar with concepts of linear programming?
Have you looked at the Solver Help topics?
Posted by Squirrel on March 19, 2001 1:31 PM
Re: not mix
No Sir, to help you understand what I am trying to do, it is information for me to key into an access effeciency program that our corp. office requires, ie total product changes, flush changes size changes that each of the 12 bottling machines produce each day,
example:
time occurences
product changes 25 (5)
Size Change 10 (2)
flush change 90 (1)
label change 50 (10)
I have worked out the formula for everything except size and flush changes
Posted by Mark W. on March 19, 2001 1:38 PM
Okay...
Would you like to submit some sample data, say 8 products,
for each of 2 machines? Also, include what you
believe to be the solution (i.e, the resultant
data that you'd feed to your corporate Access system.
Posted by Squirrel on March 19, 2001 2:31 PM
Re: Okay...
I created a workbook with the info you asked for and no macros, is that how you want it and if so how do you want it sent to you?
Posted by Mark W. on March 19, 2001 2:50 PM
Re: Okay...
I'd prefer not to go off-line because others wouldn't
benefit from the exchange of ideas. What I'd prefer
is that you paste the limited amount of data that I
requested into a follow-up posting as an array
constant. Also include the cell range from which
you obtained the data. For example, a grocery
list in Sheet1!A1:B5 might look like:
{"Item","Qty";"Bananas",5;"Captain Crunch",1;"Ben & Jerry's",2;"Yogart",10}
This array constant was created by selecting an
unused worksheet cell, typing an equal sign,
selecting cells A1:B5, pressing Ctrl+=, copying
the array constant in the formula bar and pasting
it into this posting. Got it?
Also, don't forget to share any pertinent formulas,
concepts and definitions that are needed to create
your results for your corp. database.
Will this be okay?
Posted by Squirrel on March 20, 2001 6:25 AM
Re: Okay...
I hope I did this right
Corp Access wants, how many flushes on a given day + time. 2 products require a flush after being run ,buttermilk = 90 min,Choclate = 30 min
(worksheet2) EFF
B:10 = time in min. C:10=occurrence
(worksheet4)filler 1
Products={"Skim";"Homo";"S Acid";"Butter Milk";"HALF/HALF";"Cream Raw";"2%";"2%Bag Box";"Choc.";"Orjuc"}
actual units run(col. L)
={20;50;0;100;0;0;10;10;0;0}
the 4th being buttermilk (100), if 1 product is run after buttermilk it will be a 90 min flush ,this example shows 2% milk being run(10)
=90 (1)
actual units run={20;50;0;100;0;0;10;0;10;10}
this example shows that a product was run after buttermilk and choc.
=120 (2)
actual units run={20;50;0;0;0;0;10;0;10;10}
this example shows that a product was run after choc. only
=30 (1)
Posted by Mark W. on March 20, 2001 11:05 AM
Re: Okay...
Squirrel, first let's cover a few "housekeeping"
items. In your posting you didn't specify which
cells contain your Product list. For the record,
I put them in 'filler 1'!A1:A10. Also, I trust
that the "actual units run" was in cells
'filler 1'!L1:L10. That being said...
You'll need to reserve a column for some
intermediate calculations. I used cells
'filler 1'!M1:M10 for this purpose.
Here's what you need to do:
1. Create a Defined Name called "Flush_Table"
using a reference of ={"Butter Milk",90;"Choc.",30} .
2. In cell 'filler 1'!M1 enter the formula,
{=IF(AND(A1<>A2,OR((A1=INDEX(Flush_Table,0,1))*L1*SUM(L2:OFFSET(L2,COUNTA(A:A)-1,,1)))),VLOOKUP(A1,Flush_Table,2,0),0)} .
This is an array formula which must be entered
without typing the braces, {}, using the
Shift+Ctrl+Enter key combination.
3. Copy the formula in 'filler 1'!M1 down to
cell 'filler 1'!M10.
4. On your EFF worksheet enter the formula,
=SUM('filler 1'!M:M), into cell B10.
5. On your EFF worksheet enter the formula,
=COUNTIF('filler 1'!M:M,"<>0"), into cell C10.
That's it! Let me know what you think.
