Introduction

This MP will continue using basic excel functions you were introduced to in lecture, used in lab, and will master in the MP. This MP will extend your knowledge of Excel beyond lecture, so we'll walk you through the first few questions!

Download the Lab

Download the Excel (.xlsx) file for MP4

MP4 Walkthrough

Exploring the Data Set

In MP4, your data set is a list of over 500 of Earth's tallest mountains. The data set begins below the question set, starting on Row 31. The world's tallest mountain, Mount Everest, is foudn on Row 31. Notice that everything about Mount Everest is contained in Row 31:

• the name of the mountain (C31)
• the height in both meters (m), and feet (ft) (D31, E31)
• the mountain range it is a part of (H31)
• the coordinates of the peak (J31)
• the parent mountain (K31, where Mount Everest does not have a parent mountain)
• the first time the mountain was ascended (L31), and
• the number of successful ascents (K31)

Question 1

Question 1, found in cell C9, asks "What is the average height, in meters, of mountains in our data set?" You are required to write a formula in cell E9 that will generate the correct answer. To complete this, you will need to do figure out several things:

1. Determine the Excel function(s) you will use.
   • You could use both the SUM and COUNT functions, along with division, to find the average
   • Alternatively, Excel provides an AVERAGE function that finds the average directly
2. Determine the data that we want to average.
   • The question asks for the average height, in meters
   • Examining the column headers of our data set, D30: "Height (m)", appears to be the perfect column!
   • Scrolling through the data, we find that the data starts on D31 and extends until D532.
   • Therefore, our range is D31:D532
3. Write the function in cell E9.
   • When your formula generates the correct result, a green check mark will appear in the cell next to the answer.

Question 2

Question 2, found in cell C10, asks "How many mountains are at least 20,000 ft. in height?" You are required to write a formula in cell E10 that will generate the correct answer. To complete this, you will need to do figure out several things:

1. Determine the Excel function(s) you will use.
   • Since you want to find the number of mountains, the COUNT function does exactly that!
   • Since you do want to count only some of the mountains, not all of them, you will need the conditional variation -- COUNTIFS
2. Determine the data that we want to average.
   • The question asks the number of mountains that are at least some height, in feet
   • Examining the column headers of our data set, E30: "Height (ft)", appears to be the perfect column!
   • Looking back to Q1, we see our data is on Row 31 - Row 532. Therefore, the range of data related to the height, in feet, of the mountains is E31:E532
3. Write the function in cell E9.
   • The COUNTIFS function takes in two arguments: criteria_range1 and criteria1.
   • criteria_range1 will be the range of the data we want to check a condition on. In this problem, we found our range is E31:E532.
   • criteria1 is the conditional to be applied to the data. In this problem, we are asked to find mountains that are at least 20,000 feet. In JavaScript, we would write this as:
       if (mountain.height_ft > 20000)
     • In Excel, the first part of the conditional is implicit -- we only specify that we want to find data where the height is >20000. However, ">" is not a number and must be entered as a String.
     • In Excel, we need to join the string ">" with the number 20000. To do this, Excel uses a special character, the ampersand (&), to join a string with another value.
     • Therefore, the value for criteria1 is ">" & 20000
   • Complete the full formula and enter it into E10. When your formula generates the correct result, a green check mark will appear in the cell next to the answer.

Question 3

Question 3 is nearly identical to Question 2, except we now are using a custom value that is entered in cell D11. For questions where there is an orange cell with a value in Column D, your formula must work even when the value in Column D is changed.

In Question 2, you compared the height of the mountains to check if they are greater than 20,000 feet. In question 3, instead of 20,000, you need to use the value in cell D11

• The same logic applies here: ">" & D11 works great for criteria1!

Complete Questions 4-15 on your own!

You have all the tools to complete the rest of this MP!

Grading and Submission

Grading

Your MP must be in Excel's XLSX format. We are unable to accept any other formats. If your file does not open using Excel 2010+, it will not be graded and you will receive a 0/30. We will not open/grade it with Numbers, Google Sheets, or any other program.

Once we are able to open your MP, we will grade each of the 15 questions independently. Each question is worth 2 points, where 2 x 15 = 30 total points. Your answers must appear in E9:E25; do not add any rows or columns.

Submission

To submit your MP, you must submit your completed Excel file to the CS 105 website. Ensure you save your file before uploading it.

Submit your MP4