HANDS-ON COURSE

EXCEL: FORMULAS
AND FUNCTIONS

By Tahir Aziz
Hands on Course - Ms. Office by Tahir Aziz Ms. Excel: Formulas and Functions

1 INTRODUCTION TO FORMULAS
Excel is a useful tool to perform basic and complex calculations. For this purpose, you can use formulas
or functions in cells to produce the required result.

A formula is an expression written by the user to be calculated. It is constructed using constants, cell
references, operators and functions. You can use up to 8192 characters in a formula.

• Constants: Numbers or text values entered directly into a formula, such as 5 or “Pass”. Keep
remember that when text is entered into a formula it should be mentioned in “”.
• Cell references: These are the cell names which can be used in a formula to refer to a cell e.g. A1 or
to refer to a range of cells e.g A1:A5 refers to cells from A1 to A5.
• Operators: These are the symbols which are used to perform math calculations e.g
✓ + plus sign for addition e.g =5+2 (result 7)
✓ - negative sign for subtraction e.g =10-2 (result 8)
✓ / forward slash for division e.g =10/5 (result 2)
✓ * asteric for multiplication e.g =5*2 (result 10)
✓ ^ caret for taking power e.g =5^2 (result 25)
Note: While performing calculations using above symbols, Excel will follow the DMAS rule of
math i-e it will perform division/multiplication first then addition/subtraction. You can change
the order of calculation by using parenthesis i-e ( ).
• Functions: See below

Examples of formulas:

Formula Description
=A1+5 Adds the number in cell A1 to 5
=A1+A2 Adds the number in cell A1 to A2
=A1+A2*A3 Multiplies the numbers in cell A2 & A3 then adds the result in A1
=(A1+A2)*A3 Adds the numbers in A1 & A2 then multiplies the result with A3
=A1^2 Raises the number in A1 to the power of 2

Functions are predefined built-in programs available in Excel for specific purpose e.g. SUM function is
used to calculate the sum of its arguments, AVERAGE function is used to calculate the average
(arithmetic mean) of its arguments.

Syntax of function

Each function has specific structure (syntax) and is started with = (equal to sign) followed by function
name. Each function has different number of arguments which are mentioned within parenthesis after
the name of function. Function arguments are separated by coma. Some arguments may be optional
and are shown in brackets i-e [ ]. Maximum number of arguments in a function can be 255.

Syntax of SUM function

=SUM(number1,[number2],…)

2 ENTERING A FUNCTION INTO CELL

You can enter a function in to a cell by any of the following ways:

RISE Premier School of Accountancy Page 2

Hands on Course - Ms. Office by Tahir Aziz Ms. Excel: Formulas and Functions

• Manually typing in cell

• Using category menus in Formulas tab
• Using insert function option on formula bar

Manually typing in cell

1. Select the cell where the function is to be entered

2. Type = (equal to sign) then function name. A list of functions will appear showing function names
starting with alphabet you typed. You can select function from that list using arrow keys to select
the required function and pressing Tab key to enter the function in cell or you can complete the

List of functions is appeared.

You can select the function
from list. Description of
selected function is shown next
to the function.

3. Complete the function by typing the cell names or selecting the cells and press Enter key.

RISE Premier School of Accountancy Page 3

Hands on Course - Ms. Office by Tahir Aziz Ms. Excel: Formulas and Functions

Complete the required

arguments i-e reference to
the range of cells and
press Enter key.

Excel will show the result in cell and original formula in formula bar.

Result of function is shown

in cell and original formula
in formula bar.

Using category menus in Formulas tab

All functions in Excel are available in Formulas tab > Function Library group and categorized by their
functionality. You can select the required function from relevant category menu. For this you must
know the category which contains your required function otherwise you can use Insert Function option
(described later).

To enter a function using Formulas tab:

1. Select the cell

2. Click Formulas tab > click the required menu > click the function from list.

RISE Premier School of Accountancy Page 4

