QMB 3200 excel formulas

frequency formula

=countif(range,criteria)

percent frequency formula

=(frequency cell)/sum(range)

you need frequency and the total frequencies

relative frequency

=(frequency cell)/(total cell)

you need the frequency and the total

Histogram

a visual display of a frequency, relative frequency, or percent frequency distribution. Var of interest is on the horizontal axis and frequencies are on the vertical axis.

average/mean formula

=average(range)

Median formula

=median(range)

mode formula

=mode.sngl(range)

geometric mean formula

=geomean(range)

Percentile formula

=percentile.exc(range,percent)

quartile 1,2,3 formula

=quartile.exc(range,1) same for 2 and 3

variance formula

=var.s(range)

Standard deviation formula

=stdev.s(range)

outliers formula

Q1-1.5(IQR)

Q3+1.5(IQR)

Covariance formula

=covariance.s(range 1, range 2)

Correlation formula

=correl(range1, range 2)

expected value formula

the sum of the x's * their probabilities

=sumproduct(a:a,b:b)

Binomial probability formula

need number of trials and probability of successes

=binom.dist(x value, n trials, p probability,false)

cumulative binomial pd is true

poisson probability distribution formula

=poisson.dist(x, mu, false)

cum is true

mu= mean number of occurrences

Hypergeometric probability formula

=hypergeom.dist(sample, N ,r , population, False)

cum is true

exponential probability distribution formula

=expon.dist(x, 1/mean, true)

Only true

normal dist formula

=norm.dist(x, mean, standard dev, cum/true)

standard normal dist formula

=norm.dist(x, true)

=norm.s.inv(probability)