45
Stdev.S
=STDEV.S([numbers you want the Standard Deviation of])
Similar to the AVERAGE formula, you just simply choose the cells with which you need a
standard deviation computed.
Max/MIN
=MAX([numbers you want to know the maximum of])
=MIN([numbers you want to know the minimum of])
MAX and MIN will help you find the range of numbers in a list. The MAX formula will scan
through a list of numbers and return the largest one whereas the MIN formula will scan through a
list of numbers and return the smallest one.
If
=IF([logical test],[what you want to show if test is true],[what you want to show if test is false])
The IF formula is another incredibly versatile and useful formula. Its possibilities are unmatched
by other formulas you will probably encounter. If there is ever a time when you need to create a
new variable based on the response(s) of another variable then IF is the formula for you. For
example, if you want to create a dichotomous variable from continuous data by conducting a
median split (let’s say that 6 is our median) into HIGH and LOW, then you would write:
=IF([cell]>6,“HIGH”,“LOW”)
Drag the formula for all of the cells that you want split and you’re done.
Protip
1
: if you want the product of your IF formula to be a word you need to wrap it in quotation
marks, but if you want it to be a number you do not need to wrap it in quotation marks.
=IF([cell]>6,“HIGH”,“LOW”) and =IF([cell]>6,1,0)
Protip
2
: if you want to conduct a test on whether or not a cell has a certain word in it (e.g., does a
cell say male), then you need to wrap the word that you are testing in quotation marks.
=IF([cell]=“male”,1,0)
Protip
3
: you can combine (nest) IF formulas to conduct more than one test (e.g.,if you want to
convert points earned in a class to a letter grade). To do this, you would place the additional IF
formula(s) where you would put what would be shown if the logical test if false.
=IF([cell]>89,“A”,IF([cell]>79,“B”, IF([cell]>69,“C”, IF([cell]>59,“D”,“F”))))
What this formula says is that any grade above 89 is an A; any grade above 79 (but less than 90)
is a B; any grade above 69 (but less than 80) is a C; any grade above 59 (but less than 70) is a D;
all grades lower than 60 are an F. Please note the [cell] in the formula is the exact same cell in
each nested IF. It tests the same value 4 times (once for each logical test) and produces the
correct TRUE answer or the final FALSE answer. Pay very close attention to the parentheses in
these functions as they are confusing and are extremely easy to mess up.