Excel Training Manual

Training Workshop by Sciffer Technologies

on
Advanced Microsoft Excel

Workshop conducted by:

Karan Kabra
Vinay Chhawchharia
Karan Vasani

FUNCTIONS IN EXCEL:
STRING FUNCTIONS
LOGICAL FUNCTIONS
NUMERIC AND STATISTICAL FUNCTIONS
REFERENCE AND LOOKUP FUNCTIONS
MISCELLANEOUS FUNCTIONS
STRING OR TEXT FUNCTIONS:
Trim: This is a unique function in Excel which removes redundant
blank spaces within the text. It discards out multiple spaces within
the texts and makes it regularly spaced. If any unnecessary
spaces are present at extreme ends, then it scrubs it out to make
your text look suitably organized.
Syntax: TRIM (text)
Text involves any type of string unevenly spaced.
For instance,

CONCATENATE: This function is used to merge two or more

than two text strings into a single string. This facility is limited for
30 text items only.
Syntax: CONCATENATE (text1, text2, ..)
The text strings which are to be amalgamated together are typed
within the brackets.
For instance,

LEFT: This function is used to extract specified number of

characters from left hand side a given text string.
Syntax: LEFT (text, num_chars)
Text is the string from which few characters are extracted from left.
Num_chars is the number of characters from left which are separated
out from the text. If they are not mentioned, then Left function will
automatically consider it as 1. Num_chars can be numbers greater
than or equal to 0. If they exceed the length of the string, then it
returns all the characters of the string.
For example,

MID: This function fetches specified number of string characters

anywhere from within the entire text string.
Mid function is favorable when a user wishes to extract particular
length of text from a lengthy string.
Syntax: MID (text, start_num, num_chars)
Text denotes the string from which you wish to pick some characters.
One can give appropriate cell location fro reference of the text.
Start_num indicates the position from where it will commence
selecting the characters. It is defined by a number greater than or
equal to 0. If it exceeds the entire string length then a blank is
returned.
Num_chars is a number specified to ensure that only these many
characters are chosen from the text string. If they exceed the total

text length then all the characters following the start_num position
are returned. Here, num-chars cannot be ignored.
In the following illustration,

RIGHT: This function facilitates to fetch desired number of

characters from right hand side of a given text.
Syntax: RIGHT (text, num_chars)
Text is the string from which you can separate out desired number of
characters beginning from right.
Num_chars are denoted by a number greater than or equal to 0 to
indicate the limit for picking of characters. If it surpasses the length
of the string then it will return back all the characters of the string.
However, if it is omitted then by default it will accept its value as 1.
For example,

FIND: This function enables a user to search for a specific

character within a particular string. It returns back the location of
the character set for search, in form of a number defining its
position within the string.
Note: FIND is a case sensitive function. Wildcard characters cannot be
used in this function. If you do not wish to make your search case
sensitive and enable the use of wildcard characters as well, then you
can instead use SEARCH function.
Syntax: FIND (find_text, within_text, start_num)
Find_text denotes the text which you want to search.
It needs to be enclosed within double quotation marks.
It cannot contain any wildcard characters.
If find_text is not present in within_text, then it will prompt a
#VALUE! error.

If it comprises of an empty text, i.e., , then it will find first

character from the string defined as within_text.

Within_text implies the string where you want to search your text.
Start_num emphasizes on the position from which the search should
commence. Each character in the text string is allotted a number
position to address it.
#VALUE! Error is prompted if it is not a number greater than 0.
#VALUE! Error is prompted if its length goes beyond the string
length.
By declaring it, a user can initiate the search directly from some
character within the search string omitting certain characters
from search operation.
If you skip to enter this detail while typing the formula, then it
will automatically set its value as 1 enabling the search to begin
from first character of the search string.
Following example will help you infer the significance of this function;

In example 1, the position of the character E is fetched at location 1.

In example 2, within_text is defined as the word interesting with the
help of RIGHT() function. It is now remarkable to note that the first
character of this newly defined within_text is assigned the character
position as 1. Hence, e is searched at 4th position of the word
interesting.
In example 3, start_num is set at position 14 from where the search
begins and the upcoming location where the text e appears is
returned. Thus, it skips the earlier occurring e in the Text string and
the searched location is numbered according to its position in the
defined within_text.
In example 4, the search begins from first character position.

LEN: This function computes the number of characters present in

the string. It returns the value in the form of an integer. Blank
spaces and numerals are also regarded as characters.
Syntax: LEN (Text)
Text invokes the string which is referred fro computing the length of
the string.
For instance, you want to find the length of first word only from the
entire text. You can make use of LEFT() and FIND() function to define
text within the brackets.

