Reference no: EM132313095
Question 1: Create a spreadsheet that automatically calculates your grade in this class as you enter the grades that you receive.
It should include: 1) the weights of each graded assignment, 2) your grade in each assignment, and 3) your final grade. Be sure to label everything: such as the places where the information, is stored; the names of each assignment; and where to add the actual grades that you receive. Make this sheet user friendly; especially for users that see the sheet for the first time.
To use this for your benefit, you may want to design it so that it can be used to calculate your interim grade before you have all the grades. After this week, you will receive the solution from your professor and you will be able to use your solution or our solution for future classes.
Question 2:
Here is sample data showing the length of nails in boxes labled 1 1/4 inch. The data is in inches.
1.26 |
1.22 |
1.27 |
1.21 |
1.22 |
1.25 |
1.22 |
1.25 |
1.25 |
1.23 |
1.24 |
1.24 |
1.29 |
1.24 |
1.27 |
1.29 |
1.23 |
1.22 |
1.23 |
1.26 |
1.28 |
1.25 |
1.25 |
1.23 |
1.26 |
1.23 |
1.26 |
1.29 |
1.26 |
1.26 |
1.23 |
1.21 |
1.23 |
1.21 |
1.22 |
1.22 |
1.24 |
1.21 |
1.23 |
1.25 |
1.21 |
1.28 |
1.26 |
1.25 |
1.22 |
1.29 |
1.22 |
1.22 |
1.29 |
1.24 |
1.21 |
1.22 |
1.23 |
1.26 |
1.28 |
1.26 |
1.28 |
1.21 |
1.21 |
1.21 |
1.29 |
1.26 |
1.26 |
1.28 |
1.25 |
1.24 |
1.24 |
1.27 |
1.28 |
1.24 |
1.21 |
1.25 |
1.23 |
1.25 |
1.29 |
1.29 |
1.22 |
1.21 |
1.28 |
1.27 |
For the following questions, you must use Excel formulas in the cells so that Excel calculates the answers for you.
1) Compute the mean:
Compute the median
Find the mode
2) Compute the first quartile; use =QUARTILE.EXC()
First Quartile
Compute the third quartile; use =QUARTILE.EXC()
Third Quartile
3) Compute the interquartile range
4) Find the largest number
Find the smallest number
What is the range?
5) What is the Variance? Use =VAR.S()
What is the standard deviation? Use =STDEV.S()
6) What is the Coefficient of Variation, or the CV?
7) When is the Coefficient of Variation especially useful?
Copy all of the data into a column, use Column M and go from cell M1:M80
8) Use the Data Analysis tool on the numbers just copied to find the Descriptive Statistics:
Click on Data Analysis and Choose Descriptive Statistics
Click on the Summary Statistics box.
Highlight the mean, median, mode, Standard deviation, Range, Minimum, and Maximum
Notice that the Data Analysis tool gives you all of the info needed for this problem except for the quartiles, variance, and CV
9) Interpret the measures of central tendency within the context of this problem.
Should the company producing the nails be concerned about the central tendency?
10) Interpret the measures of variation within the context of this problem.
Should the company producing the nails be concerned about variation?
Question 3:
11) Using the data on the Pivot Table Data Sheet, create a Pivot table showing
The Type of Movie and the Domestic Gross of each Type; columns D and I on the Pivot Table Data Sheet
Have three columns: Type, Count of Type, and Sum of Total Domestic Gross
Format the Domestic Gross Field using $
12) Which type of movie had the highest Domestic Gross Total for 2018?
13) Which type of movie had the highest number of films made of that type in 2018?
(You might try making more/different pivot tables to learn about the raw data. What do you want to know about
Domestic Movies in 2018?)
Question 4:
14) Use the Data in the Pivot Table Data Sheet to create a Frequency Chart:
What is the Total DomesticGross of the lowest movie sales? Hint - use either =MIN()or just choose the movie at the bottom of the list
15) Subtract the lowest from the highest
The range of Total Domestic Gross for these movies is
16) We will use 10 bins so divide the range by 10:
Each bin will be :
Start with the minimum number:
Add the width of the bins
This number is the highest total for the first bin. Put this number is cell C21 for the first bin.
Now add the number that you just put into cell 21 to cell C27. Remember that C13 contains the width of each bin.
The next bin highest number starts with the first bin highest number and adds the size of the bins
Therefore, the second bin begins with
Continue adding to get the Bins array for the =FREQUENCY() function.
The last bin number in cell C36 will equal the highest Domestic Gross movie total
17) Here are the highest numbers for each bin:
Bins: Frequency:
18) Follow the instructions in the youtube videos to use the =FREQUENCY() array function.
You know that you have correctly used the =FREQUENCY() function if Excel automatically puts {} around the function.
Don't forget to push Control-Shift-Enter at the same time to enter the =FREQUENCY function.
Question 5:
19) Histogram
Create a Histogram of the Bins and Frequency data by first creating a column Chart
20) Format the historgram so there are no spaces between the bars. Histograms have not spaces and the graph does not become a Histogram until the spaces are removed.
Add a title to the Histogram
Add horizontal and Vertical Axes titles
21) Explain the difference between a histogram and a bar graph:
22) Make a pie chart of the frequency data with a title and Legend:
Attachment:- Questions.rar