Download CSE 2111 Data Analytics: Understanding MS Excel All Lectures 2024 and more Exams Advanced Data Analysis in PDF only on Docsity!
CSE 2111
DATA ANALYTICS: UNDERSTANDING MS EXCEL
ALL LECTURES
The Ribbons:
- (^) Home Ribbon: Contains many tools
- Clipboard Group (cut, copy, paste, format painter tool, )
- Font
- Paragraph
- Number
- Styles
- Cells
- Editing - (^) Insert Ribbon: Groupings for inserting tables, illustrations, charts, links, and specific textual elements(headers/ footers), WordArt, etc. - (^) Page Layout Ribbon: The general layout and print features can be modified. Contains:
- Themes
- Page Setup
- Scale to Fit
- Sheet Options
- Arrange - (^) Formulas Ribbon:
Useful when entering calculations. Contains:
- Insert function botton
- AutoSum commands
- Drop down menus to access many of the most commonly used functions (logical functions, text functions, lookup and reference functions, etc.)
- Defined Names group (with features for specifying and using named ranges) - (^) Data Ribbon: Contains tools for managing data. Also provides access to tools to help “clean-up” data by dividing text into columns or removing duplicates.
Basic Functions and Cell Referencing
Function Wizard : a short-cut to all functions in Excel Use fx toolbar button) that walks you through building a function Common Functions:
e.g. COUNT/COUNTA e.g. SUM/AVERAGE/MIN/MAX Round Function: Syntax: =ROUND(number, num digits) number: number to be rounded num digits: number of decimal places e.g. COUNT: only count the numbers in the specific range COUNTA: count cells that are not blank in the specific range
Named Ranges: A range of cells that are named and used instead of the cell reference when using a calculation or function. (treated as an absolute cell reference) e.g.
Cell Addressing Tips:
1. Type in correct formula
2. Do I need to copy the formula down the row/across the column?
3. Yes: only need to worry about the letter (columns) / numbers (rows) in the cell
addressing.
- Copy the formula across / down
- Are the cell references correct:
- Yes: No $ needed
- No: go back to original cell and insert the correct $ in the correct letter (column) / number (row) in the cell addressing.
Statistical Functions - 1
COUNTIF Function
Counts the number of items in a range that meet a specific criteria. =COUNTIF(range, criteria) Range: A continuous range Criteria: Determines what cells to count e.g. COUNTIF Function Syntax e.g.
Statistical Functions - 2
SUMIFS Function
Sums a range (using multiple criteria and multiple ranges) that meet a specific criteria All criterion must be true in order for the cell to be summed =SUMIFS(sum_range, criteria_range1,criteria1,[criteria_range2,criteria2], …) Sum_Range: Range to sum if criterion are met Criteria_Range1: Range of first criteria Criteria1: Criteria for range Criteria syntax rules the same as COUNTIF e.g.
AVERAGEIF Function
Averages the number of items in a range that meet a specific criteria =AVERAGEIF(criteria_range, criteria,[average_range]) e.g.
find more resources at oneclass.com
AVERAGEIFS Function
Averages a range (using multiple criteria and multiple ranges) that meet a specific criteria =AVERAGEIFS(average_range, criteria_range1,criteria1[criteria_range2,criteria2], …) All criterion must be true in order for the cell to be averaged. e.g. G10=AVERAGEIFS(C6:C9,B6:B9,B2,C6:C9,”<"&G2)
LARGE Function
Returns the largest value in a range, based on the “k” argument =LARGE(array, k) Array: A continuous range k: The position (from the largest) in the array (range)
SMALL Function
Returns the smallest value in a range, based on the “k” argument =SMALL(array, k) Array: A continuous range k: The position (from the smallest) in the array (range) e.g.
Boolean Expressions
Return or display the Boolean value TRUE or FALSE Syntax example: Boolean Functions
1. =AND(logical1, [logical2], …) Returns TRUE if all arguments evaluate to true
2. =OR(logical1, [logical2], …) Returns TRUE if at least one argument evaluates to true 3. =NOT(logical) Changes FALSE to TRUE and TRUE to FALSE
Boolean Expressions Exercise
1. Write a formula in cell Analysis!B3, which can be copied down, to determine (T/F) if
the corresponding student organization traveled to all 3 locations.
Answer: =AND(Trips!B3>0,Trips!C3>0,Trips!D3>0,)
- Write a formula in cell Analysis!E3, which can be copied down, to determine (T/F) if the corresponding student organization traveled to 2 or more locations. Answer: =COUNTIF(Trips!B3:D3,“>0”)>=
- Write a formula in cell Analysis!F3, which can be copied down, to determine (T/F) if there were more trips to Italy than Spain for the corresponding Student Organization. Answer: =Trips!B3>Trips!C
- Write a formula in cell Analysis!E6, to determine T/F if all of the student organizations traveled to all locations. Answer: =AND(B3:B5)
- Write a formula in cell Trips!H3, which can be copied down, to determine (T/F), if the corresponding student organization needs more sponsors. An organization needs more sponsors if each trip cost is greater than $1,000,000 or (the total number of trips is greater than 10 and the total number of sponsors is 7 or more). Answer: =OR(AND(Cost!B$2>1000000,Cost!B$3>1000000,Cost! B$4>1000000),AND(SUM(B3:D3)>10,SUM(E3:G3)>=7))