In this illustration, we want to restrict the Text for calculating the

length to first word Excel only. For this purpose, we make use of
LEFT function. Within LEFT loop, to define num_chars, we use Find
function so that it stops at one position less to the position of
occurrence of first blank space ( ) in cell A2.
SEARCH: This function is used to find desired character within a
text string and return the position of the searched character.
Note: The basic difference between FIND and SEARCH function is
that SEARCH is a case sensitive function whereas FIND is not.
SEARCH function also accepts wildcard characters for search.
Syntax: SEARCH (find_text, within_text, start_num)
Find_text involves the text which you want to search.
Wildcard characters like question mark (?) and asterisk (*)
are also accepted in find_text. Question mark symbolizes a
single character and an asterisk epitomizes a series of
characters.
It needs to be specified enclosed within double quotation
marks.
If the find_text mentioned in the formula is not present in the
search string then #VALUE! error appears on the screen.
If an empty text, i.e., is given for search, then it will
involuntarily return the location 1 finding the first character of
the search string.
Within_text is the text string where your text is searched.

Start_num indicates the position from which the search initiates.

Start_num if skipped, then by default it accepts the value as 1.
If it surpasses the length of the search string then #VALUE!
Error gets impelled.
Start_num enables the search to begin form any desired position
in the string, skipping certain characters from undertaking any
search.
For example,

Examples 1 and 3 shows that SEARCH function is not case

sensitive. In examples 2 and 4, use of wildcard characters are
shown.
REPLACE: This function is used to replace any part of the text in
the text string with some other text. For this one has to specify
the number of characters and the position of characters that have
to be replaced in the old text.
Syntax: REPLACE (old_text, start_num, num_chars, new_text)
Old_text includes the text in which you desire to make amendments.
You can give the cell location containing the old text.
Start_num is a number that signifies the character position from where
you want to start replacing the characters.
Num_chars denotes the number of characters that you want to replace
from the old_text.
New_text involves the new characters which will replace the original
characters in the old_text. It is mandatory to express the new text
within double quotation marks. For example,

SUBSTITUTE: This function is used to substitute a part of text

by some new text. The advantage of using this function over
REPLACE function is that you do not need to specify the location of
the old character which you want to change, instead you have to just
specify which character you wish to alter.
Syntax: SUBSTITUTE (text, old_text, new_text, instance_num)
Text includes the text string in which you want to replace a character
with some new character.
Old_text is the character which you want to change.
New_text involves the character by which you want to substitute the
old text.
Instance_num is a number that defines which occurrence of the
old_text is to be replaced. If it is not specified then all the occurrences
of the old_text in the string gets replaced with the new_text.
For example,

REPT: For multiple occurrence of a text, we can use REPT function

which repeats a particular text for specified number of times and
prevents us from typing it over and over again.
Syntax: REPT (text, number_times)
Text includes the character which you want to repeat in the string. It
must be noted that the text should be enclosed within double quotation
marks.
Number_times affirms the number of times the text is to be repeated. It
is specified by a whole number.
If it is assigned a value as 0, then an empty text, i.e., is
returned.
#VALUE! Error is prompted if number_times exceeds 32767
characters.

If some floating value is inserted in place of an integer then it

gets truncated to an integral value.

EXACT: This function is used to compare two text strings. If they are
identical then it returns the value as TRUE and they are dislike then
FALSE value is returned.
Exact function is a case sensitive function.
While comparing two text strings, it ignores the formatting
style difference between the texts.
The mathematical operator double equal to(==) also
undertakes same operation as the EXACT function does.
Syntax: EXACT (text1, text2)
Text1 and Text2 are the two strings submitted for comparison.
CHAR: This function is used to return a character corresponding to
the code number entered within the brackets.
The characters vary with numbers from 1 to 255.
With different operating system, the character set also differs like:
Operating system
Character set
Macintosh
Macintosh character set.
Windows
ANSI character set.
Syntax: CHAR (number)
Number involves any number ranging from 1 to 255 each representing
some character which you wish to get back.
Character code 32 returns a blank space.

26

51

76

101

126

27

52

77

102

127

28

53

78

103

128

29

54

79

104

129

30

55

80

105

130

31

56

81

106

32

57

82

107

8
9
10
11






33
34
35
36

!
"
#
$

58
59
60
61

:
;
<
=

83
84
85
86

S
T
U
V

12
13
14
15
16
17
18
19
20
21
22
23
24
25
















