Home
Tips and tricks

50+ Complete Excel Formulas Functions and Examples

List of excel formulas that can be used to manage numbers or data to complete tasks or jobs more easily and quickly.
50+ Complete Excel Formulas Functions and Examples
50+ Complete Excel Formulas Functions and Examples



50 Complete Excel Formulas Functions and Examples - Microsoft Excel is the best number and data processing application that can be used for various administrative needs that are often used in the world of work.

Excel itself is a Microsoft Office application package that is installed with other office applications such as Word, PowerPoint, Access and Excel and others.

In this article, we will discuss all the excel formula lists that can be used to manage numbers or data to make it easier, more effective and efficient.

50+ List of Complete Excel Formulas Functions and Examples

As a data management spreadsheet application, Excel is equipped with various functions, features and excel formulas that can be used to manage numbers or data to complete tasks or jobs more easily and quickly.

Understanding excel formulas is very important because it is needed in almost all fields of work from accounting and finance, industry, statistics and research, and many other fields.

IF formula

IF(Logic Test,[Condition Meets],[Condition Does Not Meet])
The IF function is used to return or display a value if a condition or statement evaluates to TRUE, and displays another value if the condition or statement evaluates to FALSE.

The IF formula is an Excel function that is included in the category or group of logical formulas that are often used to perform certain logical tests on excel formulas.

Case Example:
We will look for pass not pass in the following data:



In the example image above, the excel formula used in cell F6 is: 
=IF(G6>150;"PASS";"FAILED")
If the value of F6 is greater than 150 then the if formula will produce the text PASS . Conversely, if the value of F6 is not more than 150 then the IF formula will produce a text value FAILED .

Double IF Formula

The function is almost the same as the IF function but it is taken from two conditions or can be stacked with several commands or what is called nested IF. The IF function can be used together with up to 64 IF functions. 
IF(Logic Test1;Condition Meets1;IF(Logic Test2;Condition Meets2;[Condition Not Meets2])
Case Example:
We will determine the resulting value in the following data:



In the example image above, the excel formula used in cell F6 is: 
=IF(G6>=90;"A";IF(G6>=80;"B";"C"))
So if the value of G6 is greater than or equal to 90 then the if formula will produce text A . Conversely, if the value of G6 is greater than or equal to 80 then the IF formula will produce a text value B , otherwise C .

AND formula

This AND formula has a function whose main function is to produce TRUE if all the arguments tested are TRUE and FALSE if all or one of the arguments are FALSE. 
AND(Logic1;[Logic2]; ...)
Case Example:
We will determine TRUE or FALSE in the following data:



In the example image above, the excel formula used is: 
=AND(D6>E6)
So if the value of D6 is greater than E6 then the AND formula will return TRUE . Conversely, if the value of D9 is written greater than E9 then the AND formula will produce a FALSE value , because the value of D9 is not more than E9.

Examples of AND and IF Cases:
We will combine the AND function in the IF formula to determine TRUE or FALSE values:



In the example image above, the excel formula used is: 
=IF(AND(D6>60;E6>60);"PASS";"NOT PASS")
So if the value of D6 is more than 60 and E6 is more than 60 it will produce a PASS value . Conversely, if it turns out that the value of D6 is less than 60 even though E6 is more than 60, it will still produce a value NOT PASS .

OR formula

The function returns TRUE if some of the arguments are TRUE and FALSE if all of the arguments are FALSE. So in terms of returning a logical value True or False, the OR function is the opposite of the AND function. 
OR(Logic1; [Logic2]; ...)

NOT formula

The function of the NOT formula is the opposite of the AND and OR formulas. its function is to reverse the TRUE value to FALSE and reverse the FALSE value to TRUE. 
NOT(Logic)
Case Example:
We will determine TRUE or FALSE in the following data:



In the example image above, the excel formula used is: 
=NOT(D6>E6)
So if the value of D6 is more than E6 then it will produce TRUE value . Otherwise it will return FALSE .

SUM formula

The SUM function is used to add up the data in the cell. Both data per cell, data in one range, or data in several ranges at once.

The thing to note when determining the sum using SUM is only the numeric value that is calculated. Empty cells, logical values ​​or text will be ignored. 
=SUM(number1,[number2],...)
Case Example:
We will determine the TOTAL VALUE in the following data:



In the example image above, the excel formula used is: 
=SUM(D6:E6)
The formula above adds up each cell value from D6:E6 , so the result is 176 .

AVERAGE formula

The AVERAGE formula functions to calculate the average number of a variable in a certain range of cells. 
=AVERAGE(value1; [value2]; ...)
Case Example:
We will determine the AVERAGE VALUE in the following data:



In the example image above, the excel formula used is: 
=AVERAGE(D6:F6)
The formula above determines the average value of the data cell variables D6:F6 . Where the average value produced is 78.8 .

COUNT formula

The COUNT formula is used to count the number of cells that contain numbers. So even if the cell contains any data or the cell data is empty, it will be ignored or not counted. 
=COUNT(value1; [value2]; ...)
Case Example:
We will count the number of cells filled with numeric data in the following data:



In the example image above, the excel formula used is: 
=COUNT(B6:H6)
The formula above calculates the number of cells filled with numeric data in data cells B6:H6 . Where the cell values ​​that contain numbers are in B6, D6, E6, F6, G6, the resulting number is 5 .

Example Case 2:
We will count the number of data blank cells in the following data:



In the example image above, the excel formula used is: 
=COUNTBLANK(B6:H6)
The formula above calculates the number of blank cells in data cells B6:H6 . Where the number of empty cells is 2 because cells E6 and D6 are empty.

COUNTA formula

The COUNTA formula in excel has almost the same function as COUNT, it's just that COUNTA functions to count the entire number of non-empty cells, both those containing numbers, text, information, etc. So a cell containing any data can be calculated using this formula. 
=COUNTA(value1; [value2]; ...)
Case Example:
We will count the number of cells filled with data in the following data:



In the example image above, the excel formula used is: 
=COUNTA(B6:H6)
The formula above calculates the number of cells filled with data in data cells B6:H6 . Where the number of cells containing data is 5 because cells E6 and D6 are empty.

COUNTIF formula

The function of the COUNTIF formula is to count the number of cells in a range with certain criteria or simply to count the same amount of data. This formula makes it very easy for you to sort data. 
=COUNTIF(Range; Criteria)
Case Example:
We will calculate the number of survey respondent data with student status in the following data:



In the example image above, the excel formula used is: 
=COUNTIF(D6:D10;"Student")
The formula above calculates the number of cells filled with student data in data cells D6:D10 .

Example Case 2:
We will calculate the number of student data with a certain value in the following data:



In the example image above, the excel formula used is: 
=COUNTIF(D6:D10;"80")
The formula above calculates the number of students with a value of 90 in the value data cells D6:D10 . Then the result is 4.

Example Case 3:
We will calculate the number of student data that has a value of more than 70 in the following data:

In the example image above, the excel formula used is: 
=COUNTIF(D6:D10;">70")
The formula above calculates the number of students who have grades greater than 70 in grade data cells D6:D10 . Then the result is 1.

Closing

So, those were 50 Complete Excel Formulas with Functions and Examples . Easy enough right? If this article is useful, you can share and recommend it to friends who need it, if you have problems you can ask via the comments column.

Post a Comment