Due Tuesday, Oct. 27 by 8:00pm
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!
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:
C31
)D31
, E31
)H31
)J31
)K31
, where Mount Everest does not have a parent mountain)L31
), and
K31
)
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:
SUM
and COUNT
functions, along with division, to find the averageAVERAGE
function that finds the average directlyD30
: "Height (m)", appears to be the perfect column!D31
and extends until D532
.D31:D532
E9
.
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:
COUNT
function does exactly that!COUNTIFS
E30
: "Height (ft)", appears to be the perfect column!E31:E532
E9
.
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)
>20000
. However, ">" is
not a number and must be entered as a String.
&
), to join
a string with another value.
criteria1
is ">" & 20000
E10
.
When your formula generates the correct result, a green check mark will appear
in the cell next to the answer.
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
">" & D11
works great for criteria1
!
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.
To submit your MP, you must submit your completed Excel file to the CS 105 website. Ensure you save your file before uploading it.