Functions In Excel

Mathematical Functions

Text Functions

Statistical Functions

Logical Functions

Date Functions

Financial Functions

Developed by:

Ashish Dutta
MCA., [Link]., PhD(Pursuing)
Asst. Professor, Computer Application & Systems Management
 Functions In Excel
MATHEMATICAL FUNCTIONS

FACT() To Find the factorial of a number

Syntax: =fact(number)

Example:

=fact(5) or =fact(b2)

Ans: 120

MOD() To Find the reminder of a number from division

Syntax: =mod(number,divisor)

Example:

=MOD(B2,2) or =mod(5,2)

Ans: 1

Excel/Fun : 2 Prepared by: Prof. Ashish Dutta ([Link]@[Link])

PRODUCT() To Find the Product of numbers
Syntax: =Product(num1, num2, num3, ….)

Example:
=Product(B1,B2,B3) or =Product(5,2,6)

Ans: 60

ROUND() Number rounded to a specific number of digits

Syntax: =Round(number, number of digits)

Example:
=Round(B1,1) or =Round(74.66,1)

Ans: 74.7

SQRT() To Find the Positive square root of a number

Syntax: =Sqrt(number)

Example:
=Sqrt(B1) or =Sqrt(49)

Ans: 7

Excel/Fun : 3 Prepared by: Prof. Ashish Dutta ([Link]@[Link])

ABS() To find the absolute value of a number
Syntax: =Abs(number)

Example:
=Abs(B1) or =Abs(5)

Ans: 5

SUM() To find the Total of a set of numbers

Syntax: =Sum(num1, num2, num3, ….)

Example:
=Sum(B1,B2,B3) or =Sum(10,20,30)

Ans: 60

INT() Return the integer part of a number

Syntax: =INT(num)

Example:
=Int(B1) or =Int(75.67)

Ans: 75

Excel/Fun : 4 Prepared by: Prof. Ashish Dutta ([Link]@[Link])

PI() Return the value of PI. i.e 3.14
Syntax: =PI()

Example:
=PI()

Ans: 3.141593

ROMAN() Convert an Arabic numeral to Roman digits.

Syntax: =Roman(num)

Example:
=Roman(B1) or =Roman(7)

Ans: VII

EVEN() Rounds a number up to the next even number.

Syntax: =Even(num)

Example:
=Even(B1) or =Even(7.6)

Ans: 8

Excel/Fun : 5 Prepared by: Prof. Ashish Dutta ([Link]@[Link])

ODD() Rounds a number up to the next odd number.
Syntax: =Odd(num)

Example:
=Odd(B1) or =Odd(8)

Ans: 9

TEXT FUNCTIONS

CONCATENATE() To join several text items into one text item

Syntax: =Concatenate(text1, text2, Text3, …..)

Example:
=Concatenate(B1,B2) or =Concatenate(“WEL”, “COME”)

Ans: WELCOME

LEN() Counts the number of character (including space) in a text

Syntax: =Len(text)

Example:
=Len(B1) or =Len(“WELCOME”)

Ans: 7

Excel/Fun : 6 Prepared by: Prof. Ashish Dutta ([Link]@[Link])

UPPEER() Convert the text into UPPERCASE
Syntax: =Upper(text)

Example:
=Upper(B1) or =Upper(“manager”)

Ans: MANAGER

LOWER() Convert the text into lowercase

Syntax: =Lower(text)

Example:
=Lower(B1) or =Lower(“MANAGEMENT”)

Ans: management

MID() To Extract a specific number of characters

Syntax: =MID(text, start no, no of characters)

Example:
=MID(B1,3,3) or =MID(“MANAGEMENT”,3,3)

Ans: nag

Excel/Fun : 7 Prepared by: Prof. Ashish Dutta ([Link]@[Link])

FIND() Returns the position of a specified character or sub-string within a
supplied text string. The function is case-sensitive.

Syntax: = FIND( find_text, within_text, [start_num] )

find_text - The character or text string that you wish to find.

