I have an EXCEL problem that I cannot figure out for homework. I couldn't format two of the tables below, but they are in order and should make sense. All data is below and the questions. Please help!

IP Address	Operating System	Browser	Site Connection	Add To Favorites	Entry	Exit	Bandwidth	Viewed Pages
999.010.210.133	Windows	MS Internet Explorer	HotBot	Yes	39731.48	39731.49	400	4
999.250.150.140	Linux	Mozilla	Yahoo	No	39724.47	39724.48	1	3
999.111.233.190	Windows	Netscape	Lycos	Yes	39724.58	39724.61	250	2
999.140.152.160	Windows	MS Internet Explorer	AltaVista	No	39726.43	39726.45	750	15
999.180.007.222	Macintosh	MS Internet Explorer	Dogpile	No	39726.61	39726.62	255	4
999.204.209.208	Linux	Unknown	WebCrawler	No	39727.05	39727.06	100	20
999.215.233.111	Macintosh	Safari	Askjeeves	No	39727.12	39727.13	20	2
999.177.166.144	Macintosh	Mozilla	About	No	39728.63	39728.65	300	25
999.133.205.212	Macintosh	MS Internet Explorer	Google	No	39730.23	39730.23	190	1
999.101.203.011	Windows	MS Internet Explorer	MSN	No	39732.16	39732.17	400	4
999.111.233.190	Windows	MS Internet Explorer	MSN	No	39732.52	39732.54	1	12
999.104.202.251	Macintosh	Mozilla	Askjeeves	No	39734.3	39734.32	250	14
999.250.150.140	Linux	Mozilla	Yahoo	No	39734.75	39734.76	750	3
999.111.233.190	Windows	Netscape	Lycos	Yes	39734.95	39734.95	255	2
999.140.152.160	Windows	MS Internet Explorer	Google	No	39736.66	39736.69	10	15
999.180.007.222	Macintosh	Safari	Dogpile	No	39736.82	39736.84	100	4
999.204.209.208	Windows	Unknown	WebCrawler	No	39738.37	39738.37	5	20
999.215.233.111	Macintosh	Safari	Askjeeves	No	39738.63	39738.65	100	2
999.177.166.144	Windows	Mozilla	About	No	39740.48	39740.48	20	25
999.133.205.212	Macintosh	MS Internet Explorer	Google	No	39740.82	39740.84	300	1
999.101.203.011	Windows	MS Internet Explorer	MSN	No	39742.6	39742.62	190	4
999.111.233.190	Windows	MS Internet Explorer	MSN	No	39743.07	39743.08	400	12
999.104.202.251	Macintosh	Mozilla	Askjeeves	No	39744.88	39744.91	1	9
999.010.210.133	Windows	MS Internet Explorer	HotBot	Yes	39746.55	39746.57	250	4
999.250.150.140	Windows	Mozilla	Yahoo	No	39748.39	39748.39	750	3
999.111.233.190	Windows	Netscape	Lycos	Yes	39749.91	39749.91	255	2
999.140.152.160	Windows	MS Internet Explorer	Google	No	39750.34	39750.34	100	15
999.180.007.222	Macintosh	MS Internet Explorer	Dogpile	No	39750.68	39750.71	20	4
999.204.209.208	Windows	Unknown	WebCrawler	No	39750.98	39751.01	300	20
999.215.233.111	Macintosh	MS Internet Explorer	Askjeeves	No	39752.97	39752.98	190	12
999.177.166.144	Macintosh	Mozilla	About	No	39753.04	39753.05	400	25
999.133.205.212	Linux	Mozilla	Google	No	39753.09	39753.11	1	1
999.101.203.011	Windows	MS Internet Explorer	MSN	No	39753.52	39753.53	250	4
999.111.233.190	Windows	MS Internet Explorer	MSN	No	39755.3	39755.32	750	12
999.104.202.251	Windows	Mozilla	Askjeeves	No	39756.88	39756.89	255	10
999.190.193.183	Windows	MS Internet Explorer	Yahoo	No	39757.17	39757.18	87	7
999.40.247.212	Windows	MS Internet Explorer	AltaVista	No	39758.69	39758.7	153	13
999.211.170.209	Windows	MS Internet Explorer	Yahoo	No	39758.96	39758.97	81	7
999.248.65.182	Windows	MS Internet Explorer	Google	No	39760.67	39760.68	210	18
999.109.187.236	Windows	MS Internet Explorer	Yahoo	Yes	39760.91	39760.91	72	6
999.190.240.80	Windows	MS Internet Explorer	Lycos	No	39761.36	39761.39	135	11
999.212.179.75	Macintosh	Safari	Askjeeves	No	39763.23	39763.25	258	22
999.135.192.236	Windows	MS Internet Explorer	Dogpile	No	39763.97	39763.99	222	19
999.182.250.249	Windows	MS Internet Explorer	Dogpile	No	39766.69	39766.69	213	18
999.18.176.118	Windows	MS Internet Explorer	Lycos	No	39767.14	39767.17	135	11
999.103.186.34	Macintosh	Mozilla	Direct Connect	Yes	39767.25	39767.26	30	3
999.211.251.83	Windows	MS Internet Explorer	Google	No	39767.56	39767.57	90	8
999.23.49.28	Windows	MS Internet Explorer	Lycos	No	39768.05	39768.07	147	12
999.166.80.64	Windows	MS Internet Explorer	Direct Connect	Yes	39768.1	39768.11	12	1
999.204.158.8	Windows	MS Internet Explorer	AltaVista	No	39769.78	39769.78	201	17
999.186.201.72	Windows	MS Internet Explorer	AltaVista	No	39771.3	39771.31	153	13
999.92.220.233	Windows	MS Internet Explorer	Google	No	39771.72	39771.74	126	11
999.30.183.65	Windows	MS Internet Explorer	Yahoo	No	39772.02	39772.02	87	7
999.76.107.216	Windows	Mozilla	Google	No	39772.39	39772.39	111	9
999.9.30.240	Windows	MS Internet Explorer	Yahoo	Yes	39772.58	39772.6	57	5
999.132.222.191	Windows	MS Internet Explorer	Webcrawler	No	39772.92	39772.93	99	8
999.241.152.36	Windows	MS Internet Explorer	Google	No	39773.36	39773.37	132	11
999.120.208.1	Macintosh	Mozilla	Direct Connect	Yes	39773.46	39773.46	27	2
999.51.217.244	Windows	MS Internet Explorer	Google	No	39774	39774.01	162	14
999.52.162.133	Windows	MS Internet Explorer	AltaVista	No	39776.58	39776.6	171	14
999.106.53.225	Windows	MS Internet Explorer	Google	No	39776.92	39776.94	102	9
999.75.136.40	Windows	MS Internet Explorer	AltaVista	No	39778.52	39778.53	177	15
999.147.12.138	Windows	MS Internet Explorer	Direct Connect	Yes	39778.53	39778.55	0	0
999.56.52.92	Windows	MS Internet Explorer	Direct Connect	Yes	39778.66	39778.68	39	3
999.84.139.160	Windows	MS Internet Explorer	Direct Connect	Yes	39778.8	39778.81	39	3
999.36.115.76	Linux	Opera	Askjeeves	No	39780.63	39780.65	249	21
999.197.254.186	Windows	MS Internet Explorer	Google	No	39780.98	39780.99	105	9
999.157.145.68	Windows	MS Internet Explorer	Dogpile	No	39783.66	39783.67	201	17
999.96.16.204	Windows	MS Internet Explorer	HotBot	No	39794.31	39794.33	192	16
999.113.4.13	Windows	MS Internet Explorer	Direct Connect	Yes	39794.44	39794.46	39	3
999.240.162.181	Windows	MS Internet Explorer	Yahoo	Yes	39794.65	39794.67	63	5
999.34.13.233	Windows	MS Internet Explorer	HotBot	No	39787.17	39787.19	156	13
999.225.179.104	Windows	MS Internet Explorer	Google	No	39788.76	39788.78	174	15
999.127.223.89	Windows	Mozilla	AOL	No	39790.56	39790.58	237	20
999.191.79.51	Windows	MS Internet Explorer	Lycos	No	39791.04	39791.05	144	12
999.160.75.95	Windows	MS Internet Explorer	Direct Connect	Yes	39791.15	39791.17	30	3
999.220.104.152	Windows	MS Internet Explorer	AltaVista	No	39792.66	39792.67	153	13
999.123.233.168	Macintosh	Mozilla	Google	No	39794.59	39794.61	279	23
999.108.144.220	Windows	MS Internet Explorer	AltaVista	No	39796.23	39796.25	189	16
999.110.39.94	Windows	MS Internet Explorer	Direct Connect	Yes	39796.29	39796.29	15	1
999.44.209.91	Windows	MS Internet Explorer	Direct Connect	Yes	39796.35	39796.36	18	2
999.121.85.33	Windows	Opera	Askjeeves	No	39798.19	39798.21	252	21
999.51.113.213	Windows	MS Internet Explorer	Direct Connect	Yes	39798.23	39798.24	9	1
999.153.3.203	Macintosh	Safari	Askjeeves	No	39800.06	39800.09	249	21
999.125.184.49	Windows	Mozilla	MSN	No	39802.05	39802.06	297	25
999.41.67.210	Windows	MS Internet Explorer	Google	No	39803.74	39803.77	207	17
999.46.16.186	Macintosh	Safari	Askjeeves	No	39803.61	39803.64	258	22
999.157.134.213	Windows	MS Internet Explorer	Direct Connect	Yes	39803.7	39803.7	24	2
999.43.148.102	Windows	MS Internet Explorer	AltaVista	No	39807.28	39807.28	171	14
999.99.61.60	Macintosh	Safari	Askjeeves	No	39809.08	39809.1	240	20
999.84.154.183	Windows	MS Internet Explorer	Direct Connect	Yes	39809.1	39809.12	3	0
999.138.81.213	Windows	MS Internet Explorer	Google	No	39810.77	39810.79	201	17
999.203.79.162	Windows	MS Internet Explorer	Direct Connect	Yes	39810.83	39810.85	18	2