Hands on Course - Ms. Office by Tahir Aziz Ms. Excel: Formulas and Functions

Click the relevant menu

and then function to
insert it in the cell.

3. Function Arguments dialogue box will appear where you can enter the required number of
arguments of the function to complete it and press OK.

Excel may enter the adjacent

cell/range reference
automatically or you can
type manually then press OK.

Description of Description of
selected function selected argument

Preview of result Finally click OK

Using insert function option on formula bar

You can enter a formula by using insert function option available on formula bar and Formula tab >
Functions Library group.

1. Select the cell

RISE Premier School of Accountancy Page 5
Hands on Course - Ms. Office by Tahir Aziz Ms. Excel: Formulas and Functions

2. Click Insert function icon on formula bar or Formula tab > Functions Library group.

Click any Insert

function icon

3. Insert Function dialogue box will appear where you can search for function using the search box or
select the required function directly from list and press OK.

Search for a
function

Or select from
list

Finally Click OK

RISE Premier School of Accountancy Page 6

Hands on Course - Ms. Office by Tahir Aziz Ms. Excel: Formulas and Functions

4. Selected function will be inserted in cell and Function Argument dialogue box will be opened.
Adjust the arguments if required and press OK (as shown earlier).

Nested function

Excel allows to use a function as an argument of another function (i-e function within other function). A
function designed in this way is called a nested function. You can nest functions up to 64 levels.

Example of nested function:

=SUM(A1,A5,AVERAGE(B1:B5))

In above example, AVERAGE function is nested into SUM function. SUM function will add numbers of
cells A1, A5 and the average of range B1 to B5.

Types of cell references used in formulas

Cell references which are used while creating formulas or functions are of three types based on their
behaviour of changing while copying from one cell to other.

Relative references:

By default, all cell references are relative references. When copied across multiple cells, they change
based on the relative position of rows and columns. For example, if you copy the formula =A1+B1 from
row 1 to row 2, the formula will become =A2+B2. Relative cell references are useful when you have to
create a formula for a range of cells and the formula needs to refer to a relative cell reference.

Absolute references:

