false

Study Guides
(248,455)

Canada
(121,562)

McMaster University
(7,418)

Computer Science
(41)

COMPSCI 1BA3
(31)

Anthony Hurst
(8)

Final

Unlock Document

Computer Science

COMPSCI 1BA3

Anthony Hurst

Fall

Description

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

Join OneClass

Access over 10 million pages of study

documents for 1.3 million courses.

Sign up

Join to view

Continue

Continue
OR

By registering, I agree to the
Terms
and
Privacy Policies

Already have an account?
Log in

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.