3 tables to draw in part B is expected to be done on excel sheet. Part A are videos that show you how it is suppose to be done
MIS 2000 – Winter 2020 – Version 1
Excel Lab 2:
Frequently Used Functions &
Formatting Charts
IF, COUNTIF, COUNTIFS, SUMIF, SUMIFS
COUNT (COUNT, COUNTA, COUNTBLANK)
VLOOKUP,
Formatting charts
Instruction
The lab is individual. Each student must submit his/her own work.
For this lab, please download a workbook named “MIS2000-Excel-Lab2”, rename it as “MIS2000-Excel-Lab2-LastName-FirstName”. Replace “LastName” with your real last name and “FirstName” with your real first name.
Upload your completed file to the UMLearn digital dropbox named: LabExcel-2. Make sure you submit the correct file.
----------------------------------------------------------------------------------------------------------------------
Part A. YouTube Exercises
Function IF
Complete the worksheet named “2-1-IF” by following 2-1 YouTube video.
Microsoft video & description (about 1 minute)
https://support.office.com/en-us/article/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2
| Supplemental Materials: https://exceljet.net/excel-functions/excel-if-function |
Function COUNT
Complete the worksheet named “2-2-COUNT” by following 2-2 YouTube video.
YouTube video:
Title: Use the COUNT (COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS) Functions (about 3 minutes)
Note: in this video, the narrator is using the function window to enter the functions in cells G7 to G10, however typing the functions manually also works.
https://www.youtube.com/watch?v=qlEegBHlPMg
| Supplemental Materials: https://exceljet.net/excel-functions/excel-count-function https://exceljet.net/excel-functions/excel-counta-function https://exceljet.net/excel-functions/excel-countblank-function https://exceljet.net/excel-functions/excel-countif-function https://exceljet.net/excel-functions/excel-countifs-function |
Math Functions with IF
Complete the worksheet named “2-3-Math-withIF” by following 2-3 YouTube video.
YouTube video:
Title: Simple Explanation of Excel SUMIF, SUMIFS, COUNTIF & COUNTIFS (Multiple Criteria) (about 14 minutes)
https://www.youtube.com/watch?v=AZuBNWMh7VM
| TA’s note: Understanding all of these syntax will help you out a lot in Part B. |
| TA’s note: OPTIONAL: Video section 6:05 – 9:42 You are NOT obligated to have the Wildcard characters as part of your criteria for the exercise. You are not obligated to calculate more than what is provided on the given spreadsheet! You are not obligated to do AverageIFS! You are obligated to do SUMIF, SUMIFS, COUNTIF & COUNTIFS ------------------------------ OPTIONAL: Video section 11:30 – 13:30 You are not obligated to make an Excel Table here. Again, the video is teaching extra useful lessons and shortcuts for your future career! I suggest listening as you may be able to apply these methods to your future work! I know I am.
|