Marks Collectibles, Inc. Spreadsheet Skills Average Function Cell Formatting Chart CountA Function CountIF Function Import External Data Insert Sheet Columns Max Function Median Function Min Function Mode Function PivotTable Sort Sum Function Worksheet Formatting Case Background

Marks Collectibles is a newly established online reseller of the very popular Tree Point Babies stuffed animals. Periodically, Mark Allan, the owner of the small, online business, downloads his Web sites traffic statistics from his Web hosting service. As the downloaded data are not in a useful format, Mr. Allan hires you to prepare a Site Statistics Analysis worksheet. The Site Statistics Analysis worksheet provides Mr. Allan with information about his Web sites visitors, including the number of visited pages, types of operating systems, browsers, visit durations, and used bandwidth. Preparing the worksheet requires you to properly format and design the Site Statistics Analysis worksheet, determine the visit duration for each visitor, the most popular operating systems, most popular browsers, most popular site connections, and amount of used bandwidth. Case Scenario A few years ago, small stuffed animals called Tree Point Babies became very popular with collectors, and demand for retired Tree Point Babies has continued to grow. Each month Tree Point Babies issues several new stuffed animals. Collectors, like Mark Allan, purchase many of these stuffed animals in the hopes that the stuffed animals can be resold at a later date for a profit. Marks Collectibles is a newly formed, small, online reseller of the popular Tree Point Babies. In an effort to widen his market, Mr. Allan has built a small Web site, showing high quality images of his available Tree Point Babies. Mr. Allan pays a nominal fee to the Web hosting service to host his Web site. As part of its service, the Web hosting service provides Mr. Allan with a text file containing traffic statistics for his Web site. Figure 1 shows a sample of this data. (the bandwidth is shown in kilobytes.) Currently, Mr. Allan purchase 10 GB of bandwidth each month from the Web hosting service. If he uses more than his allotted amount, he is charged for the extra bandwidth. Figure 1: Sample data from Web Site Statistics Text File IP Address, Operating System, Browser, Site Connection, Add To Favorites, Entry Exit, Bandwidth, View Page 12.010.210.133, Windows, Internet Explorer, HotBot, Yes, 39731.47538, 39731.49344, 400, 4, 12.120.145.230, Linux, Firefox, Yahoo, No 39724.47352, 39724.48189, 1, 3 230.234.010.001 Windows, Netscape, Lycos, Yes, 39724.58203, 39724.60697, 250, 2 120.140.152.160 Windows, Internet Explorer, AltaVista, No 39726.4299, 39726.44541, 750, 15 234.120.012.011, Windows, Google, Dogpile, No 39726.61228, 39726.62361, 255, 4 Design Specifications As previously mentioned, Mr. Allan periodically downloads a text file that contains the Web sites traffic data. As Figure 1 shows, the text file is not in a useful format, and the data must be formatted in order to provide useful information to Mr. Allan. Mr. Allan gives you the most recent text file containing his Web sites traffic data and asks you to import the data into a worksheet. To improve the appearance of the data, Mr. Allan asks you to properly format the data. For instance, you can apply a time format that will more easily display the dates and times of visitor entries and exits. He also asks you to include a header in the worksheet. He specifically requests that a worksheet title and date range indicating the time series for the data be placed at the top of the worksheet. Mr. Allan wants each row of data to be assigned a visitor number. The visitor number is intended to help him quickly determine the number of visits his Web site has received and is not intended to uniquely identify each visitor. Including a visitor number for each record required the addition of a new column. Mr. Allan wants the Visitor Number column to be the leftmost column in the worksheet. For each visit, Mr. Allan wants to know the visit duration. As you study the imported data, you notice that the entry and exit times for each visit are included. You decide to insert a Visit Duration column. You determine the visit duration vales for this column by taking the difference between the exit and entry times. Information Specifications For the Visit Duration, Bandwidth, and Viewed Pages columns, Mr. Allan wants the worksheet to display the median and mean for each of these columns. Mr. Allan wants the worksheet to display the mode for the Bandwidth and Viewed Pages columns. The median identifies the middle numbers from the visit duration, bandwidth, and viewed pages columns. Mr. Allan also wants the worksheet to show the maximum and minimum values for the Visit Duration, Bandwidth, and viewed Pages columns. Mr. Allan needs answers to the following questions. Using you newly designed Site Statistics Analysis worksheet, provide Mr. Allan with answers to his questions. Create a sheet in your workbook for each of the following questions. 1. What is the average daily bandwidth used?

