Mid Function.docx

1. Product Function  Purpose: The product function multiplies all the numbers given as argument and returns the result.  Syntax: PRODUCT (number 1, [number 2] ,….) Arguments: number 1 number 2

Required Optional. Can be up to a maximum of 255 arguments

 Example: = PRODUCT (A1, A2) to multiply those two numbers together. = PRODUCT (A1:A3, C1:C3) is equivalent to the formula = A1* A2 * A3 * C1 * C2 * C3 

Suppose, -

5 in cell A2 15 in cell A3 30 in cell A4

Formula = Product (A2:A4) = PRODUCT (A2:A4, 2) = A2*A3*A4

Result 2250 (2250*2) = 4500 2250

2. Average Function  Purpose: It calculates the average of some numbers.  Syntax: = average (number 1, number 2, …..) Number 1 Number 2, …. Can be up to a maximum of 255

Required Optional

 Example: DATA A2 A3 A4 A5 A6

10 7 9 27 2 55

B2

15

Formula =AVERAGE (A2:A6) = AVERAGE (A2:A6, 5) = AVERAGE (A2:C2)

C2

32

Result (55/5)= 11 (55+5/6)= 10 (10+15+32/3)= 19

3. ABS Function  Purpose: This function removes the sign and returns the absolute value of a number.  Syntax: ABS (number) Number Required = ABS (A1) will return 5 if cell A1 contains -5 or 5  Example: DATA:

-4 in cell A2 Formula =ABS (2) =ABS(-2) =ABS (A2)

Result 2 2 4

4. SQRT Function  Purpose: It returns the square root of a number.  Syntax: SQRT (number) Arguments: Number

Required

1. = SQRT (16) will return 4 2. = SQRT (A1) will also return 4 if the value in cell A1 is 16  Example: Suppose -16 in cell A2 Formula = SQRT (16) = SQRT (A2) = SQRT (ABS (A2))

Result 4 #NUM! 4

5. Power Function  Purpose: A number is raised to a power (multiplying it by itself a certain number of times) and returns the result by using power.  Syntax: Power (number, power) Number Power

Required Required

 Example: Formula = Power (5,2) = Power ( 98.6 , 3.2) = Power ( 4, 5/4)

Result 5^2= 25 2401077.222 5.656854249

6. TRUNC Function  Purpose: The TRUNC function removes decimal without rounding.  Syntax: TRUNC (number, [num_digits]) Number Num_digits

Required Optional

The default value for num_digits is 0 (zero)  Example: If you have 123.567 in cell A1 =TRUNC (A1, 2) will return 123.56 Formula =TRUNC (123.456 , 2) =TRUNC (123.456 , 1) =TRUNC (123.456 , 0) +TRUNC ((123.456)

Result 123.45 123.4 123 123

7. INT Function  Purpose: The INT function truncates a given number down to the closest integer.  Syntax: INT (number) Number is required The INT function always rounds down.  Example: A 1 2 3 4 5 6

5.22 5.99 99.5 -6.1 -100.9

B =INT(100.9) =INT (A2) =INT (A3) =INT (A4) =INT (A5) =INT (A6)

C 100 5 5 99 -7 -101

8. MAX Function  Purpose: The Max function returns the largest value from a supplied set of numeric values.  Syntax: MAX (number 1, [number2], ……) Arguments: Number 1 is required Number 2 and subsequent numbers are optional. Up to 255 numbers for which you want to find the maximum value.  Example:

1 2 3 4 5 6

A 100.9 5.22 5.99 99.5 -6.1 -100.9

= MAX (100.9 , 5.22 , 5.99 , 99.5 , -6.1 , -100.9 ) = MAX (A1: A6) = 100.9

9. MIN Function  Purpose: The MIN function returns the smallest value from a supplied set of numeric values.  Syntax: MIN (number 1, [number2], ……) Arguments:

Number 1 is required Number 2 and subsequent numbers are optional. Up to 255 numbers for which you want to find the minimum value.  Example:

1 2 3 4 5 6 = MIN (100.9 , 5.22 , 5.99 , 99.5 , -6.1 , -100.9 ) = MIN (A1: A6) = -100.9

A 100.9 5.22 5.99 99.5 -6.1 -100.9