Decision Support Using Excel I

# Decision Support Using Excel I - BMGT 301 SPRING 2012...

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

BMGT 301 SPRING 2012 1 Decision Making Using Spreadsheets – I Learning objectives Excel Basics Formula and Functions What-if analysis and Goal seek Excel is more than a data sheet. Open Microsoft Excel and open the tutorial file InClass_Tutorial.xls . Under worksheet ‘ Basic Formula’ , it should contain data as shown above. This is a list of students and their two exam scores (out of 100). One of the student, John Edwards, missed the first exam. In Excel, you refer to a cell by its ‘address’, i.e., its corresponding column and row headers. For instance, cell A2 has a value of ‘ Amewu ’.

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

View Full Document
BMGT 301 SPRING 2012 2 3 Formula Excel has many useful built-in formulas for you to use. For instance, ADD, SUM, AVERAGE, STDEV, COUNT, LARGE, etc. A formula starts with the ‘=’ sign. Try a few of them: (1) Calculate total scores in column E: In cell E1 , type in ‘ Total scores ’. In cell E2 , enter formula =C2+D2 ’. The cell should return the number 140 . Enter formula in similar ways in E3 to E7 . Or, you can copy and paste the formula from E2 to the other cells and see the formulas change automatically. In Excel, if you want to see all the formulas in a worksheet (instead of the results of the formulas), press the CTRL key and the “back-quote” key (`) (the same key as the ~ mark) to toggle between the formula view and the result view. . (2) Calculate simple statistics: In cell B9 , enter ‘ High Score ’. In cell C9 , enter formula ‘= MAX(C2:C7) ’. The cell should return the number 94 . Similarly, enter ‘ Low Score ’ in cell B10 , and enter ‘= MIN(C2:C7) ’ in cell C10 . The cell should return the number 90 . Again, enter Average ’ in cell B11 , and enter ‘= AVERAGE (c2:c7)’ in cell C11 . The cell should return the number 92.33 . Finally, enter ‘ Second Highest Score ’ in cell B12 , and enter ‘= LARGE(c2:c7,2)’ in cell C12 . The cell should return the number 93 .
BMGT 301 SPRING 2012 3 4 Formula To find out how many students attended the exam, we can use a COUNT function to count the number of students who received a score. In cell B13 enter ‘ Exam Attendance ’, and in C13 , enter formula ‘ = COUNT(C2:C7) ’. This should return the number 5 . To find out how many students received a score above 93, we can use a COUNTIF function, which is a Count function with a condition. In cell B14 enter Number Of Students >93 ’. In C14 , enter formula =COUNTIF(C2:c7,”>93”) ’. The condition for the COUNTIF function needs to be in quotation marks “”. Similarly, you can use the formula ‘= COUNTIF(C2:C7,””) ’ to find out how many students were absent in the exam. 5 Forumula The IF function

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

View Full Document
BMGT 301 SPRING 2012 4 The IF function always has three arguments: =IF(logical_test, value_if_true, value_if_false) To calculate the average exam score, enter ‘ Average Score ’ in cell F1 . And, in cell F2 , enter =E2/2 ’. Then, to get a grade based on the average score, enter the following formula in cell
This is the end of the preview. Sign up to access the rest of the document.

## This note was uploaded on 02/28/2012 for the course BMGT 301 taught by Professor Wang during the Spring '08 term at Maryland.

### Page1 / 18

Decision Support Using Excel I - BMGT 301 SPRING 2012...

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

View Full Document
Ask a homework question - tutors are online