The minimum and maximum of a dataset can be very useful statistics, and arerelatively simple to calculate. These statistics only apply to quantitativevariables.
Minimum Definition
The minimum value is the smallest value in the dataset, or the value thatall other values in the dataset are greater than or equal to.
Maximum Definition
The maximum value is the largest value in the dataset, or the value thatall other values in the dataset are less than or equal to.
The minimum or maximum value is sometimes the only value you need to know. Forexample, suppose your university has organized a field trip for your class to aconcert, but the event is at a 21+ venue so people under the age of 21 are notallowed in. In this case, knowing that the minimum age of the students in yourclass is 21 is sufficient, as that tells you that everyone in the class is atleast 21 and that all members of the class can go on the field trip.
Example: Dice Roll¶
Consider rolling a standard dice.
There are six faces.
Each face is equally likely to land face up.
The faces are labelled as follows: 1, 2, 3, 4, 5, 6.
It might seem unnecessary to use Sheets to calculate the minimum and maximumpossible results of a dice roll, but when there are thousands of values insteadof six, using Sheets or some other tool will be a necessity.
You can calculate the minimum and maximum value in Sheets using the MIN andMAX functions respectively.
Minimum and Maximum in Sheets
The MIN function returns the minimum value of a set of values. You caneither input several values separated by a comma (e.g.=MIN(value1, value2, value3)), or you can input a range of cells of whichyou want to know the minimum (e.g. =MIN(A1:A10)).
The MAX function returns the maximum value of a set of values. You caneither input several values separated by a comma (e.g.=MAX(value1, value2, value3)), or you can input a range of cells forwhich you want to know the maximum (e.g. =MAX(A1:A10)).
This example illustrates how to calculate the minimum value of a dice roll usingMIN, but the exact same logic and syntax applies to calculating the maximumusing MAX. As stated above, there are two ways to calculate the minimumvalue of a dice roll.
In the first way, each value is input into the MIN function, separated by acomma.
Alternately, you can specify all the values in different cells, and input thecell range into the MIN function.
In future examples, you will see that specifying a cell range is the moreefficient way to use MIN, MAX, and other statistical functions.
Example: Weather¶
Suppose you want to know the minimum and maximum temperature that New York City(NYC) generally experiences in a year.
The weather dataset previously seen here has the field“actual_min_temp” which records the coldest temperature every day, and a field“actual_max_temp” which records the highest temperature every day. (For thisexample, only NYC weather is considered so the “city” column is removed, and themonth is not relevant so the “month_text” column is removed.)
This dataset for twelve months contains just 365 data points. It would betime-consuming but not impossible to scan each column visually and find theminimum and maximum values. But imagine if this dataset covered every day forone-hundred years! Sheets would be able to find the minimum and maximum just asquickly as it did for twelve months. Doing this manually, however, iserror-prone and would not be fun.
Optional: Match¶
Knowing how to find the minimum and maximum values in a spreadsheet is usefulfor many situations, but sometimes it can be even more useful to know which rowthe minimum or maximum came from.
Match Definition
MATCH returns the relative position of an item in a range that matches aspecified value. We can use the MATCH function to find the row of theminimum or maximum.
The MATCH function has three inputs and looks like this:MATCH(search_key, range, [search_type]).
search_key: The value to search forrange: The values of the column that you want to search (ex. A1:A5)search_type: The manner in which to search1 causes
MATCHto assume that the range is sorted in ascending orderand return the largest value less than or equal to search_key0 indicates an exact match, and is required when the range is not sorted
-1 causes
MATCHto assume that the range is sorted in descendingorder and return the smallest value greater than or equal tosearch_key
To practice using MATCH, suppose a company called CandyData handed you theHalloween Candy dataset from FiveThirtyEight with information about variousHalloween candies. Suppose they ask you to find out which of the candies is mostexpensive. You know that you need to find the row with the highest value in thePrice Percent column, so you can use the MATCH function!
Now you must start filling in the inputs for MATCH. The first input is thevalue you’re searching for. You’re looking for the maximum value in the column,and you know that to find the maximum value in a column you can use the MAXfunction (MAX(C2:C86)). So now you can fill in the first part of theMATCH function: MATCH(MAX(C2:C86), something, something).
The second input is the range of the values of the column that you want tosearch. Since you want to find the value in the column called Price Percent,you fill in the next part of the MATCH function: MATCH(MAX(C2:C86), C1:C86,something).
Notice that if you use C2:C86 instead of C1:C86 instead, the row valuereturned by the function will be shifted up by one, so the answer will be 53instead of 54. This is because the returned value is equal to how far down thevalue is in the range, so when you omit the first row in the range (C1),the returned value will be one less than the row number because it’s countingthe rows starting at C2.
This is what that bug would look like if you were using a smaller dataset andtrying to find the state with the largest population:
The last input is the manner in which you want to search. Since the values inPrice Percent aren’t sorted, you use 0. The final function is=MATCH(MAX(C2:C86), C1:C86, 0). The returned value is 46, meaning the mostexpensive candy is in row 46. You can now go back CandyData and tell them that“Nik L Nip” is the most expensive candy on the dataset.
Practice using the MATCH, MAX, and MIN functions to answer thefollowing questions:
You have attempted of activities on this page
