{"id":303,"date":"2023-10-17T15:56:05","date_gmt":"2023-10-17T19:56:05","guid":{"rendered":"https:\/\/pressbooks.bccampus.ca\/businessanalytics\/back-matter\/__unknown__\/"},"modified":"2023-10-17T15:56:42","modified_gmt":"2023-10-17T19:56:42","slug":"__unknown__","status":"publish","type":"back-matter","link":"https:\/\/pressbooks.bccampus.ca\/businessanalytics\/back-matter\/__unknown__\/","title":{"raw":"Appendix: Handy Excel and R commands","rendered":"Appendix: Handy Excel and R commands"},"content":{"raw":"
\r\n

Handy Excel Commands<\/strong><\/p>\r\n

Summary Statistics<\/span> and Basic Commands<\/span><\/p>\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n
\r\n

Name<\/strong><\/p>\r\n<\/td>\r\n

\r\n

Symbol<\/strong><\/p>\r\n<\/td>\r\n

\r\n

Excel <\/strong>Command<\/strong><\/p>\r\n<\/td>\r\n

\r\n

Excel <\/strong>Notes<\/strong><\/p>\r\n<\/td>\r\n

\r\n

Excel Example<\/strong><\/p>\r\n<\/td>\r\n

\r\n

R Command<\/strong><\/p>\r\n<\/td>\r\n

\r\n

R Notes<\/strong><\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

Mean<\/p>\r\n<\/td>\r\n

\r\n

\u03bc or x\u0305<\/p>\r\n<\/td>\r\n

\r\n

=AVERAGE()<\/p>\r\n<\/td>\r\n

\r\n

<\/p>\r\n<\/td>\r\n

\r\n

=AVERAGE(A1:A10)<\/p>\r\n<\/td>\r\n

\r\n

mean()<\/p>\r\n<\/td>\r\n

\r\n

<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

Median<\/p>\r\n<\/td>\r\n

\r\n

x\u0303<\/p>\r\n<\/td>\r\n

\r\n

=MEDIAN()<\/p>\r\n<\/td>\r\n

\r\n

<\/p>\r\n<\/td>\r\n

\r\n

=MEDIAN(A1:A10)<\/p>\r\n<\/td>\r\n

\r\n

median()<\/p>\r\n<\/td>\r\n

\r\n

<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

Mode<\/p>\r\n<\/td>\r\n

\r\n

M<\/p>\r\n<\/td>\r\n

\r\n

=MODE()<\/p>\r\n<\/td>\r\n

\r\n

Only returns one value!<\/p>\r\n<\/td>\r\n

\r\n

=MODE(A1:A10)<\/p>\r\n<\/td>\r\n

\r\n

NA<\/p>\r\n<\/td>\r\n

\r\n

<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

Standard Deviation<\/p>\r\n<\/td>\r\n

\r\n

s<\/p>\r\n<\/td>\r\n

\r\n

=STDEV.S()<\/p>\r\n<\/td>\r\n

\r\n

Sample only<\/p>\r\n<\/td>\r\n

\r\n

=STDEV.S(A1:A10)<\/p>\r\n<\/td>\r\n

\r\n

sd()<\/p>\r\n<\/td>\r\n

\r\n

<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

Standard Deviation<\/p>\r\n<\/td>\r\n

\r\n

\u03c3<\/p>\r\n<\/td>\r\n

\r\n

=STDEV.P()<\/p>\r\n<\/td>\r\n

\r\n

Population, we rarely use this<\/p>\r\n<\/td>\r\n

\r\n

=STDEV.P(A1:A10)<\/p>\r\n<\/td>\r\n

\r\n

NA<\/p>\r\n<\/td>\r\n

\r\n

<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

Range<\/p>\r\n<\/td>\r\n

\r\n

R<\/p>\r\n<\/td>\r\n

\r\n

=MAX() \u2013 MIN()<\/p>\r\n<\/td>\r\n

\r\n

<\/p>\r\n<\/td>\r\n

\r\n

=MAX(A1:A10) - MIN(A1:A10)<\/p>\r\n<\/td>\r\n

\r\n

range()<\/p>\r\n<\/td>\r\n

\r\n

returns the min and max, not the actual range<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

Maximum<\/p>\r\n<\/td>\r\n

\r\n

<\/p>\r\n<\/td>\r\n

\r\n

=MAX()<\/p>\r\n<\/td>\r\n

\r\n

<\/p>\r\n<\/td>\r\n

\r\n

=MAX(A1:A10)<\/p>\r\n<\/td>\r\n

\r\n

max()<\/p>\r\n<\/td>\r\n

\r\n

<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

Minimum<\/p>\r\n<\/td>\r\n

\r\n

<\/p>\r\n<\/td>\r\n

\r\n

=MIN()<\/p>\r\n<\/td>\r\n

\r\n

<\/p>\r\n<\/td>\r\n

\r\n

=MIN(A1:A10)<\/p>\r\n<\/td>\r\n

\r\n

min()<\/p>\r\n<\/td>\r\n

\r\n

<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

Count<\/p>\r\n<\/td>\r\n

\r\n

N or n<\/p>\r\n<\/td>\r\n

\r\n

=COUNT()<\/p>\r\n<\/td>\r\n

\r\n

<\/p>\r\n<\/td>\r\n

\r\n

=COUNT(A1:A10)<\/p>\r\n<\/td>\r\n

\r\n

length()<\/p>\r\n<\/td>\r\n

\r\n

<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

Sum Product<\/p>\r\n<\/td>\r\n

\r\n

\u03a3(XY)<\/p>\r\n<\/td>\r\n

\r\n

=SUMPRODUCT(-,- )<\/p>\r\n<\/td>\r\n

\r\n

Multiplies pairs, then adds them<\/p>\r\n<\/td>\r\n

\r\n

=SUMPRODUCT(A1:A10, B1:B10)<\/p>\r\n<\/td>\r\n

\r\n

sum(X*Y)<\/p>\r\n

<\/p>\r\n<\/td>\r\n

\r\n

<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

Exponent<\/p>\r\n<\/td>\r\n

\r\n

ab<\/span><\/p>\r\n<\/td>\r\n

\r\n

=a^b<\/p>\r\n<\/td>\r\n

\r\n

shift-6 gives the ^<\/p>\r\n<\/td>\r\n

\r\n

=5^2<\/p>\r\n<\/td>\r\n

\r\n

^<\/p>\r\n<\/td>\r\n

\r\n

<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

Combination<\/p>\r\n<\/td>\r\n

\r\n

Cxn<\/span><\/p>\r\n<\/td>\r\n

\r\n

=COMBIN(n, x)<\/p>\r\n<\/td>\r\n

\r\n

Choose x out of n<\/p>\r\n<\/td>\r\n

\r\n

=COMBIN(10,2)<\/p>\r\n<\/td>\r\n

\r\n

choose(n,x)<\/p>\r\n<\/td>\r\n

\r\n

<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

Sum<\/p>\r\n<\/td>\r\n

\r\n

\u03a3x<\/span><\/p>\r\n<\/td>\r\n

\r\n

=SUM()<\/p>\r\n<\/td>\r\n

\r\n

Adds things<\/p>\r\n<\/td>\r\n

\r\n

=SUM(A1:A10)<\/p>\r\n<\/td>\r\n

\r\n

sum()<\/p>\r\n<\/td>\r\n

\r\n

<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

%which have value<\/p>\r\n<\/td>\r\n

\r\n

<\/p>\r\n<\/td>\r\n

\r\n

=COUNTIF(range,condition)\/ COUNT(range)<\/p>\r\n<\/td>\r\n

\r\n

Finds the % of values greater than 100<\/p>\r\n<\/td>\r\n

\r\n

=COUNTIF(C1:C5,\u201d>100\u201d)\/ COUNT(C1:C5)<\/p>\r\n<\/td>\r\n

\r\n

<\/p>\r\n<\/td>\r\n

\r\n

<\/p>\r\n<\/td>\r\n<\/tr>\r\n

<\/td>\r\n<\/td>\r\n<\/td>\r\n<\/td>\r\n<\/td>\r\n<\/td>\r\n<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n

Distributions<\/strong>: <\/strong>NORM.DIST(1.96, 1, 0, 1) = 0.975 means that NORM.INV(0.975, 1, 0) = 1.96<\/p>\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n
\r\n

Binomial Distribution<\/p>\r\n<\/td>\r\n

\r\n

Cnxpxqn-x<\/span><\/p>\r\n<\/td>\r\n

\r\n

=BINOM.DIST(X, n, p,0)<\/p>\r\n<\/td>\r\n

\r\n

Finds P(x)<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

<\/p>\r\n<\/td>\r\n

\r\n

P(x\u2264a)<\/span><\/p>\r\n<\/td>\r\n

\r\n

=BINOM.DIST(a, n, p,1)<\/p>\r\n<\/td>\r\n

\r\n

Cumulative probability, includes all values smaller!<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

<\/p>\r\n<\/td>\r\n

\r\n

P(a\u2264x\u2264b)<\/span><\/p>\r\n<\/td>\r\n

\r\n

=BINOM.DIST.RANGE(n, p, a, b)<\/p>\r\n<\/td>\r\n

\r\n

The probability it\u2019s between 2 numbers<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

<\/p>\r\n<\/td>\r\n

\r\n

P(x\u2264?)=P<\/span><\/p>\r\n<\/td>\r\n

\r\n

=BINOM.INV(n, p, P)<\/p>\r\n<\/td>\r\n

\r\n

Inverse or reverse cumulative probability.<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

Poisson Distribution<\/p>\r\n<\/td>\r\n

\r\n

\u03bbxe-\u03bcx!<\/span><\/p>\r\n<\/td>\r\n

\r\n

=POISSON.DIST(x, \u03bb, 0)<\/p>\r\n<\/td>\r\n

\r\n

Exactly x<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

<\/p>\r\n<\/td>\r\n

\r\n

P(x\u2264a)<\/span><\/p>\r\n<\/td>\r\n

\r\n

=POISSON.DIST(a, \u03bb, 1)<\/p>\r\n<\/td>\r\n

\r\n

a or smaller<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

<\/p>\r\n<\/td>\r\n

\r\n

P(x\u2265a)<\/span><\/p>\r\n<\/td>\r\n

\r\n

=1 - POISSON.DIST(a-1, \u03bb, 1)<\/p>\r\n<\/td>\r\n

\r\n

a or bigger<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

<\/p>\r\n<\/td>\r\n

\r\n

P(a\u2264x\u2264b)<\/span><\/p>\r\n<\/td>\r\n

\r\n

= POISSON.DIST(b, \u03bb, 1) - POISSON.DIST(a-1, \u03bb, 1)<\/p>\r\n<\/td>\r\n

\r\n

<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

Normal Distribution<\/p>\r\n<\/td>\r\n

\r\n

P(x\u2264a)<\/span><\/p>\r\n<\/td>\r\n

\r\n

=NORM.DIST(a, \u03bc, \u03c3, 1)<\/p>\r\n<\/td>\r\n

\r\n

Always use cumulative!<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

<\/p>\r\n<\/td>\r\n

\r\n

P(x\u2265a)<\/span><\/p>\r\n<\/td>\r\n

\r\n

=1 - NORM.DIST(a, \u03bc, \u03c3, 1)<\/p>\r\n<\/td>\r\n

\r\n

<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

<\/p>\r\n<\/td>\r\n

\r\n

P(a\u2264x\u2264b)<\/span><\/p>\r\n<\/td>\r\n

\r\n

= NORM.DIST(b, \u03bc, \u03c3, 1) - NORM.DIST(a, \u03bc, \u03c3, , 1)<\/p>\r\n<\/td>\r\n

\r\n

Subtract 2 things!<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

<\/p>\r\n<\/td>\r\n

\r\n

P(x\u2264?)=P<\/span><\/p>\r\n<\/td>\r\n

\r\n

=NORM.INV(P, \u03bc, \u03c3)<\/p>\r\n<\/td>\r\n

\r\n

Reverse lookup!<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

Exponential Distribution<\/p>\r\n<\/td>\r\n

\r\n

Px\u2264a=1-e-\u03bba<\/span><\/p>\r\n<\/td>\r\n

\r\n

=EXPON.DIST(x, \u03bb, 1)<\/p>\r\n<\/td>\r\n

\r\n

Excel always counts x and below<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

<\/p>\r\n<\/td>\r\n

\r\n

Px\u2265a=e-\u03bba<\/span><\/p>\r\n<\/td>\r\n

\r\n

=1 - EXPON.DIST(x, \u03bb, 1)<\/p>\r\n<\/td>\r\n

\r\n

Use (1 \u2013) to get x and above<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

<\/p>\r\n<\/td>\r\n

\r\n

P(a\u2264x\u2264b)<\/span><\/p>\r\n<\/td>\r\n

\r\n

= EXPON.DIST(b, \u03bb, 1) - EXPON.DIST(a, \u03bb, 1)<\/p>\r\n<\/td>\r\n

\r\n

Subtract 2 things<\/p>\r\n<\/td>\r\n<\/tr>\r\n

<\/td>\r\n<\/td>\r\n<\/td>\r\n<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n

Randomizing<\/span><\/p>\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n
\r\n

Name<\/strong><\/p>\r\n<\/td>\r\n

\r\n

Command<\/strong><\/p>\r\n<\/td>\r\n

\r\n

Notes<\/strong> & Examples<\/strong><\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

Uniform Discrete<\/p>\r\n<\/td>\r\n

\r\n

=RANDBETWEEN(a,b)<\/p>\r\n<\/td>\r\n

\r\n

Gives a number between 1 and 10 inclusive<\/p>\r\n

=RANDBETWEEN(1,10)<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

Binomial<\/p>\r\n<\/td>\r\n

\r\n

=BINOM.INV(n,p,RAND())<\/p>\r\n<\/td>\r\n

\r\n

There are n = 20 people, the liklihood of one showing up is p = 0.30<\/p>\r\n

# show up =BINOM.INV(20,0.3,RAND())<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

Bernoulli Trial<\/p>\r\n<\/td>\r\n

\r\n

=IF(RAND()<p,ans1,ans_2)<\/p>\r\n<\/td>\r\n

\r\n

Choose between two things, a 20% chance of being \u201chappy\u201d, an 80% chance of being \u201csad\u201d<\/p>\r\n

=IF(RAND()<.2,\u201dhappy\u201d,\u201dsad\u201d)<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

General Discrete<\/p>\r\n<\/td>\r\n

\r\n

=LOOKUP(RAND(),lower_limits, answers)<\/p>\r\n<\/td>\r\n

\r\n

Pull a value from a table with given probabilities<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

Uniform<\/p>\r\n<\/td>\r\n

\r\n

=RAND()<\/p>\r\n<\/td>\r\n

\r\n

Give a random number between 0 and 1<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

Uniform Continuous<\/p>\r\n<\/td>\r\n

\r\n

=a+(b-a)*RAND()<\/p>\r\n<\/td>\r\n

\r\n

Give a number between 10 and 11<\/p>\r\n

=11+(12 - 11)*RAND()<\/p>\r\n

<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

Normal<\/p>\r\n<\/td>\r\n

\r\n

=NORM.INV(RAND(),\u03bc ,\u03c3)<\/p>\r\n<\/td>\r\n

\r\n

Pull a random IQ score if the mean is 100 and standard deviation 15<\/p>\r\n

=NORM.INV(RAND(),100 ,15)<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

Exponential<\/p>\r\n<\/td>\r\n

\r\n

=-(1\/\u03bb )*LN(RAND())<\/p>\r\n<\/td>\r\n

\r\n

5 customers an hour come into my coffee shop. Pick a random time between two customers<\/p>\r\n

=-(1\/5)*LN(RAND())<\/p>\r\n<\/td>\r\n<\/tr>\r\n

<\/td>\r\n<\/td>\r\n<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n

\u00a0<\/span><\/p>\r\n

Forecasting<\/span><\/p>\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n
\r\n

Name<\/strong><\/p>\r\n<\/td>\r\n

\r\n

Command<\/strong><\/p>\r\n<\/td>\r\n

\r\n

Notes<\/strong> & Examples<\/strong><\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

Linear Regression<\/p>\r\n<\/td>\r\n

\r\n

=FORECAST.LINEAR([date],[known ys], [dates])<\/p>\r\n<\/td>\r\n

\r\n

The date I\u2019m forecasting could be Jan 1, 2020. Known y\u2019s would be all the Sales values I have, and known x\u2019s would be all of the dates I have.<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

Holt-Winters<\/p>\r\n<\/td>\r\n

\r\n

=FORECAST.ETS([date],[known ys], [dates])<\/p>\r\n<\/td>\r\n

\r\n

(Exponential Triple Smoothing) Same as above.<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

Conditional Average<\/p>\r\n<\/td>\r\n

\r\n

=AVERAGEIF([column where the condition is],[condition],[column you want to average])<\/p>\r\n<\/td>\r\n

\r\n

=AVERAGEIF(A:A, \u201c>1\u201d,B:B)<\/p>\r\n

Averages the B\u2019s only if their A value is greater than 1<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

<\/p>\r\n<\/td>\r\n

\r\n

=MONTH([date])<\/p>\r\n<\/td>\r\n

\r\n

MONTH(2016\/June\/05) = 6<\/p>\r\n<\/td>\r\n<\/tr>\r\n

\r\n

<\/p>\r\n<\/td>\r\n

\r\n

=COUNTIF([column you care about], [condition])<\/p>\r\n<\/td>\r\n

\r\n

=COUNTIF(F:F, \u201c>0\u201d)<\/p>\r\n

Counts the positive values in column F<\/p>\r\n<\/td>\r\n<\/tr>\r\n

<\/td>\r\n<\/td>\r\n<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n

<\/p>\r\n\r\n<\/div>","rendered":"

\n

Handy Excel Commands<\/strong><\/p>\n

Summary Statistics<\/span> and Basic Commands<\/span><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n

Name<\/strong><\/p>\n<\/td>\n

\n

Symbol<\/strong><\/p>\n<\/td>\n

\n

Excel <\/strong>Command<\/strong><\/p>\n<\/td>\n

\n

Excel <\/strong>Notes<\/strong><\/p>\n<\/td>\n

\n

Excel Example<\/strong><\/p>\n<\/td>\n

\n

R Command<\/strong><\/p>\n<\/td>\n

\n

R Notes<\/strong><\/p>\n<\/td>\n<\/tr>\n

\n

Mean<\/p>\n<\/td>\n

\n

\u03bc or x\u0305<\/p>\n<\/td>\n

\n

=AVERAGE()<\/p>\n<\/td>\n

\n

\n<\/td>\n

\n

=AVERAGE(A1:A10)<\/p>\n<\/td>\n

\n

mean()<\/p>\n<\/td>\n

\n

\n<\/td>\n<\/tr>\n

\n

Median<\/p>\n<\/td>\n

\n

x\u0303<\/p>\n<\/td>\n

\n

=MEDIAN()<\/p>\n<\/td>\n

\n

\n<\/td>\n

\n

=MEDIAN(A1:A10)<\/p>\n<\/td>\n

\n

median()<\/p>\n<\/td>\n

\n

\n<\/td>\n<\/tr>\n

\n

Mode<\/p>\n<\/td>\n

\n

M<\/p>\n<\/td>\n

\n

=MODE()<\/p>\n<\/td>\n

\n

Only returns one value!<\/p>\n<\/td>\n

\n

=MODE(A1:A10)<\/p>\n<\/td>\n

\n

NA<\/p>\n<\/td>\n

\n

\n<\/td>\n<\/tr>\n

\n

Standard Deviation<\/p>\n<\/td>\n

\n

s<\/p>\n<\/td>\n

\n

=STDEV.S()<\/p>\n<\/td>\n

\n

Sample only<\/p>\n<\/td>\n

\n

=STDEV.S(A1:A10)<\/p>\n<\/td>\n

\n

sd()<\/p>\n<\/td>\n

\n

\n<\/td>\n<\/tr>\n

\n

Standard Deviation<\/p>\n<\/td>\n

\n

\u03c3<\/p>\n<\/td>\n

\n

=STDEV.P()<\/p>\n<\/td>\n

\n

Population, we rarely use this<\/p>\n<\/td>\n

\n

=STDEV.P(A1:A10)<\/p>\n<\/td>\n

\n

NA<\/p>\n<\/td>\n

\n

\n<\/td>\n<\/tr>\n

\n

Range<\/p>\n<\/td>\n

\n

R<\/p>\n<\/td>\n

\n

=MAX() \u2013 MIN()<\/p>\n<\/td>\n

\n

\n<\/td>\n

\n

=MAX(A1:A10) – MIN(A1:A10)<\/p>\n<\/td>\n

\n

range()<\/p>\n<\/td>\n

\n

returns the min and max, not the actual range<\/p>\n<\/td>\n<\/tr>\n

\n

Maximum<\/p>\n<\/td>\n

\n

\n<\/td>\n

\n

=MAX()<\/p>\n<\/td>\n

\n

\n<\/td>\n

\n

=MAX(A1:A10)<\/p>\n<\/td>\n

\n

max()<\/p>\n<\/td>\n

\n

\n<\/td>\n<\/tr>\n

\n

Minimum<\/p>\n<\/td>\n

\n

\n<\/td>\n

\n

=MIN()<\/p>\n<\/td>\n

\n

\n<\/td>\n

\n

=MIN(A1:A10)<\/p>\n<\/td>\n

\n

min()<\/p>\n<\/td>\n

\n

\n<\/td>\n<\/tr>\n

\n

Count<\/p>\n<\/td>\n

\n

N or n<\/p>\n<\/td>\n

\n

=COUNT()<\/p>\n<\/td>\n

\n

\n<\/td>\n

\n

=COUNT(A1:A10)<\/p>\n<\/td>\n

\n

length()<\/p>\n<\/td>\n

\n

\n<\/td>\n<\/tr>\n

\n

Sum Product<\/p>\n<\/td>\n

\n

\u03a3(XY)<\/p>\n<\/td>\n

\n

=SUMPRODUCT(-,- )<\/p>\n<\/td>\n

\n

Multiplies pairs, then adds them<\/p>\n<\/td>\n

\n

=SUMPRODUCT(A1:A10, B1:B10)<\/p>\n<\/td>\n

\n

sum(X*Y)<\/p>\n

\n<\/td>\n

\n

\n<\/td>\n<\/tr>\n

\n

Exponent<\/p>\n<\/td>\n

\n

ab<\/span><\/p>\n<\/td>\n

\n

=a^b<\/p>\n<\/td>\n

\n

shift-6 gives the ^<\/p>\n<\/td>\n

\n

=5^2<\/p>\n<\/td>\n

\n

^<\/p>\n<\/td>\n

\n

\n<\/td>\n<\/tr>\n

\n

Combination<\/p>\n<\/td>\n

\n

Cxn<\/span><\/p>\n<\/td>\n

\n

=COMBIN(n, x)<\/p>\n<\/td>\n

\n

Choose x out of n<\/p>\n<\/td>\n

\n

=COMBIN(10,2)<\/p>\n<\/td>\n

\n

choose(n,x)<\/p>\n<\/td>\n

\n

\n<\/td>\n<\/tr>\n

\n

Sum<\/p>\n<\/td>\n

\n

\u03a3x<\/span><\/p>\n<\/td>\n

\n

=SUM()<\/p>\n<\/td>\n

\n

Adds things<\/p>\n<\/td>\n

\n

=SUM(A1:A10)<\/p>\n<\/td>\n

\n

sum()<\/p>\n<\/td>\n

\n

\n<\/td>\n<\/tr>\n

\n

%which have value<\/p>\n<\/td>\n

\n

\n<\/td>\n

\n

=COUNTIF(range,condition)\/ COUNT(range)<\/p>\n<\/td>\n

\n

Finds the % of values greater than 100<\/p>\n<\/td>\n

\n

=COUNTIF(C1:C5,\u201d>100\u201d)\/ COUNT(C1:C5)<\/p>\n<\/td>\n

\n

\n<\/td>\n

\n

\n<\/td>\n<\/tr>\n

<\/td>\n<\/td>\n<\/td>\n<\/td>\n<\/td>\n<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Distributions<\/strong>: <\/strong>NORM.DIST(1.96, 1, 0, 1) = 0.975 means that NORM.INV(0.975, 1, 0) = 1.96<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n

Binomial Distribution<\/p>\n<\/td>\n

\n

Cnxpxqn-x<\/span><\/p>\n<\/td>\n

\n

=BINOM.DIST(X, n, p,0)<\/p>\n<\/td>\n

\n

Finds P(x)<\/p>\n<\/td>\n<\/tr>\n

\n

\n<\/td>\n

\n

P(x\u2264a)<\/span><\/p>\n<\/td>\n

\n

=BINOM.DIST(a, n, p,1)<\/p>\n<\/td>\n

\n

Cumulative probability, includes all values smaller!<\/p>\n<\/td>\n<\/tr>\n

\n

\n<\/td>\n

\n

P(a\u2264x\u2264b)<\/span><\/p>\n<\/td>\n

\n

=BINOM.DIST.RANGE(n, p, a, b)<\/p>\n<\/td>\n

\n

The probability it\u2019s between 2 numbers<\/p>\n<\/td>\n<\/tr>\n

\n

\n<\/td>\n

\n

P(x\u2264?)=P<\/span><\/p>\n<\/td>\n

\n

=BINOM.INV(n, p, P)<\/p>\n<\/td>\n

\n

Inverse or reverse cumulative probability.<\/p>\n<\/td>\n<\/tr>\n

\n

Poisson Distribution<\/p>\n<\/td>\n

\n

\u03bbxe-\u03bcx!<\/span><\/p>\n<\/td>\n

\n

=POISSON.DIST(x, \u03bb, 0)<\/p>\n<\/td>\n

\n

Exactly x<\/p>\n<\/td>\n<\/tr>\n

\n

\n<\/td>\n

\n

P(x\u2264a)<\/span><\/p>\n<\/td>\n

\n

=POISSON.DIST(a, \u03bb, 1)<\/p>\n<\/td>\n

\n

a or smaller<\/p>\n<\/td>\n<\/tr>\n

\n

\n<\/td>\n

\n

P(x\u2265a)<\/span><\/p>\n<\/td>\n

\n

=1 – POISSON.DIST(a-1, \u03bb, 1)<\/p>\n<\/td>\n

\n

a or bigger<\/p>\n<\/td>\n<\/tr>\n

\n

\n<\/td>\n

\n

P(a\u2264x\u2264b)<\/span><\/p>\n<\/td>\n

\n

= POISSON.DIST(b, \u03bb, 1) – POISSON.DIST(a-1, \u03bb, 1)<\/p>\n<\/td>\n

\n

\n<\/td>\n<\/tr>\n

\n

Normal Distribution<\/p>\n<\/td>\n

\n

P(x\u2264a)<\/span><\/p>\n<\/td>\n

\n

=NORM.DIST(a, \u03bc, \u03c3, 1)<\/p>\n<\/td>\n

\n

Always use cumulative!<\/p>\n<\/td>\n<\/tr>\n

\n

\n<\/td>\n

\n

P(x\u2265a)<\/span><\/p>\n<\/td>\n

\n

=1 – NORM.DIST(a, \u03bc, \u03c3, 1)<\/p>\n<\/td>\n

\n

\n<\/td>\n<\/tr>\n

\n

\n<\/td>\n

\n

P(a\u2264x\u2264b)<\/span><\/p>\n<\/td>\n

\n

= NORM.DIST(b, \u03bc, \u03c3, 1) – NORM.DIST(a, \u03bc, \u03c3, , 1)<\/p>\n<\/td>\n

\n

Subtract 2 things!<\/p>\n<\/td>\n<\/tr>\n

\n

\n<\/td>\n

\n

P(x\u2264?)=P<\/span><\/p>\n<\/td>\n

\n

=NORM.INV(P, \u03bc, \u03c3)<\/p>\n<\/td>\n

\n

Reverse lookup!<\/p>\n<\/td>\n<\/tr>\n

\n

Exponential Distribution<\/p>\n<\/td>\n

\n

Px\u2264a=1-e-\u03bba<\/span><\/p>\n<\/td>\n

\n

=EXPON.DIST(x, \u03bb, 1)<\/p>\n<\/td>\n

\n

Excel always counts x and below<\/p>\n<\/td>\n<\/tr>\n

\n

\n<\/td>\n

\n

Px\u2265a=e-\u03bba<\/span><\/p>\n<\/td>\n

\n

=1 – EXPON.DIST(x, \u03bb, 1)<\/p>\n<\/td>\n

\n

Use (1 \u2013) to get x and above<\/p>\n<\/td>\n<\/tr>\n

\n

\n<\/td>\n

\n

P(a\u2264x\u2264b)<\/span><\/p>\n<\/td>\n

\n

= EXPON.DIST(b, \u03bb, 1) – EXPON.DIST(a, \u03bb, 1)<\/p>\n<\/td>\n

\n

Subtract 2 things<\/p>\n<\/td>\n<\/tr>\n

<\/td>\n<\/td>\n<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Randomizing<\/span><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
\n

Name<\/strong><\/p>\n<\/td>\n

\n

Command<\/strong><\/p>\n<\/td>\n

\n

Notes<\/strong> & Examples<\/strong><\/p>\n<\/td>\n<\/tr>\n

\n

Uniform Discrete<\/p>\n<\/td>\n

\n

=RANDBETWEEN(a,b)<\/p>\n<\/td>\n

\n

Gives a number between 1 and 10 inclusive<\/p>\n

=RANDBETWEEN(1,10)<\/p>\n<\/td>\n<\/tr>\n

\n

Binomial<\/p>\n<\/td>\n

\n

=BINOM.INV(n,p,RAND())<\/p>\n<\/td>\n

\n

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

# show up =BINOM.INV(20,0.3,RAND())<\/p>\n<\/td>\n<\/tr>\n

\n

Bernoulli Trial<\/p>\n<\/td>\n

\n

=IF(RAND()<p,ans1,ans_2)<\/p>\n<\/td>\n

\n

Choose between two things, a 20% chance of being \u201chappy\u201d, an 80% chance of being \u201csad\u201d<\/p>\n

=IF(RAND()<.2,\u201dhappy\u201d,\u201dsad\u201d)<\/p>\n<\/td>\n<\/tr>\n

\n

General Discrete<\/p>\n<\/td>\n

\n

=LOOKUP(RAND(),lower_limits, answers)<\/p>\n<\/td>\n

\n

Pull a value from a table with given probabilities<\/p>\n<\/td>\n<\/tr>\n

\n

Uniform<\/p>\n<\/td>\n

\n

=RAND()<\/p>\n<\/td>\n

\n

Give a random number between 0 and 1<\/p>\n<\/td>\n<\/tr>\n

\n

Uniform Continuous<\/p>\n<\/td>\n

\n

=a+(b-a)*RAND()<\/p>\n<\/td>\n

\n

Give a number between 10 and 11<\/p>\n

=11+(12 – 11)*RAND()<\/p>\n

\n<\/td>\n<\/tr>\n

\n

Normal<\/p>\n<\/td>\n

\n

=NORM.INV(RAND(),\u03bc ,\u03c3)<\/p>\n<\/td>\n

\n

Pull a random IQ score if the mean is 100 and standard deviation 15<\/p>\n

=NORM.INV(RAND(),100 ,15)<\/p>\n<\/td>\n<\/tr>\n

\n

Exponential<\/p>\n<\/td>\n

\n

=-(1\/\u03bb )*LN(RAND())<\/p>\n<\/td>\n

\n

5 customers an hour come into my coffee shop. Pick a random time between two customers<\/p>\n

=-(1\/5)*LN(RAND())<\/p>\n<\/td>\n<\/tr>\n

<\/td>\n<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\u00a0<\/span><\/p>\n

Forecasting<\/span><\/p>\n\n\n\n\n\n\n\n\n\n
\n

Name<\/strong><\/p>\n<\/td>\n

\n

Command<\/strong><\/p>\n<\/td>\n

\n

Notes<\/strong> & Examples<\/strong><\/p>\n<\/td>\n<\/tr>\n

\n

Linear Regression<\/p>\n<\/td>\n

\n

=FORECAST.LINEAR([date],[known ys], [dates])<\/p>\n<\/td>\n

\n

The date I\u2019m forecasting could be Jan 1, 2020. Known y\u2019s would be all the Sales values I have, and known x\u2019s would be all of the dates I have.<\/p>\n<\/td>\n<\/tr>\n

\n

Holt-Winters<\/p>\n<\/td>\n

\n

=FORECAST.ETS([date],[known ys], [dates])<\/p>\n<\/td>\n

\n

(Exponential Triple Smoothing) Same as above.<\/p>\n<\/td>\n<\/tr>\n

\n

Conditional Average<\/p>\n<\/td>\n

\n

=AVERAGEIF([column where the condition is],[condition],[column you want to average])<\/p>\n<\/td>\n

\n

=AVERAGEIF(A:A, \u201c>1\u201d,B:B)<\/p>\n

Averages the B\u2019s only if their A value is greater than 1<\/p>\n<\/td>\n<\/tr>\n

\n

\n<\/td>\n

\n

=MONTH([date])<\/p>\n<\/td>\n

\n

MONTH(2016\/June\/05) = 6<\/p>\n<\/td>\n<\/tr>\n

\n

\n<\/td>\n

\n

=COUNTIF([column you care about], [condition])<\/p>\n<\/td>\n

\n

=COUNTIF(F:F, \u201c>0\u201d)<\/p>\n

Counts the positive values in column F<\/p>\n<\/td>\n<\/tr>\n

<\/td>\n<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\n<\/div>\n","protected":false},"author":883,"menu_order":1,"template":"","meta":{"pb_show_title":"on","pb_short_title":"","pb_subtitle":"","pb_authors":[],"pb_section_license":""},"back-matter-type":[],"contributor":[],"license":[],"class_list":["post-303","back-matter","type-back-matter","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/pressbooks.bccampus.ca\/businessanalytics\/wp-json\/pressbooks\/v2\/back-matter\/303","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/pressbooks.bccampus.ca\/businessanalytics\/wp-json\/pressbooks\/v2\/back-matter"}],"about":[{"href":"https:\/\/pressbooks.bccampus.ca\/businessanalytics\/wp-json\/wp\/v2\/types\/back-matter"}],"author":[{"embeddable":true,"href":"https:\/\/pressbooks.bccampus.ca\/businessanalytics\/wp-json\/wp\/v2\/users\/883"}],"version-history":[{"count":2,"href":"https:\/\/pressbooks.bccampus.ca\/businessanalytics\/wp-json\/pressbooks\/v2\/back-matter\/303\/revisions"}],"predecessor-version":[{"id":305,"href":"https:\/\/pressbooks.bccampus.ca\/businessanalytics\/wp-json\/pressbooks\/v2\/back-matter\/303\/revisions\/305"}],"metadata":[{"href":"https:\/\/pressbooks.bccampus.ca\/businessanalytics\/wp-json\/pressbooks\/v2\/back-matter\/303\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/pressbooks.bccampus.ca\/businessanalytics\/wp-json\/wp\/v2\/media?parent=303"}],"wp:term":[{"taxonomy":"back-matter-type","embeddable":true,"href":"https:\/\/pressbooks.bccampus.ca\/businessanalytics\/wp-json\/pressbooks\/v2\/back-matter-type?post=303"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/pressbooks.bccampus.ca\/businessanalytics\/wp-json\/wp\/v2\/contributor?post=303"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/pressbooks.bccampus.ca\/businessanalytics\/wp-json\/wp\/v2\/license?post=303"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}