Understanding the Different Types of Microsoft Excel Functions
This is perhaps the most commonly-used function in Excel. It lets you count the number of cells currently being used in a spreadsheet as well as the number of blank cells remaining in your spreadsheet’s data range. There are five types of Count Functions in Excel:
- Count for number of cells containing numbers in a data range.
- CountIF for counting cells based on a certain specification.
- CountA for counting labels.
- CountIFS for counting cells meeting various specifications.
- CountBLANK for counting blank cells.
This Excel function is used for testing whether certain conditions are true or false and helps in arriving at decisions in a spreadsheet. You can create nested IF Functions to make the functions more flexible, perform calculations, enter data or text, or to leave a cell blank when certain IF conditions are met.
Another useful Excel function is the DATE Function which you can use to add the current time or day of the week to specific cells in your spreadsheet. There are two types of the DATE functions:
- NOW for entering the current time and date.
- NETWORKDAYS function for finding the number of days that a project entails.
To arrive at a decision when working on data to be added to the different cells in your spreadsheet, you can use the Excel logical functions. You can either use the IF, OR or AND logical functions to evaluate the mathematical expression on a cell.
To perform mathematical operations on your spreadsheet, you can use Excel math functions such as the SUM function, AutoSum, and SumIF. In addition, you can also use the Math Functions to produce a random set of numbers. These functions include:
- RANDBETWEEN to produce numbers in between two numbers.
- ROUND to produce numbers based on specific decimal points.
- INT to round off numbers downwards to the next whole number.
- ROUNDDOWN to round off numbers to the next lowest number.
- ROUNDUP to round a number upwards to the next highest number.
- DEGREES to convert radians to degrees.
- PRODUCT to multiply numbers.
- QUOTIENT to divide numbers.
For managing and manipulating text data in your spreadsheet, you can use Excel text functions. There are various types of text functions as well.
- Concatenate joins two or more words or string of text together.
- REPLACE replaces unnecessary characters from a data entry.
- LEFT removes characters from the right side of a data entry.
- RIGHT removes characters from the left side of a data entry.
- MID removes characters from both sides of a data entry.
- TRIM removes extra spaces from a data text entry.
For complex mathematical calculations such as those used in Trigonometry, you can use the TRIG function. This function computes the sine, cosine, and tangent of angles in addition to converting angles in degrees to radians.
For a simpler way of finding specific entries in your spreadsheet, you can always use the Excel LookUp functions. These include:
- VLookUp Function for finding data stored in columns.
- HLookUp for finding data stored in rows.
- TRANSPOSE for transfering data from a row to a column or vice versa.
For complex data analysis in your spreadsheet, Excel statistical functions are very useful tools.
- AVERAGEIF will find the average of values in cell ranges within certain criteria.
- MAX will get the largest value in a given list of numbers.
- MIN will find the smallest value.
- AVERAGE will find the average.
- MEDIAN will find the middle value from a list of numbers.
- MODE will find the most frequently occurring value in a list of numbers.
- RANK can be used to rank the size of a number compared to other numbers.
- SMALL or LARGE can be used to find data based on its size relative to other numbers.
If you want to find out various information about data in a range of cells in your spreadsheet, you use Excel information functions. You can determine:
- Whether data in a certain cell is a number or not with the ISNUMBER functions.
- Whether a cell is empty or not with ISBLANK function.
- The type of data contained in a cell with the TYPE function.
- Information such as formatting, data type, location, and whether the cell has been protected or locked with the CELL function.