### 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

Cells will be added.

**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**

** 1. ****IF****:** Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**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:**

** **

**2. NOT****: **Changes FALSE to TRUE, or TRUE to FALSE

**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: **

#### About K.Rajesh:

K.Rajesh is a B.Tech(Electronics & Communication Engineering) . Currently he is working as an Analyst Intern with NikhilGuru Consulting Analytics Service LLP, Bangalore.

## Be the first to comment on "Excel: Functions"