Can someone help me with this? Like which rows and columns to put everything in?
ID: 3173963 • Letter: C
Question
Can someone help me with this? Like which rows and columns to put everything in? I have zero experience with Excel and need clear instruction to complete this.
In this exercise you will compare the efficiency of the mean of a sample to three other estimators of position of a continuous distribution in one coordinate All three of the alternative estimators have the advantage of being more robust than the mean. N.B. Pay careful attention to the questions asked below as well as to the construction of the spreadsheet. Enter answers to the questions in some prominent place in the workbook. a) Using Excel, in one row generate ten Gaussian distributed random deviates with mean 0 and b) In the same row compute the mean of the ten deviates c) In tho samc row computc thc mcdian of thc tcn dcwiatcs. d) In the same row compute the "truncated mean" obtained by discarding the smallest and largest deviates and computing the mean of the remaining cight e) In the same row compute the "double-truncated mean" (the Instructor just invented this term) by discarding the two smallest and the two largest deviates and computing the mean of the remaining six. (Hint: The Instructor applied the RANK function to each of the deviates ten more cells to determine which of them should be discarded. f) Replicate the row created in steps a)-e) about 3000 times. g) For each of the four estimators compute the mean of the sample of 3000 (should be close to Zero h) For each of the four estimators compute the root mean square (RMS) about zero. (SUMPRODUCT anyone?) i) For each of the four estimators use Excel's STDEV.S function to compute the standard deviation. How do these values compare with the results of h)?Explanation / Answer
a)
1. Open Microsoft Excel.
2. In first 10 cells in first row write
'=norminv(rand(),0,1)'
The above formula is used to create a normally distributed set of number in Excel.
The general syntax is '=norminv(rand(),mean,Standard Deviation)'
rand() = Generate a random number
3) Thus, First row of distributed random variables is created.
b)
1. In the next cell write the below formula: =average(Range of the 10 cells)
2. Thus the average is calculated.
c)
1. In the next cell write the below formula: =Median(Range of the 10 cells)
2.Thus the median is calculated.
d)To find truncated mean we will first find the Max and Min of the 10 numbers and subtract it from the sum of 10 numbers and then divide it by 8. Please find the steps mentioned below:
1. Find the Maximum number of the 10 numbers =MAX(Range of 10 cells)
2. Find the Minimum of the 10 numbers =MIN(Range of 10 cells)
3. Find the Sum of the 10 numbers =SUM(Range of 10 cells)
4. Now subtract the minimum and maxim from the sum and divide it by 8.
=(SUM(Range of 10 cells) - MIN(Range of 10 cells) - MAX(Range of 10 cells) ) / 8
5. Please find the below image.