Exel Spreadsheet functions/formulas

SUM function

The SUM function adds all the numbers that you specify as arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.). Each argument can be a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.), a cell reference (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.), an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.), a constant (constant: A value that is not calculated. For example, the number 210 and the text "Quarterly Earnings" are constants. An expression, or a value resulting from an expression, is not a constant.), a formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).), or the result from another function. For example, SUM(A1:A5) adds all the numbers that are contained in cells A1 through A5. For another example, SUM(A1, A3, A5) adds the numbers that are contained in cells A1, A3, and A5.

Syntax

=SUM(number1, [number2], [number3], [number4], ...)

The SUM function syntax has the following arguments:

• number1  Required. The first item that you want to add.
• number2, number3, number4, ...  Optional. The remaining items that you want to add, up to a total of 255 items.

AVERAGE function

Description

Returns the average (arithmetic mean) of the arguments. For example, if the range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) A1:A20 contains numbers, the formula =AVERAGE(A1:A20) returns the average of those numbers.

Syntax

=AVERAGE(number1, [number2],...)

The AVERAGE function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

• number1  Required. The first number, cell reference (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.), or range for which you want the average.
• number2, ...  Optional. Additional numbers, cell references or ranges for which you want the average, up to a maximum of 255.

MAX function

Returns the largest value in a set of values.

Syntax

=MAX(number1,number2,...)

Number1, number2, ...   are 1 to 255 numbers for which you want to find the maximum value.

MIN function Returns the smallest number in a set of values. Syntax =MIN(number1,number2,...) Number1, number2, ...   are 1 to 255 numbers for which you want to find the minimum value. RANK function Returns the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list. (If you were to sort the list, the rank of the number would be its position.) Syntax =RANK(number,ref,order) Number   is the number whose rank you want to find. Ref   is an array of, or a reference to, a list of numbers. Nonnumeric values in ref are ignored. Order   is a number specifying how to rank number. If order is 0 (zero) or omitted, Microsoft Excel ranks number as if ref were a list sorted in descending order. If order is any nonzero value, Microsoft Excel ranks number as if ref were a list sorted in ascending order.  IF function Description The IF function returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE. For example, the formula =IF(A1>10,"Over 10","10 or less") returns "Over 10" if A1 is greater than 10, and "10 or less" if A1 is less than or equal to 10. Syntax =IF(logical_test, value_if_true, [value_if_false]) COUNTIF function Description The COUNTIF function counts the number of cells within a range that meet a single criterion that you specify. For example, you can count all the cells that start with a certain letter, or you can count all the cells that contain a number that is larger or smaller than a number you specify. For example, suppose you have a worksheet that contains a list of tasks in column A, and the first name of the person assigned to each task in column B. You can use the COUNTIF function to count how many times a person's name appears in column B and, in that way, determine how many tasks are assigned to that person.  Syntax =COUNTIF(range, criteria)   SUMIF function Description You use the SUMIF function to sum the values in a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) that meet criteria that you specify. For example, suppose that in a column that contains numbers, you want to sum only the values that are larger than 5. You can use the following formula: =SUMIF(B2:B25,">5") In this example, the criteria is applied the same values that are being summed. If you want, you can apply the criteria to one range and sum the corresponding values in a different range. For example, the formula =SUMIF(B2:B5, "John", C2:C5) sums only the values in the range C2:C5, where the corresponding cells in the range B2:B5 equal "John."  Note    To sum cells based on multiple criteria, see SUMIFS function. Syntax =SUMIF(range, criteria, [sum_range]) AVERAGEIF function Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria. Syntax =AVERAGEIF(range,criteria,average_range) Range  is one or more cells to average, including numbers or names, arrays, or references that contain numbers. Criteria  is the criteria in the form of a number, expression, cell reference, or text that defines which cells are averaged. For example, criteria can be expressed as 32, "32", ">32", "apples", or B4. Average_range  is the actual set of cells to average. If omitted, range is used.