Here is the link to that excel https://www.dropbox.com/s/7uf3h0mzqug7xyj/MTH156_
ID: 3048161 • Letter: H
Question
Here is the link to that excel
https://www.dropbox.com/s/7uf3h0mzqug7xyj/MTH156_CT_Mod2_Option2_Brazil_Populations_Students%20%281%29.xlsx?dl=0
Screen Shot 2018-02-21 at 7.08.17 PM Q Search Option 2: World Populations Instructions Today there are 195 sovereign countries in the world that are officially recognized. One can choose to look at many types of data coming from these countries, as there is a plethora of existing information For this assignment you will be looking at populations of cities within Brazil at four different times (depending on when a census was taken). The data for these cities can be found in the file named Populations. Use all of the data points for each of the years given, but please note that not every city has a population for each census. Prepare a report (see below) using the numerical methods of descriptive statistics presented in this module to show how the populations of the cities vary over the years (growth rates). Be sure to include the following three (3) items in your report. 1. Compute descriptive statistics for each of the years along with an explanation of what the descriptive statistics tell us about the different years. Are they continually growing, or i there a decrease in the number of people? The descriptive statistics will include the mean, mode, range, standard deviation, and the 5-number summary (minimum, first quartile (Q1), median (Q2), third quartile (Q3), and maximum). 2. Determine which cities, if any, should be considered outliers in each of the years? If there are any outliers in any year, please list them and state for which year each one is an outlier. Use the z-score method to determine outliers for this question showing the z- score calculations for each city and year in your spreadsheet. 3. Determine the correlation coefficient between the first year and each of the other years Please provide an explanation of the relationships. Show your calculations for each correlation coefficient within the spreadsheet. Paper Requirements Write a report that uses the Written Assignment Requirements under the heading Expectations for CSU-Global Written Assignments found in the CSU-Global Guide to Writing and APA. Items that should be included, at a minimum, are a title page, an introduction, a body which answers the questions posed in the problem, and a conclusion paragraph that addresses your findings and what you have determined from the data and your analysis. As with all written assignments, you should have in-text citations and a reference page. Please include any tables of calculations, calculated values, and graphs associated with this problem in the body of your assignment response. Note: You must submit your Excel file with your report. This will aid in grading with partial credit if errors are found in the report.Explanation / Answer
A.1) Descriptive Statistics
Sno
Statistic
Population
Population
Population
Population
Formula used
1-Sep-1991
1-Aug-2000
1-Aug-2010
1-Jul-2014
1
Mean
281422
323385
372400
397182
=average(data array)
2
Mode
#N/A
#N/A
#N/A
#N/A
=mode(data array)
3
Range
9393648
9778635
11060321
11688615
=max(data array)-min(data array)
4
Standard Deviation
729120
752002
843672
887812
=stdev.p(data array)
5
minimum
19246
34552
92023
100344
=min(data array)
6
Q1
83385
100395
118534
125808
=quartile(data array,1)
7
Q2
130799
155401
189429
203651
=quartile(data array,2)
8
Q3
227807
282203
327801
352104
=quartile(data array,3)
9
Maximum
9412894
9813187
11152344
11788959
=max(data array)
A.2)we need to calculate Z-score using Standardize function in excel
=standardize(x value, mean, SD)
Using 99% confidence interval, we conclude that any Z-score not falling between (-2.58 to +2.58) shall be considered as outlier.
After calculation, we get the below Z-scores as outliers
Name
Administrative Regions
Population
Population
Population
Population
1-Sep-1991
Z-Value
1-Aug-2000
Z-Value
1-Aug-2010
Z-Value
1-Jul-2014
Z-Value
Brasília
DF
15,15,889
1.77
19,61,499
2.18
24,82,210
2.50
27,54,765
2.66
Rio de Janeiro
RJ
54,80,768
7.37
58,57,904
7.36
63,20,446
7.05
64,53,682
6.82
Salvador
BA
20,73,510
2.56
24,42,102
2.82
26,74,923
2.73
29,02,132
2.82
São Paulo
SP
94,12,894
12.93
98,13,187
12.62
1,11,52,344
12.78
1,17,88,959
12.83
The ones highlighted in red are outliers in the data set
A.3) using excel, we can calculate correlation coefficient
Based on the inputs we get the below charts
R=sqrt(0.9859)=0.9929
A.1) Descriptive Statistics
Sno
Statistic
Population
Population
Population
Population
Formula used
1-Sep-1991
1-Aug-2000
1-Aug-2010
1-Jul-2014
1
Mean
281422
323385
372400
397182
=average(data array)
2
Mode
#N/A
#N/A
#N/A
#N/A
=mode(data array)
3
Range
9393648
9778635
11060321
11688615
=max(data array)-min(data array)
4
Standard Deviation
729120
752002
843672
887812
=stdev.p(data array)
5
minimum
19246
34552
92023
100344
=min(data array)
6
Q1
83385
100395
118534
125808
=quartile(data array,1)
7
Q2
130799
155401
189429
203651
=quartile(data array,2)
8
Q3
227807
282203
327801
352104
=quartile(data array,3)
9
Maximum
9412894
9813187
11152344
11788959
=max(data array)
A.2)we need to calculate Z-score using Standardize function in excel
=standardize(x value, mean, SD)
Using 99% confidence interval, we conclude that any Z-score not falling between (-2.58 to +2.58) shall be considered as outlier.
After calculation, we get the below Z-scores as outliers
Name
Administrative Regions
Population
Population
Population
Population
1-Sep-1991
Z-Value
1-Aug-2000
Z-Value
1-Aug-2010
Z-Value
1-Jul-2014
Z-Value
Brasília
DF
15,15,889
1.77
19,61,499
2.18
24,82,210
2.50
27,54,765
2.66
Rio de Janeiro
RJ
54,80,768
7.37
58,57,904
7.36
63,20,446
7.05
64,53,682
6.82
Salvador
BA
20,73,510
2.56
24,42,102
2.82
26,74,923
2.73
29,02,132
2.82
São Paulo
SP
94,12,894
12.93
98,13,187
12.62
1,11,52,344
12.78
1,17,88,959
12.83
The ones highlighted in red are outliers in the data set
A.3) using excel, we can calculate correlation coefficient
Based on the inputs we get the below charts
R=sqrt(0.9859)=0.9929
R=sqrt(0.9814)=0.9906
R=sqrt(0.978)=0.9889
A.1) Descriptive Statistics
Sno
Statistic
Population
Population
Population
Population
Formula used
1-Sep-1991
1-Aug-2000
1-Aug-2010
1-Jul-2014
1
Mean
281422
323385
372400
397182
=average(data array)
2
Mode
#N/A
#N/A
#N/A
#N/A
=mode(data array)
3
Range
9393648
9778635
11060321
11688615
=max(data array)-min(data array)
4
Standard Deviation
729120
752002
843672
887812
=stdev.p(data array)
5
minimum
19246
34552
92023
100344
=min(data array)
6
Q1
83385
100395
118534
125808
=quartile(data array,1)
7
Q2
130799
155401
189429
203651
=quartile(data array,2)
8
Q3
227807
282203
327801
352104
=quartile(data array,3)
9
Maximum
9412894
9813187
11152344
11788959
=max(data array)
A.2)we need to calculate Z-score using Standardize function in excel
=standardize(x value, mean, SD)
Using 99% confidence interval, we conclude that any Z-score not falling between (-2.58 to +2.58) shall be considered as outlier.
After calculation, we get the below Z-scores as outliers
Name
Administrative Regions
Population
Population
Population
Population
1-Sep-1991
Z-Value
1-Aug-2000
Z-Value
1-Aug-2010
Z-Value
1-Jul-2014
Z-Value
Brasília
DF
15,15,889
1.77
19,61,499
2.18
24,82,210
2.50
27,54,765
2.66
Rio de Janeiro
RJ
54,80,768
7.37
58,57,904
7.36
63,20,446
7.05
64,53,682
6.82
Salvador
BA
20,73,510
2.56
24,42,102
2.82
26,74,923
2.73
29,02,132
2.82
São Paulo
SP
94,12,894
12.93
98,13,187
12.62
1,11,52,344
12.78
1,17,88,959
12.83
The ones highlighted in red are outliers in the data set
A.3) using excel, we can calculate correlation coefficient
Select data and go to insert to select scatter plot
In options, add trendline
In trendline options, select display equation on chart and display R-squared value on chart
Based on the inputs we get the below charts
R=sqrt(0.9859)=0.9929
R=sqrt(0.9814)=0.9906
R=sqrt(0.978)=0.9889
Sno
Statistic
Population
Population
Population
Population
Formula used
1-Sep-1991
1-Aug-2000
1-Aug-2010
1-Jul-2014
1
Mean
281422
323385
372400
397182
=average(data array)
2
Mode
#N/A
#N/A
#N/A
#N/A
=mode(data array)
3
Range
9393648
9778635
11060321
11688615
=max(data array)-min(data array)
4
Standard Deviation
729120
752002
843672
887812
=stdev.p(data array)
5
minimum
19246
34552
92023
100344
=min(data array)
6
Q1
83385
100395
118534
125808
=quartile(data array,1)
7
Q2
130799
155401
189429
203651
=quartile(data array,2)
8
Q3
227807
282203
327801
352104
=quartile(data array,3)
9
Maximum
9412894
9813187
11152344
11788959
=max(data array)