Bunat AlGhad Academy AS ICT Excel Functions

Mathematical Functions

Sum function (Adds up all the values in a

range)

Sum if (Adds all the values in a range that The syntax for SUMIF is:
meet specific criteria) =SUMIF(range, criteria, [sum_range])
Range: The range of cells to evaluate.
Citeria: The condition that defines which cells are
to be added.
Sum range :The actual cells to sum.

Sumifs(Adds values in a range based on The syntax for the SUMIFS function is:
multiple criteria) =SUMIFS(sum_range, criteria_range1,
criteria1, ...)
Sum_range :The range of cells containing the
values you want to add.
Criteria_range :The range of cells containing the
records you want to check against the criteria.
Criteria: The criteria to determine whether the
value is added or not.
Always the range and sum_range MUST be absolute reference
Round (Round a number to a specified The syntax for the ROUND function is:
number of digits) =ROUND(number,num_digits)
Roundup (Round a number up to a The syntax for the ROUNDUP function is:
specified number of digits) =ROUNDUP(number,num_digits)

1
 Bunat AlGhad Academy AS ICT Excel Functions

Rounddown(Round a number down to a The syntax for the ROUNDDOWN function is:
specified number of digits) =ROUNDDOWN(number,num_digits)
Statistical functions
Count (Counts all the numeric values in a The syntax for the Count function is:
range) =COUNT(value1, [value2], ...)
CountA (Counts all non-empty cells in a The syntax for the COUNTA function is:
range) =COUNTA(value1,value2,...)
Countblank(Counts all blank cells in a The syntax for the COUNTBLANK function is:
range) =COUNTBLANK(range)
Countif(Counts all the cells in a range that The syntax for COUNTIF is:
meet specific criteria) =COUNTIF(range, criteria)
Range : The range of cells to evaluate.
Criteria: The condition that defines which cells
are to be counted.
Countifs (Counts all the cells in a range that The syntax for the COUNTIFS function is:
meet multiple criteria) =COUNTIFS(criteria_range1, criteria1, ...)
Criteria_range: The range of cells you want to
look for items to count.
Criteria: The criteria to determine whether the
cell is counted or not.
Note: always range MUST be absolute reference
Average (Calculates the average number The syntax for the Average function is:
from a range of values) =AVERAGE(number1, [number2], ...)

2
 Bunat AlGhad Academy AS ICT Excel Functions

The AVERAGEA function returns =AVERAGEA (value1, [value2], ...)

the average(arithmetic mean) of a group of
supplied values. The key difference
between AVERAGEA and AVERAGE
is AVERAGEA will also evaluate the logical
values TRUE and FALSE, and numbers
represented as text when they appear in
cell references, whereas AVERAGE just
skips these values.
Averageif (Calculates the average of a The syntax for the AVERAGEIF function is:
range of values that meet specific criteria) =AVERAGEIF(range, criteria, [average_range])
Range: The range of cells you want to test.
Criteria: The criteria the records have to meet to
be included.
Average_range :The range of values to average.

SUBTOTAL (function that is used in place of SUBTOTAL syntax:

a number of others.) =SUBTOTAL (TYPE OF TOTAL, RANGE OF CELLS)
To insert subtotals, first sort the data with a TYPE OF TOTAL
primary sort on the field you wish to create
subtotals for.
If there are other subtotals within the
subtotal range, the subtotal values will be
ignored. The Subtotal function ignores rows
not included.
Function num is the number 1 to 11
(includes hidden values) or 101 to 111
(ignores hidden values) that specifies which
function to use in

RANGE OF CELLS
Always Range and average_range must be absolute reference
Max (Finds the maximum value in a range) The syntax for the Max function is:
=MAX(number1, [number2], ...)
Min (Finds the minimum value in a range) The syntax for the Min function is:
=MIN(number1, [number2], ...)
Mod( The Excel MOD function returns the For example,
remainder of two numbers after division) MOD(10,3) = 1. The result of MOD carries the
Mod(number,divisor) same sign as the divisor.
Asb(value) The Microsoft Excel ABS function returns the
absolute value of a number

3
 Bunat AlGhad Academy AS ICT Excel Functions