within_text - The text string that is to be searched.
[start_num] - An optional argument that specifies the position of the
character from which the search should begin.

Example:
=FIND(“D”,B1) or =FIND(“D”, “INDIAN OIL”)

Ans: 3

SUBSTITUTE() Replaces occurrences of a search text string, within an original text string,
with the supplied replacement text.

Syntax: = SUBSTITUTE( text, old_text, new_text, [instance_num] )

text - The original text string containing the text to be replaced.
old_text - The text to be found and replaced by new_text.
new_text - The new text that is to be used to replace the old_text.
[instance_num] - An optional argument which specifies which occurrence
of old_text should be replaced by the new_text.

Example:
=SUBSTITUTE(B1, “U”, “A”) or =SUBSTITUTE(“DUTTA”, “U”, “A”)

Ans: 3

Excel/Fun : 8 Prepared by: Prof. Ashish Dutta ([Link]@[Link])

EXACT() Return a value TRUE or FALSE depending on likeness of 2 text strings
Syntax: =Exact(text1, text2)

Example:
=Exact(B1,B2) or =Exact(“KOL”, “CAL”)

Ans: FALSE

REPT() Text repeats specified no of times.

Syntax: =Rept(text, num)

Example:
=Rept(B1,10) or =Rept(“*”,10)

Ans: * * * * * * * * * *

PROPER() Convert the text into Title Case

Syntax: =Proper(text)

Example:
=Proper(B1) or =Proper(“computer application in business”)

Ans: Computer Application In Business

Excel/Fun : 9 Prepared by: Prof. Ashish Dutta ([Link]@[Link])

LEFT() Returns a specified number of characters from the start of a supplied text string.
Syntax: = LEFT( text, [num_chars] )

Example:
=LEFT(B1,3) or =LEFT(“INDIAN OIL”,3)

Ans: IND

RIGHT() Returns a specified number of characters from the end of a supplied text string.
Syntax: = RIGHT( text, [num_chars] )

Example:
=RIGHT(B1,3) or =RIGHT(“INDIAN OIL”,3)

Ans: OIL

TRIM() Removes any spaces, except for single spaces between words or characters.
Syntax: = TRIM( text)

Example:
=TRIM(B1) or =TRIM(“Institute of Business Management”)

Ans: Institute of Business Management

Excel/Fun : 10 Prepared by: Prof. Ashish Dutta ([Link]@[Link])

 STATISTICAL FUNCTIONS

AVERAGE() Find the average of arguments.

Syntax: =Average(num1, num2, num3, ….)

Example:
=Average(B1,B2,B3) or =Average(11,12,13)

Ans: 12

COUNT() Counts how many arguments are present in the list of arguments.
Syntax: =Count(Cell Range)

Example:
=Count(B1:B3)

Ans: 2

MAX() Locates the maximum value out of a set of values.

Syntax: =Max(Cell Range)

Example:
=MAX(B1:B3)

Ans: 20

Excel/Fun : 11 Prepared by: Prof. Ashish Dutta ([Link]@[Link])

MIN() Counts how many arguments are present in the list of arguments.
Syntax: =MIN(Cell Range)

Example: =MIN(B1:B3) Ans: 10

COUNTIF() Returns the number of cells, that satisfy a given criteria.

Syntax: = COUNTIF( range, criteria )

Example: =COUNTIF(B2:B6, “Sunday”) Ans: 2

Example: =COUNTIF(B2:B6, “>60”) Ans: 2

Excel/Fun : 12 Prepared by: Prof. Ashish Dutta ([Link]@[Link])

 LOGICAL FUNCTIONS
AND() Return TRUE if and only if all the arguments are true.
Syntax: =AND(Condition 1, Condition 2)
Example: =IF(AND(A2="M",B2>30),"OK","CANCEL")

OR() Return TRUE if any one argument among the lot is true.
Syntax: =OR(Condition 1, Condition 2)
Example: =IF(OR(A2="M",B2>40),"OK","CANCEL")

