### 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!

### 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!

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.