Most Popular Excel Formulas and How to Use Them

3] =MIN

=MIN is a function used to calculate the minimum or smallest value of one or more numbers, cells, or ranges of cells. This function belongs to the category of Statistics, and is one of the useful functions for finding the lowest value in a data set.

The syntax of the =MIN function is as follows:

=MIN(number1,[number2],...)

Where:

• number1 is a number, cell, or range of cells that must be entered as the first argument.
• [number2],… is an optional additional number, cell, or range of cells, which can be entered up to 255 arguments.

Here is an example of using the =MIN function:

•  =MIN(2,3,4) returns the value 2, which is the minimum value of the three numbers entered as arguments.
• =MIN(A1,B1,C1,D1) returns the value 10, which is the minimum of the values in cells A1 through D1.
• =MIN(A1:D2) returns the value 10, which is the minimum value of the values in the cell range A1 through D2.
• =MIN(A1:A3,C1:C3) returns the value 10, which is the minimum value of the values in the cell ranges A1 through A3 and C1 through C3.
• =MIN(A1:D3,5) returns the value 5, which is the minimum value of the values in the cell range A1 through D3 and the number 5 entered as an additional argument.

4] =MAX

=MAX is a function used to calculate the maximum or greatest value of one or more numbers, cells, or ranges of cells. This function belongs to the category of Statistics, and is one of the useful functions for finding the highest value in a data set.

The syntax of the =MAX function is as follows:

=MAX(number1,[number2],...)

Where:

• number1 is a number, cell, or range of cells that must be entered as the first argument.
• [number2],… is an optional additional number, cell, or range of cells, which can be entered up to 255 arguments.

Here is an example of using the =MAX function:

•  =MAX(2,3,4) returns the value 4, which is the maximum value of the three numbers entered as arguments.
• =MAX(A1,B1,C1,D1) returns the value 40, which is the maximum of the values present in cells A1 through D1.
• =MAX(A1:D2) returns the value 80, which is the maximum value of the values in the cell range A1 through D2.
• =MAX(A1:A3,C1:C3) returns the value 110, which is the maximum value of the values in the range of cells A1 through A3 and C1 through C3.
• =MAX(A1:D3,5) returns the value 120, which is the maximum value of the values present in the cell range A1 through D3 and the number 5 entered as additional arguments.

5] =COUNT

=COUNT is a function used to count the number of cells that contain numbers, either entered directly or generated by other formulas. This function belongs to the category of Statistics, and is one of the useful functions for calculating the frequency or amount of numerical data in a data set.

The syntax of the =COUNT function is as follows:

=COUNT(value1,[value2],...)

Where:

• value1 is a number, cell, or range of cells that must be entered as the first argument.
• [value2],… is an optional additional number, cell, or range of cells, which can be entered up to 255 arguments.

Here is an example of using the =COUNT function:

•  =COUNT(2,3,4) returns the value 3, which is the number of cells that contain numbers entered as arguments.
• =COUNT(A1,B1,C1,D1) returns the value 4, which is the number of cells that contain the numbers present in cells A1 through D1.
• =COUNT(A1:D2) returns the value 8, which is the number of cells that contain numbers in the cell range A1 through D2.
• =COUNT(A1:A3,C1:C3) returns the value 6, which is the number of cells that contain numbers in the range of cells A1 through A3 and C1 through C3.
• =COUNT(A1:D3,5) returns the value 13, which is the number of cells containing numbers in the range of cells A1 through D3 and the number 5 entered as additional arguments.

6] =IF

=IF is a function used to test logical conditions and return values based on the result. This function belongs to the Logic category, and is one of the most flexible and useful functions in Excel.

The syntax of the =IF function is as follows:

=IF(logical_test,value_if_true,value_if_false)

Where:

• logical_test is an expression that returns a value of TRUE or FALSE, which usually involves comparison operators, such as =, <, >, <=, >=, or <>.
• value_if_true is the value that will be returned if logical_test is TRUE. This value can be a number, text, date, time, formula, or cell reference.
• value_if_false is the value that will be returned if logical_test is FALSE. This value can be a number, text, date, time, formula, or cell reference.

Here is an example of using the =IF function:

• =IF(2>3,”True”,”False”) returns the text “False”, which is the value returned if condition 2>3 is FALSE.
• =IF(A1>B1,A1,B1) returns the value 20, which is the value returned if condition A1>B1 is TRUE. If the condition is FALSE, then the returned value is B1.
• =IF(A1:D2>50,”Pass”,”Fail”) returns a range of cells that contains the text “Pass”or“Fail”, depending on the result of the comparison between each cell in the range A1:D2 with the number 50. If the cell is greater than 50, then the returned text is “Pass”. If not, then the returned text is “Failed”.
• =IF(A1:A3>C1:C3,”A”,”C”) returns a range of cells that contain the text “A”or“C”, depending on the result of a comparison between each cell in the range A1:A3 and the corresponding cells in the range C1:C3. If a cell in range A1:A3 is larger than a cell in range C1:C3, the returned text is “A”. If not, then the returned text is “C”.
• =IF(A1:D3>100,A1:D3-10,A1:D3+10) returns a range of cells containing values resulting from arithmetic operations that depend on the result of the comparison between each cell in the range A1:D3 with the number 100. If the cell is greater than 100, then the returned value is the cell minus 10. If not, then the returned value is that cell plus 10.

7] =VLOOKUP

=VLOOKUP is a function used to look up values in a table or range of cells based on specific criteria, and return the corresponding values from the specified column. This function belongs to the Search and Reference category, and is one of the most useful functions for combining data from different sources or for searching for specific information in a table.

The syntax of the =VLOOKUP function is as follows:

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Where:

• lookup_value is the value that you want to look up in a table or range of cells. This value can be a number, text, date, time, or cell reference.
• table_array is the table or range of cells that contains the data you want to search. This table or range of cells must have at least two columns, and the value you want to look up must be in the first column.
• col_index_num is the column number in the table or range of cells that contains the value that you want to return. This column number must be a positive number, starting at 1 for the first column, 2 for the second column, and so on.
• [range_lookup] is an optional argument that specifies whether you want to look up an exact value or one that is approximate. This value can be TRUE or FALSE, or 1 or 0. If you enter TRUE or 1, or leave these arguments blank, the =VLOOKUP function looks for values that come close to, that is, values less than or equal to lookup_value, assuming that the first column in the table or range of cells is already sorted ascendingly. If you enter FALSE or 0, then the =VLOOKUP function will look up the exact value, that is, the exact same value as lookup_value, regardless of the order of the first column in the table or range of cells.

Here is an example of using the =VLOOKUP function:

•  =VLOOKUP(“Mind “,A1:D5,2,FALSE) returns the value 70, which is the value returned from the second column in the table or cell range A1:D5, based on the “Bob”value searched in the first column, by using the exact value lookup.
• =VLOOKUP(“Cici”,A1:D5,3,TRUE) returns the text“Pass”, which is the value returned from the third column in the table or cell range A1:D5, based on the “Cici”value searched in the first column, by using an approximate value lookup.
• =VLOOKUP(A2,A1:D5,4,FALSE) returns the text“A001″, which is the value returned from the fourth column in the table or cell range A1:D5, based on the values in cell A2 that were searched in the first column, by using the exact value lookup.
• =VLOOKUP(45,A1:D5,1,TRUE) returns the text “Dedi”, which is the value returned from the first column in the table or cell range A1:D5, based on the value 45 searched in the first column, by using an approximate value lookup.