Function Definition:

              A function is a predefined formula that performs calculations using specific values in a particular order.

Types of functions:

              According to Microsoft support, worksheet functions are categorized by their functionality. The categorizations are,

• Compatibility Functions
• Cube Functions
• Date & Time Functions
• Engineering Functions
• Financial Functions
• Logical Functions
• Lookup and Reference Functions
• Math and Trigonometry Functions
• Statistical Functions
• Text Functions
• Web Functions

Important Functions in the perspective of Data Science and Analytics:

Examples of The functions will be based on the below table:

COUNT function:

            This function falls under statistical category. This is used Counts how many numbers are in the list of arguments.

Syntax: COUNT (value1, [value2], …)

Example: To get the count of Age column we have to use formula as

=COUNT (D3:D10)

Output will be 7.

COUNTA function:

            This function falls under statistical category. This is used to count the number of cells that are non-empty in the given range.

Syntax: COUNTA (value1, [value2], …)

Example: To get the count of non-empty cells in the entire table range we have to use formula as

=COUNTA (A2:F10)

so the output will be 53.

COUNTIF function:

            This function falls under statistical category. This is used to count the number of cells within a range that meet the given criteria.

Syntax: COUNTIF (range, criteria)

Example: To get the count of employee with age less than 35 we have to use formula as

=COUNTIF (D3:D10,”<35″)

so the output will be 2.

COUNTIFS function:

            This function falls under statistical category. This is used to count the number of cells within a range that meet the multiple criteria.

Syntax: COUNTIFS (criteria_range1, criteria1, ….)

Example: To get the count of employee with age less than 40 and experience greater than 10 we have to use formula as

=COUNTIFS (D3:D10,”<40″, F3:F10,”>10″)

so the output will be 1.

SUMIF function:

            This function falls under Math and trigonometry category. This is used to Add the cells specified by a given criteria.

Syntax: SUMIF (range, criteria, [sum range])

Example: To get the sum of salaries of the employee with age less than 35 we have to use formula as

=SUMIF (D3:D10,”<35″, E3:E10)

so the output will be 73000.

SUMIFS function:

            This function falls under Math and trigonometry category. This is used to Add the cells in a range that meet multiple criteria.

Syntax: SUMIFS (sum_range, criteria_range1, criteria1, …)

Example: To get the sum of salaries of the employee with age greater than 35 and experience greater than 15 we have to use formula as

=SUMIFS (E3:E10, F3:F10,”>15″, D3:D10,”>35″)

so the output will be 300000.

LEFT function:

            This function falls under Text category. This is used to return the leftmost characters from a text value.

Syntax: LEFT (text, [num_chars])

Example: To get the first two letters of employee name we have to use the formula as

=LEFT(B3,2)

The output will be Dh

RIGHT function:

            This function falls under Text category. This is used to return the rightmost characters from a text value.

Syntax: RIGHT (text, [num_chars])

Example: To get the last two letters of employee name we have to use the formula as

=RIGHT(B3,2).

The output will be ni.

MID function:

            This function falls under Text category. This is used to return a specific number of characters from a text string starting at the position you specify.

Syntax: MID (text, start_num, [num_chars])

Example: To get the two letters of employee name starting from 2^nd position we have to use the formula as

=MID(B3,2,2)

The output will be ho.

FIND function:

            This function falls under Text category. This is used to find one text value within another text value. (case-sensitive).

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

Example: To get the letter D in first employee name. We have to use the formula as

=FIND (“d”, B7).

The output will be 1.

SEARCH function:

            This function falls under Text category. This is used to find one text value within another text value. (non-case-sensitive)

Syntax: SEARCH (find_text, within_text, [start_num])

Example: To get the letter D in first employee name. We have to use the formula as

=SEARCH (“d”, B7)

The output will be 1.

DAYS function:

            This function falls under Date and time. This is used to return the number of days between two dates.

Syntax: DAYS (end_date, start_date)

Example: To get the number days between the joining date of first and last employees we have to use

=DAYS (C10, C3)

The output will be 6423.

NETWORKDAYS function:

            This function falls under Date and time. This is used to return the number of whole workdays between two dates.

Syntax: NETWORKDAYS (start_date, end_date, [Holidays])

Example: To get the number of whole working days between the joining date of first and last employees we have to use

= NETWORKDAYS (C3, C10).

The output will be 4588.

WORKDAY function:

            This function falls under Date and time. This is used to return the serial number of the date before or after a specified number of workdays.

Syntax: WORKDAYS (start_date, end_date, [Holidays])

Example: To get the serial number of the date. We have to use

= WORKDAY (C3, 5)

The output will be 37385.

AVERAGEIFS function:

            This function falls under statistical category. This is used to return the average (arithmetic mean) of all cells that meet multiple criteria.

Syntax: AVERAGEIF (average_range, criteria_range1, criteria1, ….)

Example: To get the average of salaries of the employee with age greater than 35 and experience greater than 15 we have to use formula as

=SUMIFS (E3:E10, F3:F10,”>15″, D3:D10,”>35″)

so the output will be 150000.

MAXIFS function:

            This function falls under statistical category. This is used to return the maximum value among cells specified by a given set of conditions or criteria.

Syntax: MAXIFS (max_range, criteria_range1, criteria1, ….)

Example: To get the maximum of salaries of the employee with age greater than 35 and experience greater than 15 we have to use formula as

=MAXIFS (E3:E10, F3:F10,”>15″, D3:D10,”>35″)

so the output will be 150000.

MINIFS function:

            This function falls under statistical category. This is used to return the minimum value among cells specified by a given set of conditions or criteria.

Syntax: MINIFS (min_range, criteria_range1, criteria1, ….)

Example: To get the minimum of salaries of the employee with age greater than 35 and experience greater than 15 we have to use formula as

=MINIFS (E3:E10, F3:F10,”>15″, D3:D10,”>35″)

so the output will be 45000.

VLOOKUP function:

            This function falls under Lookup and reference. This is used to Lookup in the first column of an array and moves across the row to return the value of a cell.

Syntax: VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Example: To get the experience of employee name associated with Dravid. We have to use formula as

=VLOOKUP (B7, B2:F10,5, FALSE)

The output will be 10.

HLOOKUP function:

            This function falls under Lookup and reference. This is used to Lookup in the top row of an array and returns the value of the indicated cell.

Syntax: HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup])

Example:

To get the experience of employee name associated with Dravid. We have to use formula as

=HLOOKUP (B7, B2:F10,5, FALSE)

The output will be 10.

IFERROR function:

            This function falls under Logical category. This is used to return a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula.

Syntax: IFERROR (value, value_if_error)

Example:

To return a value specified if its error then we have to use

=IFERROR(D10,0)

The output will be 26.

RANK function:

            This function falls under Compatibility category. This is used to return the rank of a number in a list of numbers.

Syntax: RANK (number, ref, [order])

Example: To get the rank on ascending order according to experience then we have to use

=RANK (F3, F3:F10, 1)

The output will be 8.

SUMPRODUCT function:

            This function falls under Math and trigonometry category. This is used to return the sum of the products of corresponding array component.

Syntax: SUMPRODUCT (array1, [array2], [array3], …)

Example: To get the sum of the product of experience and age column then we have to use

=SUMPRODUCT (F3:F10, D3:D10)

The output will be 2992.