Microsoft Word - Some_Excel_Functions.doc
Economics 120A
Homework2
Dr. Maria Cândido
Some Excel Functions (some might be useful for Homework 2):
(1) Function RAND()
This function returns random values between 0 and 1.
You can use it to randomly select a sample out of a population of values.
See homework text for instructions on how to do it.
(2) Function COUNTIF
Select Insert and then Function and choose COUNTIF. Alternatively, write in a cell =COUNTIF(). This function counts
the number of cells within a range that meet a given criteria.
= COUNTIF(range, criteria)
These are the values you need to input:
ange is the range of cells from which you want to count cells.
criteria is the criterion in the form of a number, expression, or text that defines which cells will be counted. For
example, criteria can be expressed as 32, or “32” (count the cells whose value is equal to 32), ">32" (count the cells
whose values are higher than 32), "apples" (count the cells whose value is equal to apples), or “>” &A7 (count the cells
whose values are higher than the value in cell A7).
Example: Suppose you have values in column A, rows 1 to 100. If you want to count the number of cells whose value
is equal to 5, use =countif(A1:A100, 5) or = countif(A1:A100, “5”). Alternatively, you can write 5 in a cell (for
example cell C1) and then use =countif(A1:A100, C1).
If you want to count the number of cells whose values are higher than 5, use =countif(A1:A100, “>5”). Alternatively,
you can write 5 in a cell (for example cell C1) and then use =countif(A1:A100, “>”&C1). If you want to count the
number of cells whose values are higher than or equal to 5, you can write 5 in a cell (for example cell C1) and then use
=countif(A1:A100, “>=”&C1).
(3) Function COUNTIFS
Select Insert and then Function and choose COUNTIFS. Alternatively, write in a cell = COUNTIFS(). This function
applies criteria to cells across multiple ranges and counts the number of times all criteria are met.
= COUNTIF(criteria_range1, criteria1, [criteria_range2, criteria2],…)
criteria_range1 is the first range in which to evaluate the associated criteria.
criteria1 is the criteria in the form of a number, expression, cell reference, or text that define which cells will be
counted. For example, criteria can be expressed as 32, or “32” (count the cells whose value is equal to 32), ">32" (count
the cells whose values are higher than 32), "apples" (count the cells whose value is equal to apples), or “>” &A7 (count
the cells whose values are higher than the value in cell A7.
criteria_range2, criteria2, … (Optional) Additional ranges and their associated criteria. Up to 127 range/criteria pairs
are allowed.
IMPORTANT: Each additional range must have the same number of rows and columns as the criteria_range1
argument. The ranges do not have to be adjacent to each other.
Example:
If you want the count the number of values in cells A1 through A100 that are between 30 and 40 (not including 30 and
40), you would write =COUNTIFS(A1:A100,”>” 30,A1:A100,”<=”40)
If you want the count the number of values in cells A1 through A100 that are between the value specified in cell C1
(inclusive) and the value specified in cell D1, you would write
=COUNTIFS(A1:A100, ”>=” &C1 , A1:A100 , ”<”&D1)
Economics 120A
Homework2
Dr. Maria Cândido
(4) Function AVERAGEIF
Select Insert and then Function and choose AVERAGEIF. Alternatively, write in a cell =AVERAGEIF(). This function
eturns the average of all the cells in a range that meet a given criteria.
=AVERAGEIF(range, criteria, average_range)
ange is one or more cells to average, including numbers or names, a
ays, or references that contain numbers.
criteria is the criterion in the form of a number, expression, cell reference, or text that defines which cells are averaged.
For example, criteria can be expressed as 32, "32", ">32", "apples", or B4 “>=”&B4.
average_range is the actual set of cells to average. If omitted, range is used.
Example:
Suppose you have profits in cells C1 through C100. In cells A1 through A100, you have the year those profits were
made, and in cells B1 through B100, you have the quarter (1, 2 , 3, or 4) those profits were accrued. If you want to find
the average profit in the first quarter of the years, you would write
=AVERAGEIF(B1:B100, 1, C1:C100)
(5) Function NORM.S.INV (or NORMSINV in earlier versions of Excel)
Select Insert and then Function and choose NORM.S.INV. Alternatively, write in a cell = NORM.S.INV(). This
function returns the critical values of the STANDARD NORMAL cumulative distribution.
= NORM.S.INV(probability)
probability is a probability co
esponding to the normal distribution, a number between 0 and 1.
Example: Z follows a STANDARD normal distribution.
If you want to know the value of Z to the LEFT of which 5% of the distribution lies, then you would write:
= NORM.S.INV(0.05)
If you want to know the value of Z to the RIGHT of which 5% of the distribution lies, then you would write:
= NORM.S.INV(0.95)
(6) T.INV function
Select Insert and then Function and choose T.INV. Alternatively, write in a cell =T.INV(). This function returns critical
values of the Student's t-distribution as a function of the probability and the degrees of freedom.
=T.INV(probability,deg_freedom)
probability is the probability associated with the Student's t-distribution.
deg_freedom is the number of degrees of freedom with which to characterize the distribution.
Example: W is a random variable that follows the Student t-distribution with 30 degrees of freedom. If you want to
know the value of W, w, to the LEFT of which lies 0.90 of the distribution, you would write
=T.INV(0.90, 30). The function would return that value w, such that Pr(W < w) = 0.90.
Economics 120A
Homework2
Dr. Maria Cândido
(7) Function T.INV.2T
Select Insert and then Function and choose T.INV.2T Alternatively, write in a cell =T.INV.2T(). This function returns
critical values of the Student's t-distribution as a function of the probability and the degrees of freedom.
= T.INV.2T(probability, degrees_freedom)
probability is the probability associated with the two-tailed Student's t-distribution.
degrees_freedom is the number of degrees of freedom with which to characterize the distribution.
Example: W is a random variable that follows the Student t-distribution with 30 degrees of freedom.
=T.INV.2T(0.10, 30) returns that value w, such that P(|W| > w) = 0.10 where P(|W| > w) = P(W < -w or W > w).
WARNING: These last two functions were created for the newer versions of Excel. I have Excel 2007 for Mac and as
such, I don’t have these two last functions. I have a function called TINV, which does the same as T.INV.2T.
Make sure you check your version of Excel for an explanation of these types of functions.
(8) Function NORM.INV (or NORMINV in earlier versions of Excel)
Select Insert and then Function and choose NORM.INV. Alternatively, write in a cell = NORM.INV(). This function
eturns critical values of the normal cumulative distribution for the specified mean and standard deviation.
= NORM.INV(probability, mean, standard_dev)
probability is a probability co
esponding to the normal distribution, a number between 0 and 1.
Mean is the mean of the distribution.
Standard_dev is the standard deviation of the distribution.
Example: Suppose you know that X follows a normal distribution with mean 65 and standard deviation 17.
If you want to know the value of X to the LEFT of which 20% of the distribution is, then you would write: =
NORM.INV(0.20, 65, 17)
If you want to know the value of X to the RIGHT of which 20% of the distribution is, then you can write:
= NORM.INV(0.80, 65, 17)
(9) Function NORM.DIST (or NORMDIST in earlier versions)
Select Insert and then Function and choose NORM.DIST. Alternatively, write in a cell = NORM.DIST(). This function
eturns the normal distribution for the specified mean and standard deviation.
= NORM.DIST(x, mean, standard_dev, cumulative)
x is the value for which you want the distribution.
Mean is the mean of the distribution.
Standard_dev is the standard deviation of the distribution.
Cumulative is a logical value that determines the form of the function. If cumulative is TRUE, NORMDIST returns the
cumulative distribution function, i.e., it returns the probability mass to the LEFT of x. Notice that while the normal
distribution table in your textbook gives you the area to the right of each specific number; the Excel normdist(x, mean,
standard_dev, TRUE) will give you the area to the left of x (Prob(X < x)). If cumulative is FALSE, then it returns the
probability density function.
Example: Suppose you know that X follows a normal distribution with mean 65 and standard deviation 17.
If you want to know the probability that X is lower than 80, you would write: = NORM.DIST(80, 65, 17, true)
If you want the probability that X is higher than 80 you would write = 1- NORM.DIST(80, 65, 17, true)
If you want the probability that X is between than 60 and 80 you would write
= NORM.DIST(80, 75, 67, true) – NORM.DIST(60,65, 17, true)
Economics 120A
Homework2
Dr. Maria Cândido
(10) Function T.DIST (or T