The following data represent the retail prices for a sample of different brands of bathroom scales: 49 51 50 28 65 40 45 22 33 30 72 50 21 30 34 25 25 113 36 37 65 20 19 29 24 48 17 21 24 39 25 22 60 30 36 12 29 25 45 81 QUESTION #1 (2pts) The data is (circle one) : DISCRETE CONTINUOUS INSTRUCTIONS Use the "EXCEL FOR STATISTICAL ANALYSIS" (a complete version can be found on CANVAS) below as a guide to obtain (using EXCEL on a PC, not a MAC) needed information to answer the questions on this and future assignments (HW2 and HW3). MAC EXCEL OUTPUT differs from that generated by a PC and all output in exams will be in PC format. However, you may use a MAC for EXCEL in all other instances. When you are done, your EXCEL OUTPUT must include an ordered array, descriptive statistics, and a histogram obtained through instructions that follow. Round to the nearest ten thousandth (4 decimal places) during calculations. Your final answers must also be given to the nearest ten thousandth. EXCEL FOR STATISTICAL ANALYSIS Click on FILE, then NEW to get a spreadsheet. Enter label and data in column A. Be extremely careful when inputting your data! A1 = RETAIL PRICE ($), A2 = 49, A3 = 72, A4 = 65... Obtain an ORDERED ARRAY: Sort the data in Column A into ascending order: Click on DATA, SORT, and make your choices. You now have an ordered array. continued... QUESTION #2 Before you continue, create a FREQUENCY distribution for this data using the method discussed in Module 1.3. (a) Calculate the class width below (show your work for credit). Use 7 classes. (3pts) We will round up so that CW = 15. (b) Fill in the frequency distribution starting the first class at 10. The first lower limit is given. See your class notes, and use the ordered array that you created in EXCEL. This should be completed before you create a histogram in EXCEL as outlined on the next page. (8pts) 10 RETAIL PRICE (dollars) FREQUENCY (c) What is the relative frequency of the 4th class? ANSWER: SHOW YOUR WORK FOR CREDIT (d) What percentage of the bathroom scales have a retail price of less than $40? SHOW ALL YOUR WORK FOR CREDIT ANSWER: (2pts) (2pts) continued... BACK TO EXCEL... Obtain DESCRIPTIVE STATISTICS: a. Click on DATA (or TOOLS), DATA ANALYSIS, DESCRIPTIVE STATISTICS, OK (If DATA ANALYSIS menu does NOT appear or give DESCRIPTIVE STATS, try: FILE, OPTIONS, ADD-INS, and add in the ANALYSIS TOOLPAK) b. Choose the following: INPUT RANGE is Al:A41 Grouped by COLUMNS, check LABELS (why is this necessary?) OUTPUT RANGE (where to place output): D1 (or elsewhere) SUMMARY STATISTICS (then OK) (You must adjust the column widths to read the table) Construct HISTOGRAM (EXCEL does this in a complicated way): We will use the frequency distribution you created in QUESTION %#232 on the previous page. * Subtract 0.1 from the upper boundaries/limits of the classes. These are called the BINS (EXCEL requires this in order to produce an accurate histogram). To ensure bars of equal width, we add a first bin number which is obtained by subtracting 0.1 from the first lower limit of the frequency distribution. See class notes for a further explanation of terms. Enter BINS with label in column B: B1 = BINS, B2 = 9.9, B3 = 24.9, ' B9=> a. Click on DATA (or TOOLS), DATA ANALYSIS, HISTOGRAM (If DATA ANALYSIS menu does NOT appear or give HISTOGRAM, try: FILE, OPTIONS, ADD-INS, & add in the ANALYSIS TOOLPAK) b. Choose the following: INPUT RANGE is A1:A41 BIN RANGE is B1:B9. LABELS NEW WORKSHEET PLY (name it "HISTOGRAM") CHART OUTPUT OK (note: your histogram will appear on a separate SHEET) continued... c. Click tab (at the bottom) marked HISTOGRAM and edit your histogram. You will see a histogram with gaps. To eliminate gaps (see page 101-102 of text if necessary): Click on any bar in graph. On the drop-down menu, choose OPTIONS, go to GAPS, choose 0 (zero). OR Right-click on any bar and choose FORMAT DATA SERIES. Under "Series Options" reduce the GAP to 0%. Change the bar borders to a visible color. "Pull down" on the histogram to lengthen the chart area. Edit label along x-axis to RETAIL PRICE. d. PRINT the histogram. e. Click back to "Sheet 1" for spreadsheet with ordered array & descriptive statistics. PRINT SAVE YOUR EXCEL OUTPUT. You will need to make extra copies for HW2 and HW3 which are a continuation of this assignment. Upload these 4 sheets and your resulting EXCEL OUTPUT (preferably in one file) to Canvas in pdf form. Submit ONE assignment as a group (it does not matter who submits). Make sure the names of everyone in the group who contributed are written on the first page of the assignment (20 points will be deducted if students who did not contribute are included). All group members are responsible for approving the final draft/submission. You must submit all assignments to Canvas in pdf form and not in jpg, jpeg, or any other picture format. If you have a smartphone, you can download a free scanning app and use your camera to scan your work and convert to a pdf form that can be uploaded. There are plenty of free ones so you should not have to pay. If you have an i-Phone, you can use NOTES to scan your document into a pdf document. Following directions & your EXCEL OUTPUT together are worth 22 points. The following apply to ALL homework assignments: The assignment will be graded as a major project and your presentation will reflect the importance you place on your grade. Follow directions carefully and show your work when required!! Answer all questions (by hand) directly on these pages. Round to the nearest ten thousandth (4 decimal places) during calculations. Your final answers must also be given to the nearest ten thousandth.