**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.

## Be the first to comment on "MS EXCEL FUNCTIONS"