Core Excel Functions

Question 1

A random integer between 0 and 1

Answer 1

=RAND()

Question 2

Find the Median of an array

Answer 2

=MEDIAN(number1, number 2, …)

Question 3

Find the corresponding x-value on a normal distribution for the specified mean, standard deviation, and cumulative probability

Answer 3

=NORM.INV(probability, mean, standard_dev)

- Returns the corresponding x value on a normal distribution for the specified mean, dev, and cumulative probability.

Question 4

Find the cumulative probability of being less than or equal to a specified z value for normal distribution

Answer 4

=NORM.S.DIST(z, cumulative)

- When cumulative is set to “TRUE”, the function returns the probability of being less than or equal to the z value

Question 5

Count number of rows in an array

Answer 5

=COUNT(value 1, value 2, …)

Question 6

Making dummy variables

Answer 6

=IF(statement, true, false)

Question 7

Find Square Root

Answer 7

=SQRT(number)

Question 8

Find the Mode of an array (HBS style)

Answer 8

=MODE.SNGL(nubmer 1, number 2, …)

Question 9

Find Minimum of array

Answer 9

=MIN(number 1, number 2, …)

Question 10

Find the Sum

Answer 10

=SUM(number 1, number 2, …)

Question 11

Correlation Coefficient

Answer 11

=CORREL(array 1, array 2, …)

Question 12

Calculating the range of likely sample means

Answer 12

=CONFIDENCE.NORM or =CONFIDENCE.T

Question 13

Finding the Conditional Mean, or average of the cells in a specified range that meet a given criterion.

Answer 13

=AVERAGEIF(range, criteria, [aveage_range])

• Range: contains the one or more cells to which we wish to apply the criteria or condition.
• Criteria: the condition that is to be applied to the range.

Question 14

Find Maximum of an array

Answer 14

=MAX(number 1, number 2, …)

Question 15

The variance of a sample

Answer 15

=VAR.S(number 1, number 2, …)

Question 16

Find the margin of error using a normal distribution at a specific confidence level

Answer 16

=CONFIDENCE.NORM(alpha, standard_dev, size)

- alpha: is the significance level, which is equal to one minus the confidence level.

Question 17

Find the Mean of an array

Answer 17

=AVERAGE( number 1, number 2, …)

Question 18

Find percentile

Answer 18

=PERCENTILE.INC(array, k)
- Returns the k-th percentile of value in the specified array. For example, if we want to know the 95th percentile for an array of data, k would be 0.95.

Question 19

Cumulative normal distribution given a certain value of x

Answer 19

=NORM.DIST(x, mean, standard_dev, cumulative)
- When cumulative is set to TRUE, the function returns the cumulative probability, that is, the probability of being less than or equal to the specialized value x, for a normal distribution with the specified mean and standard deviation.

Question 20

The P-value associated with a given t-test

Answer 20

=T.TEST(array 1, array 2, tails, type)
- Array 1: set of numerical values or cell references
-Array 2: set of numerical values or cell references. If we only have one set of data, for the second data set we create a column for which every entry is the historical mean.
-Tails: the number of tails in the distribution. IT should be set to 1 to perform a one-sided test; to 2 to perform a two-side test.
Type: can be 1, 2, 3
1: paired test. used when the same group is tested twice to provide a before and after data for each member of a group
2: unpaired test in which the samples are assumed to have equal variances
3: unpaired test in which samples are assumed to have unequal variances. Unless we have a good reason to believe two samples have equal variances, we typically use 3 when conducting an unpaired test.

Question 21

Find the margin of error using a t-distribution

Answer 21

=CONFIDENCE.T(alpha, standard_dev, size)

-returns the margin of error using a t-distribution for a specified alpha, standard deviation, and size.

Question 22

Standard Deviation of a sample

Answer 22

=STEV.S(number1, number 2, …)