Question

a) Use the Histogram tool to develop a frequency distribution for the number of days and the browser used as a customer of this online store. Use your judgement for determining the number of bins to use.

b) Construct complete histogram graphs and numerical descriptions (that is, histograms and measures of location and dispersion) for the length of time the shopper spends on the website and the amount spent per transaction.

c) The company would like to investigate the effect that the day of the week have on sales. Summarize the total dollars spent and the mean amount spent per transaction for each day of the week. Briefly discuss your findings.

Customer	Day	Browser	Time (min)	Pages Viewed	Amount Spent ($)
8	Fri	Firefox	4.3	6	55.96
40	Wed	Chrome	5.0	5	36.48
19	Fri	Other	5.6	4	68.45
28	Sat	Firefox	6.0	4	27.91
37	Thu	Firefox	6.2	4	84.17
31	Thu	Chrome	7.1	2	41.20
49	Mon	Other	7.3	6	52.15
35	Tue	Chrome	7.8	5	71.81
24	Tue	Firefox	8.0	2	48.66
23	Sun	Chrome	8.0	3	32.73
47	Thu	Chrome	8.1	2	17.84
15	Fri	Chrome	8.4	3	84.12
3	Mon	Chrome	8.5	4	26.68
34	Fri	Chrome	9.1	3	52.09
25	Fri	Firefox	9.6	3	54.66
16	Thu	Chrome	9.6	4	59.20
48	Fri	Chrome	9.7	7	103.15
22	Tue	Chrome	10.2	6	74.43
6	Sat	Firefox	10.5	6	67.80
39	Tue	Chrome	10.6	2	39.06
43	Fri	Firefox	10.8	4	70.38
26	Wed	Chrome	11.0	2	40.54
5	Wed	Chrome	11.3	4	66.27
38	Sat	Chrome	11.3	4	55.58
4	Tue	Firefox	11.4	2	44.73
7	Sun	Chrome	11.4	2	36.04
13	Sun	Other	11.7	7	64.16
33	Sun	Chrome	11.8	4	37.17
30	Mon	Other	11.8	9	120.25
1	Mon	Chrome	12.0	4	54.52
9	Wed	Firefox	12.7	3	70.94
11	Sat	Other	13.3	6	54.04
44	Mon	Chrome	13.3	7	110.65
50	Fri	Chrome	13.4	3	98.75
46	Fri	Firefox	13.7	4	68.17
18	Mon	Chrome	14.0	7	126.40
12	Sun	Firefox	14.3	5	48.05
20	Wed	Chrome	15.1	5	32.69
41	Tue	Other	15.9	4	67.44
21	Sat	Firefox	16.3	5	78.58
36	Mon	Firefox	16.5	5	59.99
27	Sat	Chrome	16.9	5	34.69
32	Fri	Firefox	18.0	8	134.40
42	Sat	Chrome	18.1	7	60.14
2	Wed	Other	19.5	6	94.90
17	Thu	Chrome	23.3	7	91.62
14	Mon	Firefox	24.4	10	168.91
10	Tue	Chrome	24.7	7	68.73
45	Mon	Chrome	30.1	6	104.23
29	Fri	Firefox	32.9	10	155.30

Answer 1

a)

The histograms for Day is obtained in excel by using following steps,

Step 1: Write the Data Values for Day in excel. The screenshot is shown below,

Step 2: make a column "Bin" for days mon, tue, .... The screenshot is shown below,

Step 3: Since the data values are categorical, we will count the frequency using the excel function =COUNTIF(B2:B51,"Mon") . The screesnhot is shown below,

Step 4: select the column "Bin" and "Frequency" then

INSERT > Recommended Charts > All Charts > Clustered Column > OK. The screenshot is shown below,

The histogram is obtained. the screenshot is shown below,

Similarly, the histogram for Browser can be obtained by making bin Firefox, Chrome and Other then count for each bin in data values. The histogram is shown below,

b)

The histogram for time spent is obtained in excel using follwoing steps,

Step 1: Write the data values for Time(min) in excel.

Step 2: Make a column Bin with range 5, 10, 15, 20, ....40. The screenshot is shown below,

Step 3: DATA Data Analysis > Histogram > OK. The screenshot is shown below,

Step 4: Select input range: Time column, Bin range: bin column. The screenshot is shown below,

Step 5: Select column bin and frequncy then INSERT > recommended Charts > All Charts > Clustered Column. The screenshot of histogram is shown below,

Similarly, the histogram for Amount Spent can be obtained by making bin size of your choice, e. g. Bin: 0, 25, 50...175. The screenshot of histogram is shown below,

c)

The total dollars spent and the mean amount spent per transaction for each day of the week can be obtained by folowing theses steps,

Step 1: Sort the data by day.

DATA > Sort > Expand the Selection > OK. then input Sort by: Daya, Sort On: Values and Order > Custome List

The screenshot is shown below,

Step 2: Take sum for each day of week using the excel function =SUM(F2:F10). The screenshot is shown below,

Step 3: Take average for each day of week using the excel function =AVERAGE(F2:F10). The screenshot is shown below,

The Sum and average for each day of week is shown below,

	Sum	Average
Mon	823.78	91.53111
Tue	414.86	59.26571
Wed	341.82	56.97
Thu	294.03	58.806
Fri	945.43	85.94818
Sat	378.74	54.10571
Sun	218.15	43.63

Step 4: Select column Day, Sum and Average then

INSERT > Recommended Charts > All Charts > Line > Ok. The screenshot is shown below,