Unlike relative references, absolute references do not change when copied or filled. An absolute
reference is created in a formula by adding the dollar sign ($) before the column and row e.g =$A$1. A
dollar symbol, when added in front of the row and column number, makes it absolute (i.e., stops the
row and column number from changing when copied to other cells.

Mixed references:

Mixed cell references are combination of the absolute and relative cell references. A mixed cell
reference uses a dollar sign ($) either before the column or before the row. For example:

• =A&1 (The row is locked while the column changes when the formula is copied across the
columns.)
• =$A1 (The column is locked while the row changes when the formula is copied down the rows.)

You can change the reference type from relative to absolute or mixed by adding the dollar sign
manually or using F4 shortcut key. For this purpose:

1. Double click the cell which contains the reference or press F2 after selecting the cell to edit it.
2. Select the cell reference within the cell and press F4 again and again to place the $ at required
position.

Editing and Deleting the formula

RISE Premier School of Accountancy Page 7

Hands on Course - Ms. Office by Tahir Aziz Ms. Excel: Formulas and Functions

To edit the formula in a cell, double click the cell or press F2 after selecting it then make required
amendments.

You can delete the formula in a cell by selecting the cell and pressing Delete key on keyboard. In this
way all contents of the cell (i-e formula and its result) are deleted and cell becomes empty.

However, you can retain the result of formula and delete the background formula using Paste Special
command in following way.

1. Select the cell containing the formula and copy it.

2. Right click on same cell > under Paste Options on the menu click Values icon.

Avoiding common formula errors

The following table summarizes some of the most common errors that a user can make when entering
a formula, and explains how to correct them.

Make sure that you Description

Start every function If you omit the equal sign, what you type may be displayed as text or as a
with the equal sign (=) date. For example, if you type SUM(A1:A10), Excel displays the text
string SUM(A1:A10) and does not perform the calculation. If you
type 11/2, Excel displays the date 2-Nov (assuming the cell format
is General) instead of dividing 11 by 2.

Match all open and Make sure that all opening parenthesis have been closed with
closing parentheses corresponding closing parenthesis especially when creating nested
functions.

Use a colon to indicate Always use a colon (:) between the first cell and the last cell in the range to
a range refer to that range. For example, =SUM(A1:A5), not =SUM(A1 A5), which
would return a #NULL! Error.

Enter all required Make ensure that all required arguments of a function are mentioned at
arguments right place. Functions will not work if any of the required arguments is
missed. Also, make sure that you have not entered too many arguments.

Enter the correct type Some functions, such as SUM, require numerical arguments. Other
of arguments functions may require text data as argument. Text arguments must be
enclosed within “” (inverted commas) otherwise function will return an
error.

Nest no more than 64 You can enter, or nest, no more than 64 levels of functions within a
functions function.

Enclose other sheet If a formula refers to values or cells on other worksheets or workbooks,
names in single and the name of the other workbook or worksheet contains spaces or
quotation marks and non-alphabetical characters, you must enclose its name within single
place an exclamation quotation marks ( ' ), like ='Sales Data'!D3.

RISE Premier School of Accountancy Page 8

Hands on Course - Ms. Office by Tahir Aziz Ms. Excel: Formulas and Functions

mark (!) after a For example, to return the value from cell D3 in a worksheet named
worksheet name Sheet2 in the same workbook, use this formula: ='Sales Data'!D3.

Include the path to Make sure that each external reference contains a workbook name and
external workbooks the path to the workbook.

For example: =SUM('C:\My Documents\[Q2

Operations.xlsx]Sales'!A1:A20). This formula returns the sum of range A1
to A20 of sheet named Sales in workbook named Operations.

Enter numbers without Do not format numbers when you enter them in formulas. For example, if
formatting the value that you want to enter is $1,000, enter 1000 in the formula. If
you enter a comma as part of a number, Excel treats it as a separator
character.

3 USEFUL FUNCTIONS FOR ACCOUNTANTS

Excel contain many built-in functions all of which may not be relevant to accountants. Some important
functions which the accountant may need to use at workplace are described here.

Compatibility functions

These functions were part of older versions of Excel (2007 or earlier) and are replaced by new functions
in later versions (2010 onward). These functions are available in newer versions of Excel for
compatibility with workbooks which were created in earlier versions.

Function name and description Function syntax

PERCENTILE PERCENTILE(array,k)
Returns the K'th percentile of Array (Required): The range of data.
values in a given range, where K K (Required). The number in the range 0 - 1, inclusive
is in the range 0 - 1 (inclusive). representing the required percentile. E.g to identify 20th
(Replaced by PERCENTILE.INC percentile use 0.2 or 20% as value of K.
function in Excel 2010 and later)
QUARTILE QUARTILE(array,quart)
Returns the quartile of a data Array (Required). The range of numeric values for which you
set. want the quartile value.
(Replaced by QUARTILE.INC Quart (Required): A number (0 to 4) to indicate which value to
function in Excel 2010 and later) return.
0 Minimum value,
1 First quartile (25th percentile)
2 Median value (50th percentile)
3 Third quartile (75th percentile)
4 Maximum value
RANK RANK(number,ref,[order])
Returns the rank of a number in Number (Required): The number whose rank you want to find.
a list of numbers Ref (Required): An array of, or a reference to, a list of numbers.
(Replaced by RANK.EQ function Nonnumeric values in ref are ignored.
in Excel 2010 and later) Order (Optional): A number specifying how to rank number.
If order is 0 (zero) or omitted, Microsoft Excel ranks number as if
ref were a list sorted in descending order.
If order is any nonzero value, Microsoft Excel ranks number as if
ref were a list sorted in ascending order.

RISE Premier School of Accountancy Page 9

Hands on Course - Ms. Office by Tahir Aziz Ms. Excel: Formulas and Functions

STDEV STDEV(number1,[number2],...)
Estimates standard deviation Number1 (Required): The first number argument corresponding
based on sample data using to a sample of a population.
following formula. Number2, ... (Optional): Number arguments 2 to 255
corresponding to a sample of a population. You can also use a
single array or a reference to an array instead of arguments
separated by commas.
(Replaced by STDEV.S function
in Excel 2010 and later)

Illustration - Compatibility functions

Date and time functions

Function name and description Function syntax

TODAY TODAY()
Returns the current date of your The TODAY function syntax has no arguments.
system. It updates the result
when worksheet is calculated
(or pressing F9 key).
NOW NOW()
Returns the current date and The NOW function syntax has no arguments.
time of your system. It updates
the result when worksheet is
calculated (or pressing F9 key).
WEEKDAY WEEKDAY(serial_number,[return_type])
Returns the day of the week Serial_number (Required): A sequential number that represents
corresponding to a date. The the date. Date must be entered in appropriate format. Problems
day is given as an integer, can occur if dates are entered as text.
ranging from 1 (Sunday) to 7 Return_type (Optional): A number that determines the type of
(Saturday), by default. return value.
1 or omitted: Numbers 1 (Sunday) through 7 (Saturday).
2: Numbers 1 (Monday) through 7 (Sunday).
3: Numbers 0 (Monday) through 6 (Sunday).

RISE Premier School of Accountancy Page 10

Hands on Course - Ms. Office by Tahir Aziz Ms. Excel: Formulas and Functions

11: Numbers 1 (Monday) through 7 (Sunday).

12: Numbers 1 (Tuesday) through 7 (Monday).
13: Numbers 1 (Wednesday) through 7 (Tuesday).
14: Numbers 1 (Thursday) through 7 (Wednesday).
15: Numbers 1 (Friday) through 7 (Thursday).
16: Numbers 1 (Saturday) through 7 (Friday).
17: Numbers 1 (Sunday) through 7 (Saturday).
WEEKNUM WEEKNUM(serial_number,[return_type])
Returns the week number of a Serial_number (Required): A date within the week.
specific date in the year. Return_type (Optional): A number that determines on which day
the week begins. The default is 1.

Illustration - Date and time functions

Statistical functions

Function name and description Function syntax

AVERAGE AVERAGE(number1, [number2], ...)
Returns the average (arithmetic Number1 (Required): The first number, cell reference, or range
mean) of the arguments. for which you want the average.
Number2, ... (Optional): Additional numbers, cell references or
ranges for which you want the average, up to a maximum of 255.
COUNT COUNT(value1, [value2], ...)
Counts the number of cells that value1 (Required): The first item, cell reference, or range within
contain numbers in given range which you want to count numbers.
and list of values entered as value2, ... (Optional): Up to 255 additional items, cell references,
arguments. Date and time or ranges within which you want to count numbers.
values are also considered
numbers.
COUNTA COUNTA(value1, [value2], ...)
Counts the number of cells in a value1 (Required): The first argument representing the values
range that are not empty. that you want to count.

RISE Premier School of Accountancy Page 11

Hands on Course - Ms. Office by Tahir Aziz Ms. Excel: Formulas and Functions

value2, ... (Optional): Additional arguments representing the

values that you want to count, up to a maximum of 255
arguments.
COUNTIF COUNTIF(range,criteria)
Counts the number of cells range (Required): The group of cells you want to count.
within a range that meet the criteria (Required): A number, text, cell reference or expression
given criteria. This function is (logical test) that determines which cells will be counted.
not case-sensitive.
MAX MAX(number1, [number2], ...)
Returns the largest value in a list
number1, number2, ...: Number1 is required, subsequent
of arguments. numbers are optional. These are 1 to 255 numbers for which you
want to find the maximum value. These arguments can either be
numbers, cell or range references.
MIN MIN(number1, [number2], ...)
Returns the minimum value in a number1, number2, ...: Number1 is required, subsequent
list of arguments. numbers are optional. These are 1 to 255 numbers for which you
want to find the minimum value. These arguments can either be
numbers, cell or range references.
MEDIAN MEDIAN(number1, [number2], ...)
Returns the median, the number number1, number2, ...: Number1 is required, subsequent
in the middle of a set of numbers are optional. These are 1 to 255 numbers for which you
arranged numbers. In case of want to find the median value. These arguments can either be
even numbers, it returns the numbers, cell or range references.
average of middle two numbers.

Illustration - Statistical functions

Logical functions

Function name and description Function syntax

AND AND(logical1,[logical2],…)
Returns TRUE if all its arguments logical1 (Required): The first condition that you want to test that
evaluate to TRUE, and returns can evaluate to either TRUE or FALSE.
FALSE if one or more arguments logical2,... (Optional): Additional conditions that you want to test
evaluate to FALSE. that can evaluate to either TRUE or FALSE, up to a maximum of
255 conditions.

RISE Premier School of Accountancy Page 12

Hands on Course - Ms. Office by Tahir Aziz Ms. Excel: Formulas and Functions

OR OR(logical1, [logical2], ...)

Returns TRUE if any of its logical1 (Required): The first condition that you want to test that
arguments evaluate to TRUE, can evaluate to either TRUE or FALSE.
and returns FALSE if all of its logical2,... (Optional): Additional conditions that you want to test
arguments evaluate to FALSE. that can evaluate to either TRUE or FALSE, up to a maximum of
255 conditions.
IF IF(logical_test, value_if_true, [value_if_false])
Return one value if given logical_test (Required): The condition you want to test.
condition (logical test) is true value_if_true (Required): The value that you want to be returned
and another value if it's false. if the result of logical_test is TRUE.
value_if_false (Optional): The value that you want to be returned
if the result of logical_test is FALSE.
NOT NOT(logical)
Reverses the value of its logical (Required): A value or expression that can be evaluated to
argument. If logical test is TRUE or FALSE.
FALSE, it returns TRUE; if logical
test is TRUE, it returns FALSE.
IFERROR IFERROR(value, value_if_error)
Returns a value you specify if a Value (Required): The argument that is checked for an error.
formula evaluates to an error; value_if_error (Required): The value to return if the formula
otherwise, returns the result of evaluates to an error. The following error types are evaluated:
the formula. #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

Illustration 1 - Logical functions

Illustration 2 - Logical functions

RISE Premier School of Accountancy Page 13

Hands on Course - Ms. Office by Tahir Aziz Ms. Excel: Formulas and Functions

Lookup and reference functions

Function name and description Function syntax

VLOOKUP VLOOKUP (lookup_value, table_array, col_index_num,
Looks up a given value in the [range_lookup])
first column of a data table, and lookup_value (Required): The value you want to look up (search)
returns the corresponding value in the first column of data table.
from another specified column table_array (Required): The data table, containing the lookup
in same row. value in the left-hand column and the return value in another
column.
col_index_num (Required): The column number (starting with 1
for the left-most column of table_array) that contains the return
value.
range_lookup (Optional): A logical argument, that describes what
the function should return in the event that it does not find an
exact match to the lookup_value. range_lookup can be set to
TRUE or FALSE, meaning:
TRUE or 1: If the function cannot find an exact match to the
supplied lookup_value, it should use the closest match below the
supplied value.
Note: if this option is used, the left-hand column of the
table_array must be in ascending order.
FALSE or 0: If the function cannot find an exact match to the
supplied lookup_value, it should return an error.
HLOOKUP HLOOKUP(lookup_value, table_array, row_index_num,
Looks up a given value in the top [range_lookup])
row of a data table, and returns lookup_value (Required): The value to be found in the first row of
the corresponding value from the data table.
another specified row in same table_array (Required): The data table, containing the lookup
column. value in the first row and the return value in another row.
Row_index_num (Required): The row number in table_array
from which the matching value will be returned.
range_lookup (Optional): same as described earlier.

Illustration 1 - Lookup and reference functions

RISE Premier School of Accountancy Page 14

Hands on Course - Ms. Office by Tahir Aziz Ms. Excel: Formulas and Functions

Illustration 2 - Lookup and reference functions

Math and trigonometry functions

Function name and description Function syntax

ABS ABS(number)
Returns the absolute value of a number (Required): The real number of which you want the
number i-e the number without absolute value.
its sign.
CEILING CEILING(number, significance)
Rounds a number up (away number (Required): The value you want to round.
from zero) to the nearest significance (Required): The multiple to which you want to round.
multiple of significance.
If number is an exact multiple of
significance, no rounding occurs.
FLOOR FLOOR(number, significance)
Rounds number down, toward number (Required): The numeric value you want to round.
zero, to the nearest multiple of significance (Required): The multiple to which you want to round.
significance.
INT INT(number)
Truncates a number down to number (Required): The real number you want to round down to
the nearest integer i-e removes an integer.
the decimal part of number.
MOD MOD(number, divisor)
Returns the remainder after number (Required): The number for which you want to find the
number is divided by divisor. remainder.
The result has the same sign as divisor (Required): The number by which you want to divide
divisor. number.
ROUND ROUND(number, num_digits)
number (Required): The number that you want to round.

RISE Premier School of Accountancy Page 15

Hands on Course - Ms. Office by Tahir Aziz Ms. Excel: Formulas and Functions

Rounds a number to a specified num_digits (Required): The number of digits to which you want to
number of digits. round the number argument.
SUBTOTAL SUBTOTAL(function_num,ref1,[ref2],...)
Returns a subtotal in a list or function_num (Required): The number 1-11 or 101-111 that
database. specifies the function to use for the subtotal. 1-11 includes
manually-hidden rows, while 101-111 excludes them; filtered-out
cells are always excluded.
Function_num Function_num Function
(includes hidden values) (ignores hidden values)
1 101 AVERAGE
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
6 106 PRODUCT
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP
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.
Note: The SUBTOTAL function is designed for columns of data, or
vertical ranges. It is not designed for rows of data, or horizontal
ranges.
SUM SUM(number1,[number2],…)
Adds its arguments number1 (required) is the first number you want to add. The
number can be like 5, a cell reference like A1, or a cell range like
A1:A5.
number2 (optional) is the second number you want to add. You
can specify up to 255 numbers in this way.
SUMIF SUMIF(range, criteria, [sum_range])
Returns the sum of values in a range (required): The range of cells that you want evaluated by
range that meet the specified criteria.
criteria. criteria (Required): The criteria in the form of a number, text,
expression, a cell reference, or a function that defines which cells
will be added. Text and expression must be mentioned in “”.
sum_range (Optional): The actual cells to add, if you want to add
cells other than those specified in the range argument. If the
sum_range argument is omitted, Excel adds the cells that are
specified in the range argument (the same cells to which the
criteria is applied). Sum_range should be the same size and shape
as range.
TRUNC TRUNC(number, [num_digits])
Truncates a number to an number (Required): The number you want to truncate.
integer by removing the num_digits (Optional): A number specifying the digits to keep.
fractional part of the number. The default value for num_digits is 0 (zero).

Illustration 1 - Math and trigonometry functions

RISE Premier School of Accountancy Page 16

Hands on Course - Ms. Office by Tahir Aziz Ms. Excel: Formulas and Functions

Illustration 2 - Math and trigonometry functions

Text functions

Function name and description Function syntax

EXACT EXACT(text1, text2)
Compares two text strings and text1 (Required): The first text string.
returns TRUE if they are exactly text2 (Required): The second text string.
the same, FALSE otherwise.
EXACT is case-sensitive but
ignores formatting differences.
LEFT LEFT(text, [num_chars])
Returns the first character or text (Required): The text string that contains the characters you
characters in a text string, based want to extract.
on the number of characters num_chars (Optional): Specifies the number of characters you
you specify. want to extract.
RIGHT RIGHT(text,[num_chars])

RISE Premier School of Accountancy Page 17

Hands on Course - Ms. Office by Tahir Aziz Ms. Excel: Formulas and Functions

Returns the last character or text (Required): The text string containing the characters you
characters in a text string, based want to extract.
on the number of characters num_chars (Optional) Specifies the number of characters you
you specify. want to extract.
MID MID(text, start_num, num_chars)
Returns a specific number of text (Required): The text string containing the characters you
characters from a text string, want to extract.
starting at the position you start_num (Required): The position of the first character you
specify, based on the number of want to extract in text. The first character in text has start_num 1,
characters you specify. and so on.
num_chars (Required): Specifies the number of characters you
want to return from text.
LOWER LOWER(text)
Converts all uppercase letters in text (Required): The text you want to convert to lowercase.
a text string to lowercase. LOWER does not change characters in text that are not letters.
UPPER UPPER(text)
Converts text to uppercase. text (Required): The text you want converted to uppercase. Text
can be a reference or text string.
PROPER PROPER(text)
Capitalizes the first letter in text (Required): Text enclosed in quotation marks, a formula that
each word of a text value. returns text, or a reference to a cell containing the text you want
Letters following numbers or to partially capitalize.
other punctuation marks are
converted to upper case.
LEN LEN(text)
Returns the number of text (Required): The text whose length you want to find.
characters in a text string.
Spaces, commas etc. count as
characters.
TRIM TRIM(text)
Removes all spaces from text text (Required): The text from which you want spaces removed.
except for single spaces
between words.
TEXT TEXT(value, format_text)
This function lets you change value (Required): A numeric value that you want to be converted
the way a number appears by into text in specific format.
applying formatting to it with format_text (Required): A text string that defines the formatting
format codes. that you want to be applied to the given value.

Illustration - Text functions

RISE Premier School of Accountancy Page 18

Hands on Course - Ms. Office by Tahir Aziz Ms. Excel: Formulas and Functions

Information functions

Each of these functions, collectively referred to as the IS functions, checks the specified value and
returns TRUE or FALSE depending on the outcome.

Function Returns TRUE if

ISBLANK(value) Value refers to an empty cell.
ISERR(value) Value refers to any error value except #N/A.
ISERROR(value) Value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!,
#NAME?, or #NULL!).
ISLOGICAL(value) Value refers to a logical value.
ISNA(value) Value refers to the #N/A error value.
ISNONTEXT(value) Value refers to any item that is not text. (Note that this function returns TRUE
if the value refers to a blank cell.)
ISNUMBER(value) Value refers to a number.
ISREF(value) Value refers to a pure reference.
ISTEXT(value) Value refers to text.

Illustration - Information functions

RISE Premier School of Accountancy Page 19

Hands on Course - Ms. Office by Tahir Aziz Ms. Excel: Formulas and Functions

Financial functions

Excel has a variety of functions to perform different financial calculations. Some important functions
are described below.

Function Description
ACCRINT Returns the accrued interest for a security that pays periodic interest.
ACCRINTM Returns the accrued interest for a security that pays interest at maturity.
COUPDAYS Returns the number of days in the coupon period that contains the settlement date.
CUMPRINC Returns the cumulative principal paid on a loan between two periods.
EFFECT Returns the effective annual interest rate.
IRR Returns the internal rate of return for a series of cash flows.
NPV Returns the net present value of an investment based on a series of periodic cash
flows and a discount rate.
PMT Calculates the payment for a loan based on constant payments and a constant
interest rate.
PV Calculates the present value of a loan or an investment, based on a constant interest
rate.
SLN Returns the straight-line depreciation of an asset for one period.
RATE Returns the interest rate per period of an annuity.
YIELD Returns the yield on a security that pays periodic interest.

Illustration - Financial functions

RISE Premier School of Accountancy Page 20