IF() Return a specific value depending on the outcome of the test. It consist of three
parts 1st the condition to be tested, 2nd the value to be return if theargument is
true and 3rd the value to be returned if the argument is false.
Syntax: =IF(Condition 1, 2nd Part, 3rd Part )
Example: =IF(A2>=50,"PASS","FAIL")

Excel/Fun : 13 Prepared by: Prof. Ashish Dutta ([Link]@[Link])

 DATE FUNCTIONS

NOW() Return the Current Date & Time

Syntax: =NOW()

Example:
=NOW() Ans: 11/15/2011 13:31

TODAY() Return Current Date, Default format is MM/DD/YYYY

Syntax: =TODAY()

Example:
=TODAY() Ans: 11/15/2011

DAY() Return Day of the month.

Syntax: =Day(Date)

Example:
=Day(B1) Ans: 15

Excel/Fun : 14 Prepared by: Prof. Ashish Dutta ([Link]@[Link])

MONTH() Return the month number.
Syntax: =Month(Date)

Example:
=Month(B1) Ans: 11

YEAR() Return the Year from the Date.

Syntax: =Year(Date)

Example:
=Year(B1) Ans: 2011

DAYS360() Return the number of date between two dates.

Syntax: = Days360(Start date, End Date)

Example:
=Days360(A1,B1) Ans: 12

Excel/Fun : 15 Prepared by: Prof. Ashish Dutta ([Link]@[Link])

 FINANCIAL FUNCTIONS

PMT() Payment require for a loan amount.

Syntax: = PMT(rate,nper,pv)

Rate- Rate of Interest per month

NPER- Total no of payment periods in a year
PV- Present Value

Example:

=PMT(8%/12,12,20000) Ans: $2074.06

NPER() NPER function calculates the number of periods required to pay off a loan, for a
specified constant periodic payment and a constant interest rate.

Syntax: =NPER ( Rate , Payment Amount, Loan Amount)

Rate- Rate of Interest per month

Payment Amount- Payment amount per installment.
Loan Amount- Total Loan Anmount

Example: =NPER(B3%/12,-B2,B1) Ans: 87.508577

Excel/Fun : 16 Prepared by: Prof. Ashish Dutta ([Link]@[Link])

PPMT() Calculates the payment on the principal for a given investment, with periodic
constant payments and a constant interest rate
Syntax: = PPMT( rate, per, nper, pv, [type] )

rate- Rate of Interest per month

per - The period for which the payment on the principal is to be calculated (must be
an integer between 1 and nper).
nper - The number of periods over which the loan or investment is to be paid
pv - The present value of the loan / investment
Type - An optional argument that defines whether the payment is made at the start or
the end of the period.
The type argument can have the value 0 or 1, meaning:

0 - the payment is made at the end of the period

1 - the payment is made at the beginning of the period

Example: =PPMT(B2/12,A9,B3,-B1,0) Ans: 232.03

IPMT() Calculates the interest payment for a given period of an investment, with periodic
constant payments and a constant interest rate
Syntax: = IPMT( rate, per, nper, pv, [type] )

Rate- Rate of Interest per month

Per - The period for which the payment on the principal is to be calculated (must be
an integer between 1 and nper).
nper - The number of periods over which the loan or investment is to be paid
Pv - The present value of the loan / investment

Excel/Fun : 17 Prepared by: Prof. Ashish Dutta ([Link]@[Link])

 Type - An optional argument that defines whether the payment is made at the start or
the end of the period.
The type argument can have the value 0 or 1, meaning:

0 - the payment is made at the end of the period

1 - the payment is made at the beginning of the period

Example: =IPMT(B2/12,A9,B3,-B1,0) Ans: 233.33

SLN() The SLN function calculates the straight line Depreciation of an asset for one period.
Syntax: =SLN( Cost, Salvage, Life )

Cost- The initial cost of the asset

Salvage- The value of the asset at the end of the depreciation
Life- The number of periods over which the asset is to be depreciated

Example: =SLN(B1,B2,B3) Ans: 26000

Excel/Fun : 18 Prepared by: Prof. Ashish Dutta ([Link]@[Link])