or Sort Descending 
.- MIN
- MAX
- COUNT
- COUNTIF
- IF
- AVERAGE
- MODE
- MEDIAN
- STDEV
Data>Sort is a command you've used before. Click a cell
in the data you would like to sort. Click Sort Ascending
or Sort Descending .
Sort the list of earthquakes according to the following parameters:
- Magnitude
- Depth
- Latitude
- Region
MIN(number1,number2, ...) returns the smallest number in a set of values. The arguments are arrays or references to cells that contain numbers.
Example
If A1:A5 contains the numbers 10, 7, 9, 27, and 2, then
MIN(A1:A5)equals 2, andMIN(A1:A5, 0)equals 0.What was the smallest magnitude reported?
What was the minimum reported depth to hypocenter?
MAX(number1,number2, ...) returns the largest number in a set of values. The arguments are arrays or references to cells that contain numbers.
Example
If A1:A5 contains the numbers 10, 7, 9, 27, and 2, then
MAX(A1:A5)equals 27, andMAX(A1:A5,30)equals 30.What was the largest recent earthquake?
What was the deepest reported hypocenter?
What is the range (maximum - minimum) of the earthquake magnitudes?
COUNT(value1,value2, ...) returns the number of cells that contain numbers and numbers within the list of arguments. Arguments that are numbers, dates, or text representations of numbers are counted; arguments that are error values or text that cannot be translated into numbers are ignored. If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored.
Example
COUNT(A1:A7)equals 3
COUNT(A4:A7)equals 2
COUNT(A1:A7, 2)equals 4What is the total number of earthquakes in the list you downloaded?
COUNTIF(range,criteria) returns the number of cells within a range that meet the given criteria. The range is the range of cells from which you want to count cells. The criteria is in the form of a number, expression, or text that defines which cells will be counted. For example, criteria can be expressed as 32, "32", ">32", "apples".
Example
Suppose A3:A6 contain "apples", "oranges", "peaches", "apples", respectively:
COUNTIF(A3:A6,"apples")equals 2. Suppose B3:B6 contain 32, 54, 75, 86, respectively:COUNTIF(B3:B6,">55")equals 2.How many of the reported earthquakes are greater than M2.5?
What percentage of the reported earthquakes are greater than M5?
How many of the earthquakes occurred in the vicinity of Mt. St. Helens?
IF(logical_test,value_if_true,value_if_false) returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE. Use IF to conduct conditional tests on values and formulas.
Logical_test is any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. This argument can use any comparison calculation operator.
Value_if_true is the value that is returned if logical_test is TRUE. For example, if this argument is the text string "Within budget" and the logical_test argument evaluates to TRUE, then the IF function displays the text "Within budget". If logical_test is TRUE and value_if_true is blank, this argument returns 0 (zero). To display the word TRUE, use the logical value TRUE for this argument. Value_if_true can be another formula.
Value_if_false is the value that is returned if logical_test is FALSE. For example, if this argument is the text string "Over budget" and the logical_test argument evaluates to FALSE, then the IF function displays the text "Over budget". If logical_test is FALSE and value_if_false is omitted, (that is, after value_if_true, there is no comma), then the logical value FALSE is returned. If logical_test is FALSE and value_if_false is blank (that is, after value_if_true, there is a comma followed by the closing parenthesis), then the value 0 (zero) is returned. Value_if_false can be another formula.
Nested IFs Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. See the last example below.
Examples
On a budget sheet, cell A10 contains a formula to calculate the current budget. If the result of the formula in A10 is less than or equal to 100, then the following function displays "Within budget". Otherwise, the function displays "Over budget".
IF(A10<=100,"Within budget","Over budget")In the following example, if the value in cell A10 is 100, then logical_test is TRUE, and the total value for the range B5:B15 is calculated. Otherwise, logical_test is FALSE, and empty text ("") is returned that blanks the cell that contains the IF function.
IF(A10=100,SUM(B5:B15),"")Suppose an expense worksheet contains in B2:B4 the following data for "Actual Expenses" for January, February, and March: 1500, 500, 500. C2:C4 contains the following data for "Predicted Expenses" for the same periods: 900, 900, 925.
You can write a formula to check whether you are over budget for a particular month, generating text for a message with the following formulas:
IF(B2>C2,"Over Budget","OK")equals "Over Budget"
IF(B3>C3,"Over Budget","OK")equals "OK"Suppose you want to assign letter grades to numbers referenced by the name AverageScore. See the following table.
If AverageScore is Then return Greater than 89 A From 80 to 89 B From 70 to 79 C From 60 to 69 D Less than 60 F
You can use the following nested IF function:
IF(AverageScore>89,"A",IF(AverageScore>79,"B", IF(AverageScore>69,"C",IF(AverageScore>59,"D","F"))))In the preceding example, the second IF statement is also the value_if_false argument to the first IF statement. Similarly, the third IF statement is the value_if_false argument to the second IF statement. For example, if the first logical_test (Average>89) is TRUE, "A" is returned. If the first logical_test is FALSE, the second IF statement is evaluated, and so on.
Use the IF function to assign designations of small (<M3), moderate (M3-M5), and large (M6 or greater) to each earthquake in the list.
Use the IF function to designate each earthquake as shallow, intermediate, or deep.
AVERAGE(number1,number2, ...) Returns the average (arithmetic mean) of the arguments. When averaging cells, keep in mind the difference between empty cells and those containing the value zero. Empty cells are not counted, but zero values are.
Example
If A1:A5 is named Scores and contains the numbers 10, 7, 9, 27, and 2, then
AVERAGE(A1:A5)equals 11.What is the average depth of the earthquakes?
What is the average magnitude of the earthquakes?
MODE(number1,number2, ...) returns the most frequently occurring, or repetitive, value in an array or range of data.
Example
MODE({5.6, 4, 4, 3, 2, 4})equals 4.
MEDIAN(number1,number2, ...) returns the median of the given numbers. The median is the number in the middle of a set of numbers; that is, half the numbers have values that are greater than the median, and half have values that are less. If there is an even number of numbers in the set, then MEDIAN calculates the average of the two numbers in the middle. See the second example below.
Examples
MEDIAN(1, 2, 3, 4, 5)equals 3.
MEDIAN(1, 2, 3, 4, 5, 6)equals 3.5, the average of 3 and 4.
STDEV(number1,number2,...) estimates standard deviation based on a sample. The standard deviation is a measure of how widely values are dispersed from the average value (the mean). +/- 3 standards deviations always contain 90 % of the data. You can also use a single array or a reference to an array instead of arguments separated by commas.
STDEV assumes that its arguments are a sample of the population. If your data represents the entire population, then compute the standard deviation using STDEVP.STDEV uses the following formula:
Example
Suppose 10 tools stamped from the same machine during a production run are collected as a random sample and measured for breaking strength. The sample values (1345, 1301, 1368, 1322, 1310, 1370, 1318, 1350, 1303, 1299) are stored in A2:E3, respectively. STDEV estimates the standard deviation of breaking strengths for all the tools.
STDEV(A2:E3)equals 27.46What is the standard deviation of the earthquake depths?