37
38
39
40
41
42
43
44
45
46
47
48
49
50

%
&
'
(
)
*
+
,
.
/
0
1
2

62
63
64
65
66
67
68
69
70
71
72
73
74
75

>
?
@
A
B
C
D
E
F
G
H
I
J
K

87
88
89
90
91
92
93
94
95
96
97
98
99
100

W
X
Y
Z
[
\
]
^
_
`
a
b
c
d

151

176

201

226

251

152

177

202

227

252

153

178

203

228

253

154

179

204

229

254

155

180

205

230

255

131

156

181

206

231

132

157

182

207

232

108
109
110
111

l
m
n
o

133
134
135
136

158
159
160
161

183
184
185
186

208
209
210
211

233
234
235
236

112
113
114
115
116
117
118
119
120
121
122
123
124
125

p
q
r
s
t
u
v
w
x
y
z
{
|
}

137
138
139
140
141
142
143
144
145
146
147
148
149
150

162
163
164
165
166
167
168
169
170
171
172
173
174
175

187
188
189
190
191
192
193
194
195
196
197
198
199
200

212
213
214
215
216
217
218
219
220
221
222
223
224
225

237
238
239
240
241
242
243
244
245
246
247
248
249
250

For example, =CHAR(43) returns +

CLEAN: This function used to erase all non printable characters from
the text. Generally, certain undesirable characters get added within
the text when it is imported from an external source of applications
as they do not get printed in our operating system. To remove them
Clean function is used.
Syntax: CLEAN (text)
Text includes the data from which you want all redundant characters to
be deleted.
Clean function is capable of removing first 32 non-printing characters
from 7 bit ASCII code which is a subset of ASNI character set.

For example,

LOWER: This function allows a user to transform the text to

corresponding lower case characters.
Syntax: LOWER (text)
Text includes the string of characters in which all upper case letters
get transformed to lower case.
For example,

UPPER: This function converts all the lower case letters in the text to
upper case.
Syntax: UPPER (text)
Text includes the text string in which all letters are to be changed to
their corresponding upper case form.
For example,

PROPER: This function transforms the text into a standard format

where every new word begins with an upper case letter and all the
subsequent letters of the word is changed into lower case character.
Syntax: PROPER (text)
Text includes the text in which the letters of the word are not presented
in a regular format and which needs to be coordinated. You can type the
text in double quotes or can give cell reference of the cell containing the
text.
For instance,

LOOKUP AND REFERENCE FUNCTIONS:

COLUMN: This function returns the column number from the cell
location mentioned within the brackets.
Syntax: COLUMN (reference)
Reference is the location of a cell or a range of cells of which the column
number is returned.
If reference detail is skipped, then involuntarily it returns back the
column number of the cell in which this function is executed.
Multiple areas cannot be referenced.
Let us understand the following illustration:

COLUMNS: This function returns the number of columns used in the

reference cells or arrays.
Syntax: COLUMNS (array)
Array involves references to a range of cells or an array or array
formula (a formula is valid multiple cells.) To create an array, select a
range of cells and press F2 and then press ctrl +shift +enter or press
F9. Any array is defined in {} brace brackets.
For example,

ROW: To acquaint about the row number of a particular cell

location, Row function can be used.

Syntax: ROW (reference)

A cell or a range of cells are given as a reference.
If no reference is defined in the formula then by default it returns the
row number of the cell in which Row function is typed.
For example,

ROWS: This function notifies the number of rows comprised in a

particular range of cells or in some predefined array.
Syntax: ROWS (array)
Array involves references to a range of cells or an array or array
formula (a formula is valid multiple cells.) To create an array, select a
range of cells and press F2 and then press ctrl +shift +enter or press
F9. Any array is defined in {} brace brackets.
For example,

MATCH: This function gives back the equivalent position of an item

matching the details of that item in an ordered list.
Syntax: MATCH (lookup_value, lookup_array, match_type)
Lookup_value includes the details of an item which you want to receive
back from the database. It can be expressed in the form of a number,
text or logical value or even their cell references can be given. It is the
text that you want to search in the lookup_array.
Lookup_array comprises of an ordered list of items whose details are
equivalently compared to find exact match for lookup_value in the list.
Lookup_array comprises of an array or an array reference.
Match finds the value in the lookup_array according to any of the three
ways of match_type:1, 0, and 1.
If match_type is not mentioned then involuntarily Excel accepts its
value as 1.

If match_type is 0 and lookup_value is a text, then you can use

wildcard characters like question mark (?) and asterisk (*) in the
lookup_value.
If correct arrangement order of the lookup_array is not
maintained according to the match_type, then #N/A error appears
on the screen.

MATCH_TYPE
-1

DESCRIPTION
The smallest value
greater than or equal to
the lookup_value is
returned.
The first value that is
exactly identical to the
lookup_value is returned.
The greatest value less
than or equal to the
lookup_value is returned.

ARRANGEMENT OF
LOOKUP_ARRAY
Descending order

Any order

Ascending order

MATCH is not a case sensitive function. The basic difference between

MATCH and LOOKUP function is that MATCH returns only the position
of the lookup_value in the array whereas LOOKUP returns the value
itself.
INDEX: This function is used to return the text values or even
value of some cell references according to the location or reference
specified for searching that value.
INDEX function can return the value in two ways:

Array form

Reference form
Array form:
In this format of indexing, the value is returned from an array or range
of cells containing the data.
Syntax: INDEX (array, row_num, column_num)
Array comprises of an array constant or range of cells where you are
going to locate and return a value.

Row_num and column_num are the corresponding row and column

indexes of the defined array or table with the help of which the
location of the desired value is traced.
(To create an array constant over a range of cells, select the cell
reference in the formula and press F9 button. The created array will
now be enclosed within the brace brackets.)
Note:
1. If your array covers only one row or column, then row_num or
column_num need not be specified.
2. However, in case of multiple rows and columns containing
array, if either of the row_num or column_num is specified by
setting the value of anyone of them as 0, then it will return the
values of entire row or column whose defined index is non zero.
3. #REF! error gets prompted if the intersecting location due to
row_num and column_num does not lie within the array.

Reference form:
In this form of indexing, we can give multiple areas, i.e., more than
one set of range of cells for reference to return back the value. The
areas that are given for reference can comprise of non adjacent range
of cells over the worksheet. We will have to specify the area in which
our coveted value is present.
Syntax: INDEX (reference, row_num, column_num, area_num)
Reference encompasses multiple areas or single set of range of cells
from where the value is returned. Multiple areas defined in the
reference must be enclosed within the brackets.
The intersection of row_num and column_num helps INDEX
function to trace the value.
Area_num should be specified in case of multiple areas or else by
default it will accept its value as 1 enabling INDEX function to fetch
for a value from area 1.
Examples index and sum
-index and match (Vlookup)

This function has great applications while working on long

spreadsheets in Excel.
INDIRECT: This function can be used to give text strings that
appear to be as simple cell address for referencing so that the
contents of the referred cells get displayed.
Another noteworthy attribute of this function is that it enables a user
to refer to cells in same or different worksheets or even different
workbooks. However, it must be noted that if you are referring to
some cell in different workbook, then that workbook should be open,
or else #REF! error may get prompted.
The cell references used in some formula change automatically on
introducing a new column or row or even while deleting some
originally present row or column in the worksheet. But if you enter
these cell references as string arguments in the INDIRECT function,
then this automatic change of cell references due to addition or
deletion of rows or columns can be impeded.
Syntax: INDIRECT (ref_text, A1])
Ref_text is used to give reference and it can comprise of any one of
the following:

A cell that contains a reference (A1 or R1C1 style reference).

Cell address as text string.
Some defined name of the workbook.

A1 declares the type of reference used in the Ref_text through a

logical value.
A1- True or omitted, R1C1-False.
Example1:
Indirect function used to give cell references while calculating sum.
Example2:
Using indirect function to give cell references in different workbooks:
Example 3:
Referring to named range of cells.

This makes working in Excel more convenient as it broadens the scope

of retrieving all the data in one shot by just entering appropriate cell
references helping us to make our work more efficient and refined.
VLOOKUP: This function is an amazing tool for retrieving the data
from an extended table array. It searches the value in the left most
column of the table array and then returns the value by moving
across the same row according to the specified column index.
VLOOKUP stands for vertical lookup of the lookup_value in the
leftmost column. VLOOKUP is not a case sensitive function.
Syntax: VLOOKUP (lookup_value, table_array,
column_index_num, range_lookup)
Lookup_value is the value that you want to search in the left most
column of the table array which can be declared by defining the range
of cells containing this column. If the value you are searching in not
present in this range of cells then it will prompt a #N/A! error in
return.
Table_array is the range of cells containing all your table contents
from where you want to retrieve some value. These values can be
text, numbers, dates and logical values.
Column_index_num is a number that specifies the column number
from which this function will return a value. Any number greater than
or equal to 1 and less than the last column index in the table array can
be accepted or else an error is impelled.
Range_lookup defines the way in which the value should be returned.
It accepts only logical values like True or False.
True (or omitted) : This implies that if exact match of the value is not
found then it will return back the next largest value lesser than the
lookup_value present in the table array.
It should be noted that for True range_lookup type, the first column of
the table array where lookup_value is searched needs to be sorted in
ascending order or else it will return an incorrect value.
False: This range_lookup type fetches for the exact match in the table
array and also the first column of the table array is not bound for any
particular sorting arrangement. If exact match is not found then it
returns a #N/A! error.

Wildcard characters can be used if our range_lookup type is False and

lookup_value is some text.
If the lookup_value comprise of dates then it must be ensured that
they are not entered as text values in the table array to avoid any
mistakes. The data in the first column of the table array should not
contain any unnecessary spaces at the beginning or at the end of the
text which is undesirably cause the function to return wrong values.
HLOOKUP: This lookup function is searches for the value in the
topmost row of the table array and returns a value from the same
column as specified in the table array by means of the row index.
HLOOKUP stands for horizontal lookup of the lookup_value in the
topmost row of the table array. HLOOKUP is not a case sensitive
function.
Syntax: HLOOKUP (lookup_value, table_array, row_index_num,
range_lookup)
Lookup_value can be any text, numbers, or logical values which is
searched in the first row of the table array.
Table_array includes the range of cells in which all the data is present
and from which a value will be retrieved.
Table_array arrangement

LOGICAL FUNCTIONS:
IF: This function enables a user to undertake logical tests over
values or formulas and return values according to the conditions set
for TRUE and FALSE results.
Syntax: IF (logical_test, value_if_true, value_if_false)
Logical_test specifies the condition which you want to test logically for
drawing a clear result as True or False. We can make use of
comparison operators to define the condition for logical test.
Value_if_true is executed, if the result evaluates as True.
Value_if_false is implemented, if result evaluates as False.
NOTE: You can at maximum use 7 nested IF loops for conducting
comprehensive and simultaneous conditional test.

IF function has great significance in carrying out various operations

over the database, assessing the data step by step by subjecting it to
various conditional tests.
For example, two nested IF statements are used to get the Remark.

The second IF condition in the formula is the value_if_true statement

for first IF condition. IF function is analyzing the data through the
conditions and returning appropriate results.
AND: It is a Logical function that check the conditions and
evaluates TRUE if all the conditions are TRUE whereas it returns
FALSE if any of the condition evaluates FALSE. It returns either of
the two logical results- TRUE or FALSE depending upon the logical
test.
Syntax: AND (logical1, logical2, .)
Logical1, logical2, . are the arguments which are subjected to logical
tests. A user is restricted to give maximum 30 logical statements to
test.

The arguments can have logical values as TRUE or FALSE or
they can be reference to a range of cells or array returning the
value as TRUE or FALSE.

The text or values contained in the cell references that are
included as arguments are neglected. #VALUE! error gets
impelled if the reference or range of cells do not contain any
logical value.
For example,

OR: It is a logical function that returns the value as TRUE if any of

the arguments are TRUE and it returns the value as FALSE if all the
arguments evaluates FALSE.
Syntax: OR (logical1, logical2, )

Logical statements inserted in the formula can be logical values or the

texts evaluating logical values. Maximum 30 statements can be given
as arguments for testing the OR condition. These arguments can be
cell references returning logical answers- TRUE or FALSE. #VALUE!
error gets prompted if none of the arguments return logical values.
For example,

NUMERIC AND STATISTICAL FUNCTIONS:

AVERAGE: This statistical function is used to calculate the average
of the numbers.
Syntax: (Number1, number2, )
The arguments of this function can be an array of numbers or you can
also give cell references of those cells which will return numbers for
computing the average.
Using this function you can calculate an average of maximum 30
numbers.
Note:
1. If the range of cells given for reference in the formula contain
any string texts or blanks or any logical values then they are
overlooked and only the numbers contained in the given
reference are accepted as arguments to this function.
2. The text representation of the numbers and logical values given
through some cell reference is not accepted as arguments for
calculation. But if we directly type the text representation of
numbers or logical values in the argument list, then this function
returns the average value.
3. Though empty cells are not counted, cells containing the value
as 0 are considered during calculation.
4. Arguments that are error values or text that cannot be
translated into numbers cause errors.
Important: If you wish to include logical values and text
representation of numbers as well for calculating Average, then it is
advisable to use AVERAGEA function rather than AVERAGE function.
AVERAGA function counts these logical values and numbers in the form
of text while calculating average. It evaluates TRUE as 1 and FALSE as
0. Empty texts inserted in the argument list are also evaluated as 0.

This implies that even the blank cells included in the cell references as
arguments are taken into account while computing average.
The Syntax for AVERAGEA function is AVERAGEA (value1, value2,)
These values can be text numbers, numbers, logical values or even
empty texts.
COUNT: This function counts the number of numeric elements
present in a selected list or range of cells. It by default ignores the
blank spaces, texts and errors in that range.
Syntax: COUNT (value1, value2, )
The range of cells mentioned within the bracket includes a list of
numeric data that is accounted to return the count of elements present
in the list. Maximum 30 such values can be defined in this function.

If arguments of this function include any logical values or

number text, numbers or dates directly typed in the brackets,
then they are counted.
But the logical values or the text representation of numbers
present in the range of cells entered through array or cell
reference are not counted.

For instance,

COUNTA: This function is used to count the numeric as well as text

entries and logical values present in the data list submitted as a
range in the brackets.
The blank spaces are neglected.
Syntax: COUNTA (value1, value2, )
For this function, all the text and numeric items in the range of cells
are counted. Maximum 30 such values can be defined in this function.
These values can include even error values and empty texts () but
not empty cells.

For instance,

COUNTBLANK: This function can be used to ascertain the number

of empty cells present in a given range of cells.
Syntax: COUNTBLANK (range)
Range includes the list of data where the count of total number of
blank cells is to be accounted. Even the formulas returning empty text
() as result are counted.
For example,

COUNTIF: This function counts the number of cells that satisfies a

given condition within a defined range of cells.
Syntax: COUNTIF (range, criteria)
Range involves the cells containing all the data which is to be
accessed to determine the number of cells within that range
satisfying the given criteria.
Any criteria can be defined with help of comparison operators,
numbers, texts, expression or even wildcard characters can be utilized

for that purpose. It should be noted that the criteria including symbols
or characters must be enclosed within the double quotation marks.
Example1:

In examples 1 and 2, wildcard characters are used in the criteria. The

cells within the range satisfying the criteria are only counted.
Example 2:
While defining the criteria for COUNTIF function, we cannot give
multiple comparison conditions simultaneously for the cells to abide.
Following is an example suggesting method to apply multiple
conditions using COUNTIF function.

SUM: As the name suggests this function is used to calculate the

sum of numbers inserted as arguments. You can also give a certain
range of cells as reference for computing the sum of numbers in
those cells.
Syntax: SUM (number1, number2, )
The numbers, logical values and text representation of numbers
are accepted as arguments to calculate the sum.

If logical values and text representation of numbers are included

for calculating the sum through reference of array or cells then
an error gets prompted.
Maximum 30 numbers can be included as arguments in this
function.
For example,

SUMIF: This function undertakes addition operation only for those

cells which comply with the given condition.
Syntax: SUMIF (range, criteria, sum_range)
Range comprises of the cells in which data is to be analyzed by means
of some condition.
Criteria are the condition for which every cell in the range should be
tested. Use of wildcard characters in the criteria is permitted.
Sum_range includes the actual range of cells whose values are to be
added if the equivalent cells of the range have fulfilled the criteria on
the basis of which totaling would be done. From the defined
sum_range only those cells get added which fulfill the condition
expressed in the criteria.
For example,

Note: If the sum_range contains any texts, number represented as

text or logical values then it simply evaluates it as 0 while calculating
the total.

SUMPRODUCT: This function initially multiplies equivalent

numeric elements of at least two arrays and the products obtained
are further added to calculate the sum.
Syntax: SUMPRODUCT (array1, array2, .)
Maximum 30 arrays can be inserted as arguments to execute this
function. It is imperative to note that all the array elements of the
arrays mentioned in the brackets have uniform dimension or else
#VALUE! error is prompted.
If any texts, logical values are present as array element in the
specified arrays, then excel involuntarily evaluates it as 0.
For instance,

MAX: This function is used to find maximum of the numbers

present in the specified array.
Syntax: MAX (number1, number2, )
The set of values given for finding the maximum value among
them can be expressed as directly typed numbers or logical
values as arguments or as array or cell references comprising of
numeric data elements in them.
Date and time are also accepted as arguments since they
correspondingly return their serial values which can be easily
considered as numbers.
However, the text representation of numbers or logical values
inserted as array or cell references are not accepted as valid
entry for this function.
If no numbers are present in the arguments, then this function
returns maximum value as 0.
Important note:
Logical values and text representation of numbers can also be
validated as arguments to compute the greatest of all values by using
MAXA function.
The syntax for MAXA function is MAXA (value1, value2, )

All other features of this function are similar to the MAX function.
For example,
EXAMPLE regarding MAX and MAXA saved in google search (excel)
book) >sheet 1.
MIN: This function is used to find minimum of the values specified
in the array or cell references given as arguments.
Syntax: MIN ( number1, number2, ..)
The set of values given for finding the minimum value among
them can be expressed as directly typed numbers or logical
values as arguments or as array or cell references comprising of
numeric data elements in them.
Date and time are also accepted as arguments since they
correspondingly return their serial values which can be easily
considered as numbers.
However, the text representation of numbers or logical values
inserted as array or cell references are not accepted as valid
entry for this function.
If no numbers are present in the arguments, then this function
returns minimum value as 0.
Important note:
Logical values and text representation of numbers can also be
validated as arguments to compute the smallest of all values by using
MINA function.
The syntax for MINA function is MINA (value1, value2, )
All other features of this function are similar to the MIN function.
Example: Changes needed in max examples.
LARGE: With the help of this function we can find the kth largest
term in the specified range.
Syntax: LARGE (array, k)
Array includes the range of cells given as a reference to fetch the kth
largest term.
K denotes the position or rank of the value you want to retrieve.
For example,

SMALL: With the help of this function we can find the kth smallest
term in the specified range.
Syntax: SMALL (array, k)
Array includes the range of cells given as a reference to fetch the kth
smallest term.
K denotes the position or rank of the value you want to retrieve.
For example,

ABS: ABS stands for absolute. This function returns the absolute
value of a number, that is, it returns back the value of the number
overlooking the sign of that number.
Syntax: ABS (number)
Number can be any real number (positive or negative).
Even the text representation of number is accepted as an argument.
For example,

TRUNC: TRUNC stands for truncate. This function prunes the

number to an integer erasing the fractional part of the number.
Syntax: TRUNC (number, num_digits)
Number can be any real number whose integral value is to be
retrieved.

Num_digits is a digit specifying the truncation precision. If omitted,

then it assigns its value as 0.
Important note:
Though both TRUNC and INT functions return integral solution only,
INT function rounds up the given number to its nearest integer
whereas TRUNC eliminates the fractional part of the number and
returns the integer.
For example,

ROUND: This function is used to round a number to specified

decimal places.
Syntax: ROUND (number, num_digits)
Number can be any real number whose value you want to get rounded
to specific decimal places.
Num_digits declares the extent to which decimal places should be
displayed.
1. If num_digit is equal to 0, then the number is rounded to the
nearest integer.
2. If the num_digit is smaller than 0, then the number is rounded
to the left of the decimal point.
3. If the number is greater than 0, then the number is rounded up
to specified number of decimal places.
For example,

ROUNDUP: This function rounds up a number moving away from 0.

Syntax: ROUNDUP (number, num_digits)
Number is any real number that is you wish to round up.
Num_digits declares the extent to which decimal places should be
displayed.
ROUNDUP is similar to ROUND function with the only difference that it
always rounds a number up.
1. If num_digit is equal to 0, then the number is rounded to the
nearest integer.
2. If the num_digit is smaller than 0, then the number is rounded
to the left of the decimal point.
3. If the number is greater than 0, then the number is rounded up
to specified number of decimal places.
For example,

ROUNDDOWN: This function rounds down a number tending

towards 0.
Syntax: ROUNDDOWN (number, num_digits)
Number is any real number that is you wish to round up.
Num_digits declares the extent to which decimal places should be
displayed.
ROUNDDOWN is similar to ROUND function with the only difference
that it always rounds a number down.
4. If num_digit is equal to 0, then the number is rounded to the
nearest integer.
5. If the num_digit is smaller than 0, then the number is rounded
to the left of the decimal point.
6. If the number is greater than 0, then the number is rounded up
to specified number of decimal places.
For example,

MOD: When a number is divided by a divisor, the remainder left

after the division operation is recovered back through this function.
The remainder has the sign as that of divisor.
Syntax: MOD (number, divisor)
Number is the dividend which is divided by the divisor.
Divisor is the number by which a given number is divided. If it is given
a value as 0, then #DIV/0! Error is prompted.
MOD function can be interpreted as follows:
Remainder= Dividend- divisor* (Dividend/divisor).
For example,

FREQUENCY: This function enables a user to scrutinize a list of

data items on the basis of various intervals and retrieve a number
indicating the count of data items that are relevant to these
intervals. This function returns an array of numbers based on such
comparisons made for various intervals.
The significance of this function is that using this function you can
actually filter out the data from the data array summarized under
different categories.
Syntax: FREQUENCY (data_array, bin_array)
Data_array includes a list of data items which are subjected to
comparisons against different intervals for which you can give some
cell reference as well. If our array comprise of no values then this
function returns an array of ciphers.
Bin_array include an array or reference to distinct intervals among
which you wish to distribute the data items on the basis of
comparisons pertaining to these intervals. If bin_array is exclusive of
any values then it returns back the count of data items present in the
array.
Important note:
Any blank spaces or text values in the array are neglected.

The number of items in the bin_array is one less than that in the
array retrieved as a result. The extra item appearing in the
result returns the count of data items which lie in the range
above the last closing interval.
FREQUENCY function is entered as an array formula only after
defining the range of cells in which you want the final
distribution to appear. For this purpose, you can highlight
sufficient range of cells that can accommodate the result of all
the defined intervals.

Following illustration will help to infer how this function executes to

return appropriate result to various intervals:
Suppose you have a list of data items in which age of the staff
members are stated and you wish to distribute the members into
different age group categories. Using FREQUENCY function this can be
achieved by following the procedure as given below:
Step1: Select a range of cells in which the count of people belonging
to each category can be displayed. Ensure that you select one extra
cell so that the information about members belonging to the category
range above the last defined one also gets displayed.

Step2: Subsequently, start typing the formula as

=FREQUENCY(A7:A19,B7:B11). One must note that since this function
is an array formula, it should be expressed in the form of an array.
Do not press <Enter> immediately after typing the above formula or
else entire array result will not be retrieved. To convert the above
formula into an array formula, you must press <ctrl> + <shift> +
<enter>. With this the formula gets enclosed within the braces {}.
The distribution gets accomplished instantly for all the categories.

It is not mandatory to select the range of cells adjacent to the given

array only. The key point is that one should ensure that sufficient
range of cells is selected.
LCM: This function is used to calculate LCM of given set of
numbers. Least common multiple is the smallest positive integer
which is the common multiple of all the integers included as
arguments of this function.
Syntax: LCM (number1, number2, )
The numbers inserted as the arguments to the function should be
positive integers. If some floating value is inserted as its argument,
then the number gets truncated.
LCM of maximum 29 numbers can be calculated through this function.
Any text or logical value entered as argument returns #VALUE! error.
If negative numbers are inserted then #NUM! error gets prompted.
Note: If this function returns #VALUE! error, then it implies that the
Analysis tool Pak add-in check box needs to be activated to include
this function. Therefore, go to Tools menu. Click on Add-ins option and
activate the add-in for the enabling the function to run.
Example:

GCD: This function is used to calculate GCD of given set of

numbers. Greatest common divisor is the highest positive integer
which is the common factor of all the integers included as
arguments of this function.
Syntax: GCD (number1, number2, )
The numbers inserted as the arguments to the function should be
positive integers. If some floating value is inserted as its argument,
then the number gets truncated.
GCD of maximum 29 numbers can be calculated through this function.
Any text or logical value entered as argument returns #VALUE! error.
If negative numbers are inserted then #NUM! error gets prompted.
Note: If this function returns #VALUE! error, then it implies that the
Analysis tool Pak add-in check box needs to be activated to include
this function. Therefore, go to Tools menu. Click on Add-ins option and
activate the add-in for the enabling the function to run.
Example:

RANK: In a list of numeric data, each number is ranked according

to its value size and this rank position can be known through this
function.
Syntax: RANK (number, reference, order)
Number is the numeric data item present in a list whose rank is to be
identified. You can also give the cell location containing that number
instead of the number itself.
Reference involves the range of cells containing the list items in
which you want to know the rank position of a number. Any nonnumeric data in the reference is neglected.
Specifying order is optional.
Excel can accept any of the two order of ranking denoted by the
numbers 0 and 1.
0: It the default order of assigning ranks to the numbers. In this, the
numbers in the list are ranked in descending order of their value.

Any non-zero value: The numbers in the list are ranked according to
their value in ascending order.
Same numbers in a list are assigned identical rank. But due this,
the rank positions of the other numbers are influenced.
Suppose the number 3 appears thrice in a list sorted in ascending
order and it attains the rank position 5 in the list, then the
subsequent number in the list gets a rank position as 8. This implies
no number in the list acquires the rank position 6 and 7.

For example,

In examples 1and 2, ranking is done in descending order whereas in

illustration 3, ranking is done in ascending order.