Management Science Chapter 1 and 2 notes.docx

The sumproduct function is used extensively in linear

This preview shows page 15 out of 44 pages.

The SUMPRODUCT function is used extensively in linear programming spreadsheet models. The formulas in the output cells E7:E9 are very similar. Rather than typing each of these formulas separately into the three cells, it is quicker (and less prone to typos) to type the formula just once in E7 and then copy the formula down into cells E8 and E9. To do this, first enter the formula = SUMPRODUCT(C7:D7, UnitsProduced) in cell E7. Then select cell page 30 E7 and drag the fill handle (the small box on the lower right corner of the cell cursor) down through cells E8 and E9. When copying formulas, it is important to understand the difference between relative and absolute references. In the formula in cell E7, the reference to cells C7:D7 is based upon the relative position to the cell containing the formula. In this case, the relative position is the two cells in the same row and immediately to the left. This is known as a relative reference. When this formula is copied to new cells using the fill handle, the reference is automatically adjusted to refer to the new cell(s) at the same relative location (the two cells in the same row and immediately to the left). The formula in E8 becomes = SUMPRODUCT(C8:D8, UnitsProduced) and the formula in E9 becomes = SUMPRODUCT(C9:D9, UnitsProduced). This is exactly what we want, since we always want the hours used at a given plant to be based upon the hours used per unit produced at that same plant (the two cells in the same row and immediately to the left). In contrast, the reference to the UnitsProduced in E7 is called an absolute reference. These references do not change when they are filled into other cells but instead always refer to the same absolute cell locations. To make a relative reference, simply enter the cell addresses (e.g., C7:D7). By contrast, references referred to by a range name are treated as absolute references. Another way to make an absolute reference to a range of cells is to put $ signs in front of the letter and number of the cell reference (e.g., $C$12:$D$12). See Appendix A for more details about relative and absolute referencing and copying formulas. You can make the column absolute and the row relative (or vice versa) by putting a $ sign in front of only the letter (or number) of the cell reference. Next, ≤ signs are entered in cells F7, F8, and F9 to indicate that each total value to their left cannot be allowed to exceed the corresponding number in column G. (On the computer ≤ (or ≥) is often represented as < = (or > =), since there is no ≤ (or ≥) key on the keyboard.) The spreadsheet still will allow you to enter trial solutions that violate the ≤ signs. However, these ≤ signs serve as a reminder that such trial solutions need to be rejected if no changes are made in the numbers in column G. One easy way to enter a ≤ (or ≥) in a spreadsheet is to type < (or >) with underlining turned on.
Image of page 15
You've reached the end of this preview.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern