there are 16 questions, they are in the word doc. The data is in the excelfile.We need to get the answers by using excel and we need to turn in the excel file as well.
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: range 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="" returns="" the="" average="" of="" all="" the="" cells="" in="" a="" range="" that="" meet="" a="" given="" criteria.="AVERAGEIF(range," criteria,="" average_range)="" range="" is="" one="" or="" more="" cells="" to="" average,="" including="" numbers="" or="" names,="" arrays,="" 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 corresponding 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 returns critical values of the normal cumulative distribution for the specified mean and standard deviation. = NORM.INV(probability, mean, standard_dev) probability is a probability corresponding 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 returns 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 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="">