Functions are the predefined formulas that perform calculations on specific values which are called arguments. Each function takes specific types of arguments such as numbers, references, text or logical values. We can enter the arguments; enclosing them in parentheses after the function name for example the SUM function adds values or range of values. Functions in excel are divided in following categories.
Mathematical
Logical
Statistical
Date and Time
Text
Financial
Math and Trigonometry Functions
Mathematical functions are used to perform wide variety of simple or complex calculations such as totaling the value for a range of cells, rounding of a number.
SUM (): Adds all the numbers in a range of cells.
Syntax: SUM (number 1, number 2…………..)
For Example:
= SUM (9, 8) =17
We can select lots of numbers such that=SUM (9, 8, 3, 5) =25
Round (): Round () function rounds off a number to a specified number of digits.
Syntax: Round (Number, num_digits), where number is the number we want to round.
Steps for Round a Number:
1. If num_digits is greater than 0 then number is rounded to the specified number of decimal places.
2. If num_digits is 0 then number is rounded to the nearest integer.
3. If num_digits is less then, 0 than number is rounded to the left of the decimal point.
For Example:
= ROUND (5.26, 1) = 5.3
= ROUND (-5.26, 0) = -5
=ROUND (53.26, -1) = 50
Round down ():
Rounddown () function rounds a number down towards zero.
Syntax:
ROUNDDOWN (number, num_digits)
Steps for Rounddown a Number:
1. If num_digits is greater than 0 then number is rounded down to the specified number of decimal places.
2. If num_digits is 0 then number is rounded down to the nearest integer.
3. If num_digits is less then, 0 than number is rounded down to the left of the decimal point.
For Example:
= ROUNDDOWN (5.26, 1) equals 5.2
= ROUNDDOWN (-5.26, 0) equals -5
= ROUNDDOWN (53.26, -1) equals 50
RoundUP: ROUNDUP () function rounds a number up away from 0.
Syntax: ROUNDUP (number, num_digits)
Steps for Roundup a Number:
1. If num_digits is greater than 0 then number is rounded up to the specified number of decimal places.
2. If num_digits is 0 then number is rounded up to the nearest integer.
3. If num_digits is less then, 0 than number is rounded up to the left of the decimal point.
For Example:
= ROUNDUP (5.26, 1) equals 5.3
= ROUNDUP (-5.26, 0) equals -6
= ROUNDUP (53.26, -1) equals 60
ABS (): ABS () function returns the absolute value of a number. The absolute value of a number is the number without any + or _ sign.
Syntax: ABS (NUM)
Where NUM is the real number of which we find the absolute value.
For Example: ABS (-5) = 5 and ABS (5) =5
SQRT (): SQRT () return a positive square root of a number.
SQRT (NUM), where NUM is the number for which we find the square root. If number is negative, SQRT returns the #NUM! Error value because square root of a negative number in not defined.
For Example: SQRT (25) =5
TRUNC (): TRUNC () function truncates number to an integer value by removing the fractional part of the number.
Syntax: TRUNC (NUM, NUM_DIGITS)
For Example:
TRUNC (15.9) = 15
TRUNC (-12.9) = -12
TRUNC (8.9786, 3) = 8.978
Logical Functions:
Logical functions are used when we want to ensure whether a given condition is true or false.
IF (): IF () function used to determine whether a condition specified with in braces is true or false. It returns one value if a specified condition evaluate to TRUE and another value if it evaluates to false.
Syntax:
If (logical_test, value_if_true, value_if_false)
For Example:
=if (A<50, "Fail", if (A<55, "D", if (A<65, "C", if (A<75, "B", if (A>74, "A")))))
TRUE (): TRUE () returns the logical value TRUE and it takes no arguments.
Syntax: TRUE ()
FALSE (): FALSE () function is same as TRUE () but it returns the logical value FALSE.
Syntax: FALSE ()
Statistical Functions: Statistical functions are used to perform statistical analysis on ranges of data. It includes easy as well as complex statistical functions like average, min, max, count etc.
MAX (): Max function returns the largest value in a set of values stored in the cells.
Syntax: MAX (number1, number2 …)
Example: =MAX (5, 6, 4, 9, 11) = 11
MIN (): MIN () function returns the smallest number in a set of values.
Syntax: MIN (number1, number2, ……..)
Example: =MIN (4, 6, 8, 10) = 4
Average (): the average function returns the average of the arguments which are passed in function as an argument.
Syntax: AVERAGE (number1, number2, ……..)
Example: =AVERAGE (6, 7, 5) = 6
COUNT (): Count Function counts the number of cells that contain numbers and the numbers within the list of arguments.
Syntax: COUNT (value1, value2,…….)
Example: =COUNT (1, 3, 6, 8, 5, 9) = 6
Date and Time Function: We can analyze and work with date and time values in formulas . There are different types of date and time function which are below:
Today (): It returns the current date of the system.
Syntax: TODAY ()
Now (): It returns the current date and time.
Syntax: NOW ()
Day (): It returns the day of a date passed as a argument. Integer range lies from 1 to 31.
Syntax: DAY (number)
Example: =DAY (“4-JAN”) = 4
Month (): It returns the month of a date. Range lies from 1 to 12 i.e. January to December.
Syntax: MONTH (number)
Example: =Month (“6-Sept”) = 9
YEAR (): It returns the year.
Syntax: YEAR (Number)
Example: =YEAR (“5/7/2007”) =2007
Text and Data Function: We can use text strings in formulas by text functions. We can also change the case of the string, find out the length of a text string or we can join two strings etc.
Concatenate (): By Concatenate function we can join two or more strings into one text string.
Syntax: Concatenate (text1, text2, …….)
Example: Concatenate (“Total”, “Value”) = “Total Value”
LEFT (): LEFT returns the first character or characters in a text string based on the number of characters specified as arguments.
Syntax: LEFT (text, num_chars)
Example: =LEFT (“PROJECT”, 4) = “PROJ”
RIGHT (): It is same as LEFT but it returns the last character or characters in a text string based on the number of characters specified as arguments.
Syntax: RIGHT (text, num_chars)
Example: =RIGHT (“RAJESH”, 3) =ESH
Financial Function: Financial functions perform common business calculations. There are following function.
SLN (): It returns the straight line depreciation of an asset for one period.
Syntax: SLN (Cost, Last_value, life)
For Example: Suppose we purchased a scooter for 10000 that has a useful life of 10 years and a salvage value of 750. The depreciation allowance for each year is
=SLN (10000, 750, 10) = 925.00
PMT (): Suppose we want to buy a scooter and want to calculate our monthly installment then we can use the PMT () function which is a built in function in Excel 2007.
Comments