Calculating
the answers for the objective function and constraints
1. Enter the calculation into the function answer cells, this can be done one of two ways:
· Manually: The answer to the objective function can be calculated as the sum of the products of the multiplication of each variable by its coefficient. For Example, in Excel we can enter the formula in the answer cell of the objective function (L6 as we have set up the problem) to calculate this value for us by entering the formula:
·
=B$4*B6+C$4*C6+D$4*D6+E$4*E6+F$4*F6+G$4*G6+H$4*H6+I$4*I6+J$4*J6
· Using a Built-In Function: However, entering long formulas can be a time- consuming process, so Excel has a set of pre-programmed ‘functions’ that enable us to perform standard calculations. The answer to the objective function can be calculated using Excel’s built in sumproduct function. Instead of the long formula above, we can simply enter:
=sumproduct(variables,
B6:J6)
The word ‘variables’ is the name we gave to the range of variable value cells.
‘B6:J6’ is Excel’s notation specifying the range of cells from B6 to J6.
Notice that the cell range B6:J6 is a relative reference, not an absolute reference, which could be entered $B$6: $J$6. A range name, like ‘variables’ is always an absolute reference. In this problem, we want the reference to the variables to be absolute, and the reference to the function coefficients to be relative so that we can accurately copy this calculation to the constraint function answer cells.
2. Copy the formula in L6 to the rest of the Answer column. Highlight cell L6, and then go to the ‘Edit’ menu and select ‘Copy.’ Now select the ‘Answer’ column cells for all of the constraint rows, L7-L12 in this example. Then, go to the ‘Edit’ menu again and select ‘Paste’. You have now pasted the equation from the objective function into each row’s ‘Answer’ cell with the proper operands.
3. Enter the value for each constraint in the far right hand column. Take note that the column between the answer and the constraint limit value is a column filled with labels that help us understand what is going on. Don’t forget to enter an apostrophe before these labels.