Hot Topics in Analytics

# Excel: Functions ### MATHEMATICAL FUNCTION

#### 1. SUM: Sum function is used to calculate a simple total for a range of numeric cells.

SYNTAX:  =SUM (number1, [number2], …)

EXAMPLE: Add numbers stored in cell from cell numbers D1 to D4. #### 2. SUM PRODUCT: Returns the sum of the products of corresponding ranges of array

SYNTAX: =sum product (array1, array2, [array3],…)

ARRAY1:  Required. The first array argument whose components you want to multiply and then add.

ARRAY2, ARRAY3: Optional. Array arguments 2 to 255 whose components you want to multiply and then add.

EXAMPLE:

In this context an array is a continuous range of cells in a rows or columns. The number of  Cells must be the same in each array. #### 3. ROUND: Round a number to a specified number of decimal digits

SYNTAX: =ROUND (number, num_digits)

NUMBER: Required. The number that you want to round.

NUM DIGITS: Required. The number of decimal digits to which you want to round the number argument.

EXAMPLE: Round a number to two decimal places.

#### 4. ROUNDUP: Rounds a number up, away from zero

SYNTAX: =ROUNDUP (number, num_digits)

NUMBER:   Required. Any real number that you want rounded up.

NUM_DIGITS: Required. The number of digits to which you want to round number.

EXAMPLE:  Round a number to one decimal places.

#### 5. SUBTOTAL: Returns a subtotal of a filtered list or database

SYNTAX: =SUBTOTAL (function_num, ref1, [ref2], ….)

FUNCTION_NUM: The number 1 – 11 or 101 to 111 that specifies the functions to use for

Subtotal.

For the function_num constants from 1 to 11, the subtotal function includes the values

Of rows hidden by the hide rows command.

For the function_num constants 101 to 111, the subtotal function ignores values of

Rows hidden by the hide rows command.

REF1: Required. The first named range or reference for which you want the subtotal.

REF2: Optional. Named ranges or references 2 to 254 for which you want the subtotal.

EXAMPLE: #### 6. TRUNC: Truncates a number to an integer by removing the decimal, or fractional,

Part of number

SYNTAX:  =TRUNC (number, [num_digits])

NUMBER:  Required. Number is an integer who’s decimal of fractional part as to be removed.

NUM_DIGITS: It is the number of decimal places to display in the resulting truncated number.

EXAMPLE: Truncates value to return the integer part. #### 7. INT: Rounds a number down to the nearest integer

SYNTAX: =INT (number)

NUMBER: Required. The real number you want to round down to an integer.

EXAMPLE: #### 8. MOD: Returns the remainder after a number is divided by a divisor

SYNTAX: =MOD (number, divisor)

NUMBER: Required. The number for which you want to find the remainder.

DIVISOR: Required. The number by which you want to divide number.

EXAMPLE: #### 9. POWER: Returns the result of a number raised to a power

SYNTAX: =POWER (number, power)

NUMBER: Required. The base number. It can be any real number.

POWER:  Required. The exponent to which the base number is raised.

EXAMPLE: #### 10. SQRT: Returns a positive square root of a number

SYNTAX: =SQRT (number)

NUMBER: Required. The number for which you want the square root.

EXAMPLE: #### 11. ABS: Returns the absolute value of a number

SYNTAX: =ABS (number)

NUMBER: Required. The real number of which you want the absolute value.

EXAMPLE: #### 12. SUMIF: Add the cells specified by a given condition or criteria

SYNTAX: =SUMIF (range, criteria, [sum_range])

RANGE: Is the range of cells you want to test.

CRITERIA: It is the criteria in the form of a number, expression, or text that defines which

SUM RANGE: These are the actual cells to sum. The cells in sum range are summed only if their corresponding cells in range match the criteria. If sum range is omitted, the cells in range are summed.

EXAMPLE:

For each call to the Excel Sum if function the range argument is either the cell range A3- A10 or the cell range B3 – B10, and the sum range argument is the cell range C3 – C10. #### 13. SUMIFS: Add the cells specified by a given set of conditions or criteria

SYNTAX: =SUMIFS (sum_range, criteria_range1, criteria1, criteria_range2, criteria2, …)

SUM RANGE: Is one or more cells to sum, including numbers or names, arrays, or references that contain number. Blank and text values are ignored.

CRITERIA_RANGE1, CRITERIA_RANGE2: Arrays of values (or ranges of cells containing values) to be tested against the respective criteria1, criteria2.

The supplied criteria range arrays must all have the same length as the sum range.

CRITERIA1, CRITERIA2: The conditions to be tested against the values in criteria_range1, criteria_range2.

EXAMPLE: ### STATISTICAL FUNCTIONS

#### 1. COUNT: Count the number of cells in a range that contain numbers

SYNTAX: =COUNT (value1, [value2],…)

VALUE1: Required. The first item, cell reference, or range within which you want to count numbers.

VALUE2: Optional. Up to 255 additional items, cell references, or ranges within which you want to count numbers.

EXAMPLE: #### 2. COUNTIF: Count the number of cells within a range that meet the given condition

SYNTAX: =COUNTIF (range, criteria)

RANGE: Is one or more cells to count, including numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.

CRITERIA: Is the criteria in the form of a number, expression, cell reference, or text that defines which cells will be counted.

EXAMPLE: #### 3. COUNTIFS: Counts the number of cells specified by a given set of conditions or criteria’s

SYNTAX: =COUNTIFS (criteria_range1, criteria1, critera_range2, criteria2,…)

CRITERIA_RANGE1: Required. The first range in which to evaluate the associated criteria.

CRITERIA1: Required. The criteria in the form of a number, expression, cell reference, or text that

Define which cells will be counted.

CRITERIA_RANGE1, CRITERIA2: Optional. Additional ranges and their associated criteria.

Up to 127 range/criteria pairs are allowed.

EXAMPLE: #### 4. COUNTA: Counts the number of cells in a range that are not empty

SYNTAX: =COUNTA (value1, [value2],..)

VALUE1: Required. The first argument representing the values that you want to count.

VALUE2: Optional. Additional arguments representing the values that you want to count, up to a maximum of 255 arguments.

EXAMPLE: #### 5. COUNTBLANK: Counts the number of empty cells in a specified range of cells

SYNTAX: =COUNTBLANK (range)

RANGE: The range from which you want to count the blank cells.

EXAMPLE: ### LOGICAL FUNCTIONS

#### AND: The AND function returns TRUE if all conditions are true and returns FALSE if any of the conditions are false.

SYNTAX: =IF (AND (logical1, logical2], …)

EXAMPLE: #### AND: The AND function returns TRUE if all conditions are true and returns FALSE if any of the conditions are false.

SYNTAX: =NOT (AND (logical1,logical2], …)

EXAMPLE: #### 3. OR: The OR function returns TRUE if any of the conditions are TRUE and returns FALSE if all conditions are false.

SYNTAX: =OR (logical1,logical2], …)

EXAMPLE: #### 4. IF TRUE: Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

SYNTAX: =IF (logical_test, [value_if_true], [value_if_false])

EXAMPLE: #### 5. AND: Checks whether all arguments are TRUE, and returns TRUE if all arguments are TRUE

SYNTAX: =AND (logical1, [logical2], …)

EXAMPLE: #### 6. NOT: changes FALSE to TRUE, or TRUE to FALSE

SYNTAX: =NOT (logical)

EXAMPLE:  