Textbook Notes (363,149)
Canada (158,223)


20 Pages
Unlock Document

McMaster University
Computer Science
Anthony Hurst

Comp Sci Notes: EXCEL Session 1: • Excel stores spreadsheets in workbooks • Name box- displays cell reference of active cell • Select All- selects all active cells • Row Headings- numbered rows • Formula bar-displays value • Workbook is made of sheets- worksheets and chartsheets • Sheet Tab scrolling scrolls through sheets • Zoom controls zoom in out • Column headings- letters identifying different columns • What-if analysis- change one value, changes the others • Ribbon- top heading bar • Chart sheets contain visuals and can be placed in Worksheets • Ctrl page up and down changes sheets • Planner sheet- what problems solved? What data needed? What calculations entered? What form should my solution be in? • Text data= text string • Text if left aligned, numbers are right aligned • Truncated- hidden texts (cells too small) • Autocomplete-feature that tries to predict what you are typing in the cell reference box • Alt and Enter make multiple lines in a cell • A pixel is a single point on a computer monitor • A point Is 1/72 of an inch • Autofitting fits all data into biggest cell throughout column • Clearing-removes data Deleting- removes data and cells • Spellchecker • Page Layout tab- changes way worksheets are appeared • Group of cells is a cell range • Status bar- ready or in edit mode • Non adjacent range- two or more distinct non adjacent ranges • View- changes how viewed • Adjacent range- group of cells in single block of cells • Auto Sum- Sum average etc, formulas • Range Reference- location and size of range • Drag and drop- drag range and replace it • Cutting- cut and paste • Operators- used to make formulas combining diff values making one value • Arithmetic operators- perform additions subtraction etc • Order of precedence- is a set of predefined rules used to determine sequence in which operators are used. • Functions- named operation performs a value • Edit mode- shortcuts only apply to texts • Find command to locate characters- search string-replacement string • Normal View, Page Break Preview (page breaks), Page layout view (printed) • Portrait and Landscape • Formula view- only formulas viewed • A formula is an expression that returns a value Begins with = Operators combine different values Generally arithmetic operators ex Sum, Divide Usually use numbers stored within cells Order of precedence applies (OOO) • To enter a formula: Type in a cell Select cells Verify via F2 (which cells are in formula) • Scaling- fitting pages to print • Rows and Columns intersect at cells- cell reference • Intersection of a row and column is a cell • The file tab opens backstage view- file level options and settings • Related commands are organized in groups on a tab • The dialog box launcher opens options for a tab • F1- help key • 3 types of DATA - Text: a combination of letters, numbers, and some symbols that form words and sentences -Number: any numerical value that can be used in a mathematical calculation -Date and Time: commonly recognized formats for date and time values • The main reason to use excel is to display values calculated Session 5 • To enter a function: - Type - select cells - Function wizard • AutoSum can be used for Sum, Average, Count, Minimum, and Maximum - Be cautious as this function makes assumptions, use F2 or double click to verify • To edit, F2 editing mode, esc to erase edits and enter to complete them • Table name • Each column represents a field • Header row, beginning row • Rows represent a record • Filter order- filters records • An Excel Table is a structured range of data that is managed independently from the rest of the data in a worksheet. Why do we use excel tables? - Can format table quickly - The table range is expanded automatically - Can add a total row to calculate summary functions - Formulas entered in one table cell are automatically copied down - Can create formulas that reference cells in a table using table and column names, rather than cell addresses - Can sort and filter data easily • Sorting - columns filtered -Can be applied to one or more columns -Custom sorting lists can be created • Filtering -Can be applied to one or more columns -Can filter by exact values or broader criteria filters • Data definition table- list fields to be maintained for each record • Freezing lets you keep headings • Totals and Subtotals -Totals available through the “Total Row” box -Subtotals can only be applied to ranges -Sort the data by the column you want to subtotal -“Convert to Range” -Data tab, Outline group, Subtotal button -“At each change in” (sorted column) -Outline levels allow you to view the data in various levels of detail • Convert table to range for subtotals etc hides total row • Structured References -You can replace the specific cell or range address with a structured reference, the actual table name or column header -Syntax: -Tablename[qualifier] -Example: Summary [Sales] • Qualified vs. unqualified references -Unqualified references don’t include the table name. -You can use unqualified references in a calculated column or formula within an Excel table, however you need to use qualified references outside the table or in another worksheet when referring to an Excel table or a portion of the table. • Ascending order filters a-z, descending z-a • First sort field is primary, second is secondary • Custom list- indicates sequence you want data to be ordered • Selections list- displays unique items in selected column • Split bar- division between panes • Clear- removes filters • Criteria filters- enable you to specify various conditions in addition to those that are based on an equal’s criterion. • The layout is the four categories you can see in the bottom right corner of the pivot chart layout • A Pivot table- puts data in a concise table -Sometimes large tables can be difficult to interpret -PivotTables allow us to summarize data into categories using functions (detailed data is not shown) -Quick and concise analysis -Named as such since data can easily be “pivoted” or moved around in the table • Terminology of Pivot Tables -Values fields- the fields containing the data to be summarized -Category fields- the fields that the value fields will be grouped by -Rows -Columns • PivotChart- of a table • Slicer- filters pivot charts and tables • Category Fields- are the fields that group the values in a pivot table. • Filtering a field helps focus on one thing Session 7: • Structured Reference- When you create a formula that references all or parts of an Excel table, you can replace a specific cell or range address with a structured reference which is the actual table name or column header • The AND FUNCTION- logical function that returns a TRUE value if ALL are true, and a FALSE value if any or all of the logical conditions are false, used for multiple conditions. -Syntax: =AND (logical1 [,logical2]…) Can nest in an IF statement as the logical test Reminder: =IF(logical_test, value_if_true,[value_if_false]) Example: =IF(AND(A1=1,a2=1),”yes”,”no”) • Functions Argument box, box to build functions • The IF FUNCTION is a logical function that evaluates a condition, and then returns one value if the condition is true and a different value if it’s false -Syntax: =IF(logical_test, value_if_true, [value_if_false]) • Calculated Column- formula in one cell=all cells • Use AND function as logical test of IF function to test two or more conditions to see if all are TRUE • To determine if ANY are TRUE, use OR function as logical test of the IF function • OR Function -Allows you to test multiple conditions -Returns either TRUE or FALSE -Returns TRUE if any tested values are TRUE and FALSE if all of the tested values are FALSE -Syntax: =OR (logical1 [,logical2]…) -Can nest in an IF statement as the logical test -Reminder: =IF(logical_test,value_if_true,[value_if_false]) -Ex. =IF(A1=1,A2=1),”yes”,”no”) • A logical condition is an expression such as H2=”Y” that returns either a TRUE or FALSE value. • A NESTED IF function is when one IF function is placed in another to evaluate another condition -Nested IF Functions =IF([Pay Grade]=1,2500, IF([Pay Grade]=2,5000, 7500)) Structured references make the formula easier to write, use absolute reference b/c this value is the same for all rows • LOOKUP TABLE -A table that organizes data you want to retrieve into different categories -The categories for the lookup table, called compare values, are located in the table’s first column or row -To retrieve a particular value from the table, a lookup value (the value you are trying to find) needs to match the compare values • Using the lookup table to find an exact match -VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) • Change range to make an approximate lookup match • The LOOKUP VALUE is the value you are trying to find. • Invalid code- IFERROR • VLOOKUP function searches vertically down the first column of the lookup table and retrieves the appropriate value from the table • Lookup functions allow you to use tables of data to lookup values insert them in another worksheet location. • Absolute reference- when you only reference on condition and one formula that changes throughout the field, this is when you need to use absolute referencing • Relative Referencing- when using one formula that relates to all the other fields you can just drag it down and relative reference the formula with all cells. • An EXACT match lookup occurs when the value entered must match one of the values in the first column of the lookup table. • Approx match lookup occurs when the value entered falls within a range of values in the first column of the lookup tables • HLOOKUP function searches horizontally across the top row of the lookup table and retrieves the value in the column you specify. • HLOOKUP syntax: HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) • Errors values indicate that some element of the formula or a cell referenced in a formula is preventing excel from returning a calculated value. • Formatting (colors, highlighting etc.) • Conditional Formatting- change format when something specific occurs • Create a new rule in conditional formatting box- Rules manager- type of formatting when something occurs • It defies the condition cell and range to put the rule on • Rules manager-edit conditions and rules • Error values such as #DIV/0!, #N/A, and #VALUE! indicate that some element in a formula or a cell referenced in a formula is preventing Excel from returning a calculated value • The IFERROR function can determine if a cell contains an error value and display the message you choose rather than the default error value • You can calculate the number of cells in a range that match criteria you specify using the COUNTIF function, which is sometimes referred to as a conditional count -Syntax: =COUNTIF(range,criteria) • You can add the values in a range that meet criteria you specify using the SUMIF function, which is also called a conditional sum -Syntax: =SUMIF(range,criteria[,sum_range]) • You use the AVERAGEIF function to calculate the average of values in a range that meet criteria you specify -Syntax: =AVERAGEIF(range,criteria[,average_range]) • You use the AVERAGEIF function to calculate the average of values in a range that meet criteria you specify -Syntax: =AVERAGEIF(range,criteria[,average_range]) Session 10.1-10.2 • What if Analysis -What is it? -Different methods available -Goal Seek -One-Variable Data Table -Two-Variable Data Table -Scenarios Solver • Cost-volume-profit (CVP) analysis (sometimes called a break event analysis) expresses the relationship between a company’s expenses, its volume of business, and the resulting profit or net income -Types of expenses -Variable expenses change in proportion to the amount of business a company does (i.e. manufacturing materials) -A fixed expense is an expense that must be paid regardless of sales volume (i.e. rent) • The break-even point is the point where revenue equals expenses -Breakeven, Net income = 0 • We can input an income statement into Excel and then perform: -What if analysis: -Change the
More Less

Related notes for COMPSCI 1BA3

Log In


Don't have an account?

Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.