Our process continuously feeds (normally <= 8) ingredients from a choice of hundreds, using 3 feeders only. So if ingredient count X is > 3, 3 through X are stirred together in a batch process and the resulting "premix" gets put in its own feeder. 6 is a hard limit for the number of ingredients a premix can have. It is possible to need to make 2 or even 3 premixes so that 2 or all 3 feeders feed premix. Unlikely but possible. This process is for experiments where we try 2 to 15 different recipes where we vary the ingredients and/or the weight-proportions. Hopefully this image conveys the situation.

spreadsheet snip

A person is deciding what ingredients get their own feeder, what goes in the premix, what experimental runs have enough in common to share a big premix, and what order to do the runs in to minimize changes. I'd like to make Excel and VBA make all those decisions for her. I can code it up once I get my mind around the problem. I can even derive a symbolic system to describe it, but I'm sure someone smarter than me already has. However I don't know what family of problems this is from. Can you point me in the right direction?

  • In stage one, you have a number of 6-to-1 convex linear combinations (meaning that you cannot feed a negative percentage of an ingredient into a mix). In stage two, you have a 3-to-1 convex linear combination. Your goal is to minimize the use of premixes (cough rank cough). – rwong Nov 29 '14 at 23:50
  • Thanks @rwong. I recognize it's the same problem twice and may be solved elegantly with some recursion. I've had some linear algebra. Yes to convexity (no negative feed rates). Am revisiting rank. Perhaps this will do it. – klausnrooster Nov 30 '14 at 0:21

Welcome to the classic use case for Linear Programming and it's more straitlaced cousin, Constraint Programming. More specifically it's an example of a convex hull in the form of a combination. You'd use Linear Programming (possibly via constraint programming) to solve the problem of finding the area of a convex hull that fits your constraints.

There are many, many tools that can do this. Excel's particular variation on this is a plug-in called "Solver".

  • Thanks @World Engineer. I have used Solver. I have an old textbook that includes LP. You and rwang are pointing the same way. I'll bone up on LP. You think it can be set up so solver has one cell (formula) to minimize? – klausnrooster Nov 30 '14 at 0:32
  • Possibly. It's been many years since I even tried to use Excel to do heavy lifting. – World Engineer Nov 30 '14 at 0:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.