2. How many pages were viewed in October? November? December? Assume Mr. Allan wants a chart that compares the number of viewed pages for each month. Prepare the chart.

3. What is the average stay for each visitor? What is the longest time a visitor stayed at the Web site? What is the shortest visit?

4. What percentage of visitors added the Web site to their list of favorites links? The chart should compare the visitor percentages by month.

5. Mr. Allan wants to know the average stay by operating system, connection, and browser. Mr. Allan wants this information displayed in pivot tables. Based on the results, what conclusions might you draw? Implementation Concerns Although you are free to work with the design of your worksheet, the worksheet should have a consistent, professional appearance. Also, you should use appropriate formatting for the cells and worksheet. Test Your Design After creating the Site Statistics Analysis worksheet, you should test your design. Perform the following steps.

1. Add the following data for January 2009 to your worksheet: IP Address, Operating System, Browser, Site Connection, Add To Favorites, Entry Exit, Bandwidth, View Page, 12.010.210.133, Windows, Internet Explorer, Google, Yes, 39814.48, 39814.5, 420, 4 12.120.145.230, Windows, Firefox, Yahoo, No 39815.59, 39815.63, 100, 3 230.234.010.001, Windows, Google, Lycos, Yes, 39816.92139, 39817.02, 250, 2 120.140.152.160, Windows, Internet Explorer, AltaVista, No 39814.05, 39814.12, 950, 15 234.120.012.011, Windows, Google, HotBot, No 39818.48, 39818.5, 450, 7

2. Which operating system is the most popular? Prepare a chart comparing the operating systems and their percentages. The chart should display the operating system percentages by month.

3. Which browser is most popular? Prepare a chart comparing the browsers and their percentages. Prepare a chart comparing the browsers and their overall counts by month.

4. How much bandwidth was used for each month? Prepare a chart that compares the bandwidth usages by month. Should Mr. All consider raising or lowering his bandwidth?

5. Mr. Allan wants to know the average bandwidth by operating system, connection, and browser. Mr. Allan wants this information displayed in pivot tables. Based on the results, what conclusions might you draw?