Advance Excel Functions Presentation.pdf

Advance Excel functions

1. ADVANCED
EXCEL:
FUNCTIONS

2. 01
SUM Function
Adds values of the selected
cells in a spreadsheet
Syntax: =SUM(Number1,
Number2, …, Number255)
02
Shortcut for SUM
Function
Round Function
Reduces a given value to a specific
number of decimal points
Syntax: =ROUND(Number,
Num_digits)

●Number - Value to be
rounded
●Num_digits - No. of decimal
places to reduce

3. 03 04 IF Function
Tests to see if a certain condition in a
spreadsheet is true or false
Syntax: =IF(logical_test, value_if_true,
value_if_false)

●logical_test - Value that is to be
tested
●value _if_true - Value to be
displayed if logical_test is true
●value _if_false - Value to be
displayed if logical_test is false
Count Function
Adds up the cells in selected range
that contain numbers

Syntax: =COUNT(Range)
●Range - The group of cells the
function is to count

4. 05COUNTIF Function
Counts up number of cells in a
selected range that meets a specific
criteria
Syntax: =COUNTIF(Range, Criteria)
●Range - The group of cells the
function is to search
●Criteria - Determines whether
the cell is to be counted or not
06 COUNTIFS Function
Counts up number of times
data in a two or more ranges
of cell meets multiple criterion
Syntax: =COUNTIFS(criteria_range-1,
Criteria-1, Criteria_range-2, criteria-2)
●Criteria_Range - The group of
cells the function is to search
●Criteria - Determines whether the
cell is to be counted or not

5. 07 08SUMIF Function
Adds up the value in cells in a selected
range that meet a certain criteria.
Syntax: =SUMIF(Range, Criteria, Sum
Range)
●Range - The group of cells the
function is to search
●Criteria - Determines whether the
cell is to be summed or not
SUMIFS Function
Adds up data in selected cells when
specific criteria are met in two or more
ranges of data,
Syntax: =SUMIF(Sum_range,
Criteria_range1, Criteria1, Criteria_range2,
Criteria2)
●Sum_range - Range of cells to be
added
●Criteria_range - Range of cells the
function is to search
●Criteria - Determines whether a cell
in the Sum_range is to be summed or
not

6. 09
VLOOKUP Function
Finds specific information stored in a
vertical spreadsheet table.
Syntax: =VLOOKUP(lookup_value,
table_array, col_index_num,
range_lookup)
10
HLOOKUP Function
●Lookup_value - Value to find in the first
column of table_array
●Table_array - Range of data that
VLOOKUP searches to find information
●Col_index_num - No. of the column in th
table_array that contains the data to be
returned,
●Range_lookup - True (returns approx
value) or False (returns exact value)
Finds specific information stored in a
horizontal spreadsheet table.
Syntax: =HLOOKUP(lookup_value,
table_array, row_index_num,
range_lookup)
●Lookup_value - Value to find in the first
column of table_array
●Table_array - Range of data that
HLOOKUP searches to find information
●Row_index_num - No. of the row in th
table_array that contains the data to be
returned,
●Range_lookup - True (returns approx
value) or False (returns exact value)