SOLVED

Solver tool: optimization question

%3CLINGO-SUB%20id%3D%22lingo-sub-3240653%22%20slang%3D%22en-US%22%3ESolver%20tool%3A%20optimization%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3240653%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20what%20seems%20to%20be%20a%20normal%20optimization%20problem%2C%20but%20I%20can't%20find%20a%20way%20to%20avoid%20circular%20references.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20optimize%20the%20quantities%20X%20and%20Y%20(i.e.%20I%20want%20to%20determine%20B11%20and%20B12%2C%20marked%20in%20green)%20of%20a%20certain%20product%20so%20that%20the%20total%20output%20of%20both%20products%2C%20i.e.%20sumproduct%20(D5%3AD6%3BB11%3AB12)%2C%20is%20maximized.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEach%20product%26nbsp%3Brequires%20a%20given%20number%20of%20liters%20of%20oil%20and%20technician%20hours%20(B5%3AC6).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20several%20input%20fields%20(all%20marked%20in%20blue)%20which%20can%20be%20changed%3A%20output%20per%20unit%20X%20and%20Y%2C%20total%20liters%20of%20oil%20available%2C%20total%20hours%20of%20technicians%20available%20and%20sum%20of%20quantities%20for%20X%20and%20Y.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20using%20the%20solver%20tool%20to%20maximize%20the%20total%20output%2C%20i.e.%20sumproduct%20(D5%3AD6%3BB11%3AB12)%2C%20with%20the%20constraints%20for%20oil%20(G4%3DB5*B11%2BB6*B12)%2C%20hours%20(G5%3DC5*B11%2BC6*B12)%20and%20quantity%20X%20%2B%20Y%20(G6%3DB11%2BB12)%2C%20I%20can't%20seem%20to%20avoid%20circular%20references%20in%20defining%20X%20and%20Y.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20helpful%20for%20every%20hint%20to%20solve%20the%20question.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESJ%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22IMG_1152.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F352337i8E0992DB7FEE1A5C%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22IMG_1152.jpg%22%20alt%3D%22IMG_1152.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3240653%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3242446%22%20slang%3D%22en-US%22%3ERe%3A%20Solver%20tool%3A%20optimization%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3242446%22%20slang%3D%22en-US%22%3E%3CP%3Ethe%20question%20has%20been%20solved.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,

 

I have what seems to be a normal optimization problem, but I can't find a way to avoid circular references.

 

I would like to optimize the quantities X and Y (i.e. I want to determine B11 and B12, marked in green) of a certain product so that the total output of both products, i.e. sumproduct (D5:D6;B11:B12), is maximized.

 

Each product requires a given number of liters of oil and technician hours (B5:C6).

 

There are several input fields (all marked in blue) which can be changed: output per unit X and Y, total liters of oil available, total hours of technicians available and sum of quantities for X and Y. 

 

When using the solver tool to maximize the total output, i.e. sumproduct (D5:D6;B11:B12), with the constraints for oil (G4=B5*B11+B6*B12), hours (G5=C5*B11+C6*B12) and quantity X + Y (G6=B11+B12), I can't seem to avoid circular references in defining X and Y.

 

I am helpful for every hint to solve the question.

 

Thanks

 

SJ

 

IMG_1152.jpg

1 Reply
best response confirmed by Stefan_Joaquin (New Contributor)
Solution

the question has been solved.