RodriguezDuenezL1

.pdf

School

University of Nevada, Reno *

*We aren’t endorsed by this school

Course

207

Subject

Statistics

Date

Feb 20, 2024

Type

pdf

Pages

Uploaded by BarristerWildcat4111

Lab 1: Using formulas and functions in Excel, Frequency distributions for qualitative data Attached is an Excel file that contains one spreadsheet, containing a short data set, and corresponding instructions (below). Please perform the tasks assigned, answer the questions in the space provided (you only need to provide answers for questions 1-4, 6, 9, and 13) and save the spreadsheet as directed. Please make sure your answers are in blue so they are easier to find. When you’re finished, upload the saved Excel file (worth 30 points ) and the Word document back to this Assignment tab. Save the file as “ YourLastName L1.doc ” so I can identify your assignment. Sheet 1: Gradesheet This spreadsheet contains a column of values. These values represent the final exam scores for the students who took APST 207 last Fall semester. Please discuss this data accordingly: 1. What are the experimental units or cases for this study? (5 points) 2. What is the variable of interest? (5 points) 3. Is the variable of interest quantitative or qualitative? (5 points) 4. What measurement scale (e.g. nominal, ordinal, interval, ratio) does the variable of interest use? How do you know? (Review the “Levels of Measurement” section in your textbook if you need help deciding)(10 points) 5. Use the VLOOKUP function to assign a letter grade for every number grade. The grade distribution is as follows: A = 90 or better, B = 80 – 89, C = 70 – 79, D = 60 – 69, F = 59 or below. This means: (1) creating a reference table, (2) inserting the VLOOKUP function for the first cell (A2), and (3) copying the function for the rest of the cells in the column. In this study, the experimental units/ cases for this study were those students who took APST 207 last Fall semester. The variable of interest in this study is the grades that the students received on their final exams. From the data, the variable of interest is quantitative . The variable of interest is ordinal . We use this type of measurement scale because we are categorizing our variables based on the ranking order.

6. Glance over the distribution of letter grades (column 2). What letter grade seems to be most frequent? Which is the least frequent? (10 points) 7. Once you’ve assigned the letter grades, SORT the numerical data in ascending order. Be sure the letter grades are also sorted along with their corresponding numerical grades. This means highlighting both columns for sorting (right click à Sort à Sort smallest to largest). 8. Construct frequency distributions for these data two ways: a. Construct a pivot table for letter grade and then a column graph. **Note: i. Place the pivot table/graph combination in a new spreadsheet. Rename each new spreadsheet ‘Grades Graphs 1, 2, 3, etc.’ ii. Make sure your graphs are very clear. This means editing the title, axis labels, data labels, bar width, removing extra categories, etc. 9. Based on the column graph, which letter grade is the most frequent? Which is the least frequent? How does this compare to your earlier guess (question number 6)? (20 points) 10. Copy the original data’s spreadsheet and place this new spreadsheet at the end of the list of sheets. Rename the new spreadsheet Gradesheet 2 (right click on the spreadsheet name “Gradesheet” at the bottom and you should have an option to copy the spreadsheet). 11. You realize that three of the grades were entered incorrectly. You want to correct these grades and then redo your calculations. Change all of these grades: 45 to 95, 51 to 91, and 57 to 97. 12. Construct a new pivot table and column graph for these data in a new spreadsheet (copy and paste the original data as instructed in Step 10 and then change the grades). Please put this table and chart in the new spreadsheet, and edit the chart by changing the title, axis labels, data labels, bar width, etc. 13. How do these new data change your conclusion about the most and least frequent letter grades? (15 points) After glancing at the column graph, the letter grade “A” still seems to be the most frequent, meanwhile the letter grade “B” seems to be the least frequent. My earlier guess was still correct. Glancing over the distribution of the letter grades, the letter grade “A” seems to be the most frequent, meanwhile the letter grade “B” seems to be the least frequent. After constructing a new pivot table and graph for incorrectly entered grades, I can safely say that the letter grade "A" still remains to be the most frequent variable, but the letter grade "F" has now become the less frequent variable .

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version