false

Study Guides
(248,639)

Canada
(121,648)

McMaster University
(7,419)

Computer Science
(41)

COMPSCI 1BA3
(31)

Anthony Hurst
(8)

School

McMaster University
Department

Computer Science

Course Code

COMPSCI 1BA3

Professor

Anthony Hurst

Description

th
COMP SCI 1BA3: Exam review: 28 -sep-13
- look at visual overview in the text book – pg 4
Spreadsheet software: computer program used to enter, analyze and present quantitative data.
A collection of text and numbers lay out in a rectangular grid.
it organises and allows to perform a ‘what-if analysis’ change values and assess the effects
A workbook can have 2 different types of sheets: worksheets & chart sheets
Worksheet: grid of rows and columns
- each cell is identified with a cell reference- where rows and columns intersect
Chat sheet: excel chart that provides a visual representation of worksheet data
Creating effective workbooks:
- plan out your work book
what problems do you want to solve? What data is needed? What calculations are required?
Include a documentation sheet to describe your workbook
BUTTON: f1 brings up the help function to search for shortcuts
Autocomplete: makes assumptions therefore must always verify!
3 data types:
- text: letters, numbers and symbols = form words & sentences
-Number: any numerical value for calculation
- Date and Time
Truncated = when text entry gets hidden when cell is too small... in numbers it comes out like
####
To enter lines within same cell = alt+enter
Editings: click F2, double click, or in formula bar
Enter when done, ESC to exit without changes
Column widths expressed in pixels (a single point) standard is 64 pixels = 8.43 characters
A row is usually 15 points = 20pixels = 1/72 of inch
Change column width and row height (also applies to multiple selections)
- drag
if certain number of characters wanted… click entire column – cells – format – column width –
insert character # OR.. you can go to cells- format – auto fit
Inserting rows or columns
- click inset button when the full row/column is selected and inserts to the right
-right click
Deleting rows or columns
- right click – clear contents – delete row/column – rows below shift up
Cell and cell ranges
cell range – group of cells which have references
if there is a colon :: between then it is everything taken in [adjacent]
if a comma,,,, then it is nonadjacent
Moving and copying cell ranges (drag and drop)
- select the range and put cursor over border – changes and move it
- to copy click ctrl
- to copy/paste = clipboard tab on the home
Inserting and deleting a range
select range - home tab – inset button
Formulas
Formula – is an expression that returns a value
begins with =
Operators – combine different values
- arithmetic operators
-numbers stored within cells
-order of precedence applies
^you can do a combination of cell references or cell reference with a number
To enter formula
- type the cell reference in onto the cell for the formula
-instead of typing the cell reference – click on the cells that need to be used
Copying and pasting formulas:
click on the cell with the formula copy it into other cells = makes a pattern Absolute vs relative references
relative: when creating a formula the references to cells or ranges are based on the position
relative to the cell that contains them.. therefore the patterns emerge
absolute: if you don’t want excel to adjust to a pattern or reference if you want A5 constant and
to multiply it with C1, C2, C3.. you can’t just copy and paste.. you need to create an absolute
reference… therefore … =$A$5*C1
Paste special: to paste to particular parts of a cell selection
home tab – paste – paste special : select what needs to be paste, and which operation
View all formulas by pressing “ctrl+”
…Functions
To add a group =SUM(A1:A3)
Home – editing – auto sum sum, average, count, min, max
Excel Tables: structured range of data that is managed independently from the rest of the data
Each column represents a ‘field’ (characteristic of a person, place or thing)
Each row represents a ‘record’(unique entity or occurrence)
First row = header row= contains column headings
Excel table vs structured range
- Format table quickly
- Range is expanded
- Can add a total row
- Sort, and filter
- Create formulas using table names than cell addresses
Sorting: puts rows of the table in particular order
home ribbon – data – sort button
Filtering: hides rows that don’t meet criteria
home ribbon – data - filter Totals & subtotals
totals – available through the ‘total row’ box
tables tools – design – total row (last row appears on the table with value)
Subtotals: cannot be used in a excel table can only be applied to ranges
tools – table tools – convert to range—subtotal button – at each change
Outline levels: allow you to view the data in various levels of detail. – each group summarises
Structured references – specific cell or range address with a structured reference/table
name/column header.
THIS STRUCTURED REFERENCE IS EASIER TO UNDERSTAND: THAN THIS CELL REFERENCE:
=SUM(DeptSales[SaleAmt]) =Sum(C2:C7)
qualified vs unqualified references:
- unqualified: don’t include the table name: use in a calculated column or formula within an
excel table *doesn’t exist in the table+
- when referring to excel table: use a qualified reference [exists in the table]
PIVOT TABLES: when large tables are difficult to interpret
- allows us to summarize data into categorie

More
Less
Unlock Document

Related notes for COMPSCI 1BA3

Only pages 1 and half of page 2 are available for preview. Some parts have been intentionally blurred.

Unlock DocumentJoin 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.