Decision Support Using Excel tutorial

Decision Support Using Excel tutorial - BMGT 301 Spring...

Info iconThis preview shows pages 1–4. Sign up to view the full content.

View Full Document Right Arrow Icon
BMGT 301 Spring 2011 Decision Making Using Excel – 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 Excel1_tutorial_blank.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 ’. 1
Background image of page 1

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

View Full DocumentRight Arrow Icon
BMGT 301 Spring 2011 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 . 2
Background image of page 2
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
Background image of page 3

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

View Full DocumentRight Arrow Icon
Image of page 4
This is the end of the preview. Sign up to access the rest of the document.

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

Page1 / 12

Decision Support Using Excel tutorial - BMGT 301 Spring...

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

View Full Document Right Arrow Icon
Ask a homework question - tutors are online