Study Guides (400,000)
CA (150,000)
UW (7,000)
CS (400)
CS100 (40)
Study Guide

# CS100 Study Guide - Winter 2019, Comprehensive Midterm Notes - Https, Your Computer (British Magazine), Windowing System

Department
Computer Science
Course Code
CS100
Professor
Alexandra Boutros
Study Guide
Midterm

This preview shows pages 1-3. to view the full 110 pages of the document.
CS100

Only pages 1-3 are available for preview. Some parts have been intentionally blurred.

Excel
Values can be text values (lables) or number values or date/time and boolean
=sum(number1, number2 …)
=average(#1, #2…)
Can also add the different functions or…
=sum(average(#1,#2…),#1,#2…)
Can reference cells instead by using the cell number ex. =2d+4a
PMT calculates the payment =pmt(rate, number of payments, principal value)
Copy an paste, can copy the formulas
Autofill for excel, bottom right corner and you can drag it
- It can copy down the pattern or be the same as the formula if you are adding
ABSOLUTE REFERENCES
(\$A\$1) keep the column and row the same (keep same cell)
(\$A1) keep the column the same but not the row
(A\$1) keep the row the same but not the column
NAMED RANGES
Can name cells to make it easier
- Ex. Name something HST so use =16777*HST
- Even if you move the cell, formulas will still be using the HST name even tho it is in a
different cell, it will move with it
o makes formula easier to interpret (understand and follow)
o people looking at sheet will know what’s going on
o don’t have to worry about absolute references since it is built in
- can also assign name to a range
o why?
o Can say =sum(range name)
o Can also use other functions
- If you add another line out of the range, go to formulas and use name manager
o It will show you all the areas that have a name
o You can edit the range with this and you can highlight the new range to include
the full thing
find more resources at oneclass.com
find more resources at oneclass.com

Only pages 1-3 are available for preview. Some parts have been intentionally blurred.

Strings and lables
+ tax will give you #NAME?
How do you fix that? Use a single ‘
o Ex. ‘+ tax
You can also use quotations
o = “=Total” = Total
o Can use &
o &D5& glue 2 things together = “your total is “&D5&” pleas pay”
o =TEXT is a function takes a number and places it as text in some format
Can use =upper(…) to make it upper case
=left(text, number of characters)
=middle(text, starting character number, number of characters)
^ use to take out parts of a text
Find can be used to find the text within a text and the character number of
it
Working with multiple worksheets
=sheet1!cell+Sheet2!cell
o This will add 2 values from 2 different cells
Boolean Logic
You can either have true or false
AND, OR, xor
o XOR
You get either one or the other
o Inclusive or, or(x,y)
Having x, y, x and y are true
=cell>cell will give you true or false
o Can have < = etc.
Can use IF
o IF(content of cell is true, value if true, value if false)
o What if content of cell is not Boolean value, ex yes
use cell=”yes” to check if content is true
this converts it to a Boolean value
o can also use < > = etc
Can combine text together
cell&” “&cell&” “&”cell
can use if(isblank) function for Boolean logic
ex if all cells were filled you get Kris Kris Kris
o if one is blank you get Kris Kris
^ use cell&” “&IF(ISBLANK(cell),,cell&” “&”cell
find more resources at oneclass.com
find more resources at oneclass.com

Unlock to view full version

Only pages 1-3 are available for preview. Some parts have been intentionally blurred.

IFERROR(formula, “what you want to give it if there is an errer”)
Conditional formatting
Can customize cells and show you certain things about the data
o Example, failing = change cells to red
COUNTIF
=countif(range, criteria)
If you want the criteria to be >1000 you need to put in quotations or it will interpret as a
formula
AVERAGEIF
=averageif(range, criteria, [average range])
COUNTIFS
Use to specify multiple criteria
o =countifs(criteria range1, criteria1, criteria range2, criteria2)
VLOOKUP
=VLOOKUP(lookup value, table, column index number, range lookup)
Last thing is a boolean value, will be true for approximate match or false for exact match
CHARTS (usually place the chart in a whole new sheet)
Column chart good for categories
Line chart good for data that changes over time
Pie chart good for parts of a whole, ex. Expenses: rent tuition food…
Scatterplot good for correlations between 2 variables
Pivot tables + pivot charts (insert pivot tables)
o Good for large data sets
o Allow you to pick and choose certain parts and look at the important parts of the
data sets
o Change rows columns data and apply filters
o Data chart allows the same thing but displays the table in a chart format
Adding this gives you another pivot table and adds in a pivot chart
FILTERS
You can apply filters to large sets of data so you can filter out stuff
SUMPRODUCT
=sumproduct(array1,array2,arry3…) adds the range and multiplies them
find more resources at oneclass.com
find more resources at oneclass.com