{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

Decision Support Using Excel I

Decision Support Using Excel I - BMGT 301 Fall 2009...

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

View Full Document Right Arrow Icon
BMGT 301 Fall 2009 Instructor: Xiaoqing Wang Using Excel for Decision Support – BMGT 301 Excel Tutorial I Agenda: Excel basics Formula Pivot table Chart Conditional formatting What if analysis Goal seek 1. Excel Basics and Formula Excel is more than a data sheet. Open Microsoft Excel and open the tutorial file Excel1_tutorial_blank.xlsx . 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
Image of page 1

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

View Full Document Right Arrow Icon
BMGT 301 Fall 2009 Instructor: Xiaoqing Wang 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 . 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
Image of page 2
BMGT 301 Fall 2009 Instructor: Xiaoqing Wang 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 3
Image of page 3

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

View Full Document Right Arrow Icon
BMGT 301 Fall 2009 Instructor: Xiaoqing Wang 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
Image of page 4
Image of page 5
This is the end of the preview. Sign up to access the rest of the document.

{[ 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