Question

NOTE: Please provide instructions on how to solve it in excel add screenshots

Thank you

Suppose a random sample of 137 households in Chicago was taken as part of a study on annual household spending for food at home. The sample data is below.

1. For the sample data, compute the mean and the median and construct a box and whisker plot. (Use Excel to Descriptive statistics and copy paste the screenshot here).
2. Are the data skewed or symmetric? Approximately what percent of the data values are between $2,900 and $3,250?

3491.87,3127.1,3137.61,2822.66,3135.94,3518.41,3383.77,3446.47,3350.62,3756.99,2729.15,3586.06,3561.95,3103.44,3187.71,2762.31,3412.39,3199.49,3309.21,3149.74,3532.46,3113.21,2754.67,2997.63,3344.45,3119.26,3411.02,2789.5,3106.31,3320.2,3408.16,3040.53,3393.49,3156.12,3223.96,3176.76,2884.14,3514.76,3162.04,3109.76,3581.36,2811.56,2856.72,2999.66,3133.38,3340.37,3033.87,3783.9,3256.83,3164.88,3411.8,3630.46,3233.13,3090.72,2928.11,2878.76,3158.13,3319.31,3275.16,2990.89,3387.1,3372.8,3674.43,3048.22,3152.79,2865.26,3319.9,3122.28,3199.46,3535.27,3406.3,3713.68,3753.18,2926.63,3526.59,3370.49,3673.77,3373.5,3176.1,3433.93,2974.83,3941.5,3478.97,3293.82,2864.58,3272.04,3056.15,3345.62,3655.3,3341.42,3122.78,3788,3310.74,2778.11,3318.81,3292.08,3128.46,3895.15,3447.51,3529.82,3053.02,3034.95,3170.24,3675.76,3344.87,3438.72,3137.63,3370.29,3570.8,3266.52,2964.6,3497.53,3485.13,3003.17,2797.96,2974.49,3646.57,3559.41,3437.4,3317.12,3436.46,2965.68,3154.04,3604.53,3315.64,3527.88,2994.41,3645.64,3247.93,2803.25,3139.06,3131.98,3266.98,3476.7,3337.24,3260.99,3260.74

Answer 1

Note: Excel formulas are in italic. Final answers are highlighted in colour.

part a)

Step 1: Copy the data into excel Column A.

Mean = AVERAGE(A1:A137) = 3268.562

Median= MEDIAN(A1:A137)= 3272.04

We can get the mean and median values along with other summary statistics using descriptive statistics feature of excel.

Mean and Median using Summary statistics:

1. On the Data tab, in the Analysis group, click Data Analysis.

2. Provide input range and check the Descriptive statistics box.

3. It will generate descriptive statistics as shown below.

Column1
Mean	3268.56219
Standard Error	22.54632175
Median	3272.04
Mode	#N/A
Standard Deviation	263.8979301
Sample Variance	69642.11753
Kurtosis	-0.422934842
Skewness	0.078031841
Range	1212.35
Minimum	2729.15
Maximum	3941.5
Sum	447793.02
Count	137

Box and whisker Plot:

Select the data and click on Insert >> Box & Whisker as shown below

Click on Ok, it generates a box and whisker plot. If you need to give any title to the graph or axis or display the data in the chart, click on + icon on top right corner.

The final chart with Min, Q1, Q2, Q3, Max values is as shown below

part b)

Drawing a histogram

Step 1: Decide on the bin size and class width based on the range of values. As the range is [2729.15,3941.5], Considering ideal 8 bins, I am taking a class width of 175.

Therefore the bins are

Bins
2550
2725
2900
3075
3250
3425
3600
3775
3950

Here, 2550 indicates the values <=2550. 2725 indicates the values greater than 2550, less than or equal to 2725.

Step 2: Select Data >> Data analysis >> Histogram.

Step 3: Fill the input and bin ranges as shown below.

This generates the below histogram.

The histogram shows the values are slightly skewed towards right(Positively skewed distribution). This means the scores fall toward the lower side of the scale and relatively few higher scores.

what percent of the data values are between $2,900 and $3,250?

The number of households between $2,900 and $3,250 are(from the histogram table) 49 (17+32) . This is approximately 35.8% of the total values.