Microsoft Excel Flashcards
save
control+s
select all
control+a
go to last cell in rows
control + arrow
select cells one by one
shift + arrows
find blanks
f4
find and replace
f5
excel formulas must begin with
=
add cells
=SUM
cells in the range E4 TO E11
E4:E11
multiply
*
divide
/
power
to make an absolute reference
$
minimum function
MIN
maximum function
MAX
average function
AVERAGE
count function
COUNT
count non blank cells function
COUNTA
count blank cells function
COUNTBLANK
count cells with criteria
COUNTIF(s)
Examples of logical functions
IF, AND, OR
Vertical look up function
VLOOKUP
Horizontal look up function
HLOOKUP
Why would you include true/false in a look up function
false gives you exact, true is approx
to return a value in the cell at a certai intersection use
INDEX
to return a relative position of an item use the function
MATCH
what does match_type=1 give
largest value that is less than or equal to lookup_value
what does match_type=0 give
finds the first value which is exactly equal to look up value
what does match_type=-1 give
the smallest value that is greater than or equal to the look up value
formula for quartiles
=QUARTILE.EXE
=QUARTILE.INC
formula for variance of a populations
=VAR.P
formula for variance of populations
=VAR.P
formula for standard deviation of population
=STDEV.P
formula for standard deviation of sample
=STDEV.S
how to get boxplot
select all data
insert
statistical
box + whisker
skew formula
=SKEW
correlation coefficient formula
=CORREL(arg1, arg2)
binomial distribution function
=BINOM.DIST
poisson distribution formula
=POISSON.DIST
z value formula
=STANDARDIZE(x, mean, stdev)
find prob p(
=NORM.DIST(x, mean, stdev, true/false)
What does true/false mean in norm.dist formula
true: accumulative
false; singular probabiiity
to find z value given probability
=NORM.S.IV
How to get descriptive analysis
data analysis
descriptive stats
summary statstics box
