Appendix: Handy Excel and R commands

Name

Symbol

Excel Command

Excel Notes

Excel Example

R Command

R Notes

Mean

μ or x̅

=AVERAGE()

=AVERAGE(A1:A10)

mean()

Median

x̃

=MEDIAN()

=MEDIAN(A1:A10)

median()

Mode

M

=MODE()

Only returns one value!

=MODE(A1:A10)

NA

Standard Deviation

s

=STDEV.S()

Sample only

=STDEV.S(A1:A10)

sd()

Standard Deviation

σ

=STDEV.P()

Population, we rarely use this

=STDEV.P(A1:A10)

NA

Range

R

=MAX() – MIN()

=MAX(A1:A10) – MIN(A1:A10)

range()

returns the min and max, not the actual range

Maximum

=MAX()

=MAX(A1:A10)

max()

Minimum

=MIN()

=MIN(A1:A10)

min()

Count

N or n

=COUNT()

=COUNT(A1:A10)

length()

Sum Product

Σ(XY)

=SUMPRODUCT(-,- )

Multiplies pairs, then adds them

=SUMPRODUCT(A1:A10, B1:B10)

sum(X*Y)

Exponent

ab

=a^b

shift-6 gives the ^

=5^2

^

Combination

Cxn

=COMBIN(n, x)

Choose x out of n

=COMBIN(10,2)

choose(n,x)

Sum

Σx

=SUM()

Adds things

=SUM(A1:A10)

sum()

%which have value

=COUNTIF(range,condition)/ COUNT(range)

Finds the % of values greater than 100

=COUNTIF(C1:C5,”>100”)/ COUNT(C1:C5)

Binomial Distribution

Cnxpxqn-x

=BINOM.DIST(X, n, p,0)

Finds P(x)

P(x≤a)

=BINOM.DIST(a, n, p,1)

Cumulative probability, includes all values smaller!

P(a≤x≤b)

=BINOM.DIST.RANGE(n, p, a, b)

The probability it’s between 2 numbers

P(x≤?)=P

=BINOM.INV(n, p, P)

Inverse or reverse cumulative probability.

Poisson Distribution

λxe-μx!

=POISSON.DIST(x, λ, 0)

Exactly x

P(x≤a)

=POISSON.DIST(a, λ, 1)

a or smaller

P(x≥a)

=1 – POISSON.DIST(a-1, λ, 1)

a or bigger

P(a≤x≤b)

= POISSON.DIST(b, λ, 1) – POISSON.DIST(a-1, λ, 1)

Normal Distribution

P(x≤a)

=NORM.DIST(a, μ, σ, 1)

Always use cumulative!

P(x≥a)

=1 – NORM.DIST(a, μ, σ, 1)

P(a≤x≤b)

= NORM.DIST(b, μ, σ, 1) – NORM.DIST(a, μ, σ, , 1)

Subtract 2 things!

P(x≤?)=P

=NORM.INV(P, μ, σ)

Reverse lookup!

Exponential Distribution

Px≤a=1-e-λa

=EXPON.DIST(x, λ, 1)

Excel always counts x and below

Px≥a=e-λa

=1 – EXPON.DIST(x, λ, 1)

Use (1 –) to get x and above

P(a≤x≤b)

= EXPON.DIST(b, λ, 1) – EXPON.DIST(a, λ, 1)

Subtract 2 things

Name

Command

Notes & Examples

Uniform Discrete

=RANDBETWEEN(a,b)

Gives a number between 1 and 10 inclusive

=RANDBETWEEN(1,10)

Binomial

=BINOM.INV(n,p,RAND())

There are n = 20 people, the liklihood of one showing up is p = 0.30

# show up =BINOM.INV(20,0.3,RAND())

Bernoulli Trial

=IF(RAND()<p,ans1,ans_2)

Choose between two things, a 20% chance of being “happy”, an 80% chance of being “sad”

=IF(RAND()<.2,”happy”,”sad”)

General Discrete

=LOOKUP(RAND(),lower_limits, answers)

Pull a value from a table with given probabilities

Uniform

=RAND()

Give a random number between 0 and 1

Uniform Continuous

=a+(b-a)*RAND()

Give a number between 10 and 11

=11+(12 – 11)*RAND()

Normal

=NORM.INV(RAND(),μ ,σ)

Pull a random IQ score if the mean is 100 and standard deviation 15

=NORM.INV(RAND(),100 ,15)

Exponential

=-(1/λ )*LN(RAND())

5 customers an hour come into my coffee shop. Pick a random time between two customers

=-(1/5)*LN(RAND())

Name

Command

Notes & Examples

Linear Regression

=FORECAST.LINEAR([date],[known ys], [dates])

The date I’m forecasting could be Jan 1, 2020. Known y’s would be all the Sales values I have, and known x’s would be all of the dates I have.

Holt-Winters

=FORECAST.ETS([date],[known ys], [dates])

(Exponential Triple Smoothing) Same as above.

Conditional Average

=AVERAGEIF([column where the condition is],[condition],[column you want to average])

=AVERAGEIF(A:A, “>1”,B:B)

Averages the B’s only if their A value is greater than 1

=MONTH([date])

MONTH(2016/June/05) = 6

=COUNTIF([column you care about], [condition])

=COUNTIF(F:F, “>0”)

Counts the positive values in column F