The Excel RANDBETWEEN function returns bottom - An integer representing the lower value
a random integer between given numbers. of the range.
RANDBETWEEN recalculates when a top - An integer representing the lower value of
worksheet is opened or changed. the range.=RANDBETWEEN (bottom, top)
String Functions
LEFT (displays a specified number of Syntax:
characters from the left hand side of a =LEFT(Text,Number of characters required)
piece of text)
RIGHT (displays a specified number of Syntax:
characters from the right hand side of a =RIGHT(Text,Number of characters required
piece of text)
MID ( returns the characters from the = MID ( Text , Start_num , Num_chars )
middle of a text string, given a starting Text - the string containing the desired
position and length ) data.
Start_num - specifies the starting
character from the left of the string to be
kept.
Num_chars - specifies the number of
characters to the right of the Start_num
to be retained.
SEARCH (Locates the position of a SEARCH( search_text, within_text, [start_num] )
(character within a string search_text - The character or text string
that you wish to search for.
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
FIND( find_text, within_text, [start_num] ) Find_text - the character or substring you want
to find.
Within_text - the text string to be searched
within. Usually it's supplied as a cell reference,
but you can also type the string directly in the
formula.
Start_num - an optional argument that specifies
from which character the search shall begin. If
omitted, the search starts from the 1st character
of the within_text string.
LEN (Returns the number of characters in a LEN(a string of text)
text string)
REPT The Excel REPT function repeats =REPT("x",5) returns "xxxxx".

4
 Bunat AlGhad Academy AS ICT Excel Functions

characters a given number of times.

CONCATENATE ( Joins separate pieces of CONCATENATE(text1, [text2], ...)

text into one item)
&( Joins separate pieces of text into one =text1&text2..&text4
item)
VALUE (converts a piece of text into actual =Value(text)
value)
TEXT (converts a value into text in a specific =TEXT(B14,format)
number format)

TRIM( Removes leading and trailing spaces . =TRIM(text)

from the text in the formula )

=CONVERT(" followed by the value. When =CONVERT(30.4,"C","F")

=CONVERT(65,"mi","km")
you enter the comma to move to the units
section of the function
Use TYPE to test the value in a particular cell =TYPE (value)
so that other functions that depend on the type Type Meaning
code
can perform as expected. The table below
shows the possible type codes returned from 1 Number

TYPE and the meaning of each: 2 Text

4 Logical value

16 Error value

Use the REPLACE function when you REPLACE (old_text, start_num, num_chars,
want to replace text based on its new_text)
location in a string.  old_text - The text to replace.
 start_num - The starting location in the
text to search.
 num_chars - The number of characters
to replace.
 new_text - The text to replace old_text
with.

SUBSTITUTE(text, old_text, new_text,

[instance_num])
The SUBSTITUTE function syntax has the
following arguments:
 Text Required. The text or the reference to
a cell containing text for which you want to
substitute characters.

5
 Bunat AlGhad Academy AS ICT Excel Functions

 Old_text Required. The text you want to

replace.
 New_text Required. The text you want to
replace old_text with.
 Instance_num Optional. Specifies which
occurrence of old_text you want to replace
with new_text. If you specify instance_num,
only that instance of old_text is replaced.
Otherwise, every occurrence of old_text in
text is changed to new_text.
Use the REPLACE function when you want
to replace text based on its location in a
string.
Use SEARCH to find the location of text to
replace it it's not known in advance.
Use SUBSTITUTE to replace text based on
content.
=UPPER(TEXT) The UPPER function syntax has the
following arguments:
 Text Required. The text you want
converted to uppercase. Text can be a
reference or text string.

=LOWER(TEXT) Converts all uppercase letters in a text string

to lowercase.

=PROPER(TEXT)  Capitalizes the first letter in a text string and

any other letters in text that follow any
character other than a letter. Converts all
other letters to lowercase letters
Lookup functions
Vlookup(Looks vertically down a list to Lookup value: The value to search for.
find a record and returns information Table array : The list of data to search for
related to that record). the value in. It looks for the value in the
=vlookup(lookup_value,table_array,col_in leftmost column.
dex_num,[range_lookup]) Col index num: The column number from
the left of the data to be returned.
range_lookup: Logical value that could be
true or false

Hlookup works in the same way as Lookup value: The value to search for.
Vlookup except that it searches for a Table array : The list of data to search for
value in the top row of a table and the value in. It looks for the value in the

6
 Bunat AlGhad Academy AS ICT Excel Functions

returns a value from a specified row. leftmost column.

=Hlookup(lookup_value,table_array,ro row index num: The row number from the
w_index_num,[range_lookup]) left of the data to be returned.
range_lookup: Logical value that could be
true or false.
The Excel INDEX function returns the =INDEX (array, row_num, [col_num],
value at a given position in a range or [area_num])
array. You can use index to retrieve
individual values or entire rows and
columns.
A number representing a position in lookup_value - The value to match in
lookup_array. lookup_array.
MATCH (lookup_value, lookup_array, lookup_array - A range of cells or an array
[match_type]) MATCH returns a reference.
position. To retrieve a value, see How match_type - [optional] 1 = exact or next
to use INDEX and MATCH. smallest (default), 0 = exact match, -1 =
exact or next largest.

Logical functions
If (Something is True, then do If( logicaltest, value if true, value if fasle)
something, otherwise do something
else)
And to determine if all conditions in a And(logical1, logical2,…)
test are TRUE.
Or Use the OR function, one of Or (logical1, logical2,…)
the logical functions, to determine if any
conditions in a test are TRUE
Iferror handels errors The value you 1. IFERROR (value, value_if_error)
specify for error condition
IsNA- is blank- is error returns true or Isnull(A2)
false according to the test
=ISTEXT (value) =ISTEXT (value)
A logical value (TRUE or FALSE)
The Excel ISTEXT function returns
TRUE when a cell contains a text, and
FALSE if not

Date and time functions

DATE ( this function creates a real date =date(year,month,day)
by using three normal numbers typed

7
 Bunat AlGhad Academy AS ICT Excel Functions

into separate cells)

The result is displayed in the dd/mm/yy

But using format cells it can be changed

Inter
Explanation =datedif(Firstdate,secondate,”interval”).
val Interval:
Y The number of complete years. "Y" The number of complete years in the
M The number of complete months. period.
D The number of days. "M" The number of complete months in the
period.
"D" The number of days in the period.
DATEDIF (This function calculates the
difference between two dates)

TODAY (returns the current date =today()

formatted as date)
DAY( Extracts the day from a complete =DAY(A2)
date)
MONTH(Extracts the month from a =MONTH(A2)
complete date)
YEAR(extracts the year number from a =YEAR(A2)
date)

WEEKDAY(serial_number, =WEEKDAY(A2)
[return_type])
Returns the day of the week
corresponding to a date. The day is
given as an integer, ranging from 1
(Sunday) to 7 (Saturday), by
default

Note: we can subtract two dates and it will give the interval in days.
To find the difference in months we find difference in years multiplied by 12 then add to
it the difference in months.
Date value (date_text) Use DATEVALUE to convert a date
represented by text to a serial number.

8
 Bunat AlGhad Academy AS ICT Excel Functions

YEARFRAC(start_date,end_date,basis) YEARFRAC(start_date,end_date,basis)
Calculates the fraction of the year Start_date is a date that represents
represented by the number of whole the start date.
days between two dates End_date is a date that represents
the end date.
Basis is the type of day count basis to
use.

=TIME(Hour,minute,second) =TIME(Hour,minute,second)

TIME(converts three separate numbers

to an actual time)
=hour(A2) =hour(A2)
HOUR(Show the hour of the day based
upon a time or a number)

=MINUTE(A2) =MINUTE(A2)
MINUTE(Show the minute based upon
a time or number)
=SECOND(A2)
SECOND(Show the second based upon
a time or number)
=now() =now()
NOW(Shows the current date and time)
 1 minute=60 seconds
 1hour=60 minute=(60*60)seconds
 Always add minutes with minutes, seconds with seconds
 Never use inhomogeneous time units
 To find a time interval subtract the start time from the finish time.