Decision Making using Excel II

# Decision Making using Excel II - BMGT301 Excel II Spring...

This preview shows pages 1–5. Sign up to view the full content.

BMGT301 Excel II Spring 2011 Decision Making using Excel – II Learning objectives Conditional Formatting Charts Solver Pivot table I. Conditional Formatting Another useful feature of Excel is Conditional Formatting : Excel allows you to format certain cells that meet a particular criteria. For example, we want to change the text color of the students who get an “F” to red, or change the text color of the students who get a “B” to blue. We may use Conditional Formatting to achieve that. First highlight cells A2 to H2 , and under Home tab, on the Styles group, click on Conditional Formatting . The Conditional Formatting Rules Manager window will pop up after you select Manage Rules . 1

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
BMGT301 Excel II Spring 2011 Click on New Rule and select Use a formula to determine which cells to format . Under Format values where this formula is true , put in “ =IF(\$G2=”F”,”True”) ”, and click on the Format to select red color for the font. Clicking OK will bring you back to the rule manager. Repeat the same procedure for the “C” and “B” grader. After you are done with adding the condition, close the rule manager by clicking OK . The row for “Amewu” should now be in brown. Use the format brush to apply the rules to other rows. II. Charts – Visualized Data 2
BMGT301 Excel II Spring 2011 To create a chart, let’s select the worksheet “ Basic Formula ” again. Select all the data you want to include in the chart. Let’s say we want to include the student’s “ First name ”, “ Last name ”, Score1 ”, “ Score2 ” and “ Average Score ”. Highlight the cell from A1 to D7. With the cells highlighted, press “CTRL” and then highlight the cells from F1 to F7 as well. Now on the Insert tab, under the Charts group, expand Column chart and select the 2D clustered column chart. The chart will show up in your worksheet. Then, On the Design tab, under Chart Layouts group, select Layout 5. Double click on the “ Chart Title ” to modify it to “BMGT 301 Fall 2008”. Do the same to change “ Axis Title ” to “Score”. You are done! 3

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
BMGT301 Excel II Spring 2011 III. Solver Excel has an easy-to-use add-in, called Solver , for problem solving. Solver comes with the standard Excel package, but it has to be installed. To install Solver, refer to the SolverAddInforExcel.pdf posted on BB (also distributed in class) Example: A firm manufactures two types of markers -- dry erase and permanent. Each is made from the following components: ink and plastic. A dry erase marker requires 0.1 fl. oz. of ink, 0.8 oz. of plastic and sells for \$0.25. A permanent marker requires 0.25 fl. oz. of ink, 1 oz. of plastic and sells for \$0.40. The firm has 5000 oz. of plastic and 1000 fl. oz. of ink available. All markers manufactured will be sold. How many of each type of marker should the firm manufacture to maximize its revenues? The major requirements are:
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

### Page1 / 14

Decision Making using Excel II - BMGT301 Excel II Spring...

This preview shows document pages 1 - 5. Sign up to view the full document.

View Full Document
Ask a homework question - tutors are online