IF formula in Excel – Microsoft Excel is a type of Microsoft that is widely used by people in this world. Apart from Microsoft Word and Microsoft Power Point, Microsoft Excel is also very helpful for someone in completing their work.
There are many formulas that can help you in Microsoft Excel, one of which is the IF formula. Maybe ordinary people will be confused with this term. However, for people who have been in Microsoft Excel for a long time, they must be familiar with the formulas in it. There are still many people who don’t know about the use and types of this one formula.
See the following explanation about the type and use of this formula.
Definition of Microsoft Excel
Microsoft Excel or Microsoft Office Excel is a worksheet application program created and distributed by Microsoft Corporation that can run on Microsoft Windows and Mac OS. This application is part of Microsoft Office.
This application features calculations and graphing using Microsoft’s aggressive marketing strategy, making Microsoft Excel one of the most popular computer programs used on microcomputers to date. This program is the most widely used spreadsheet program by many parties, both on Windows-based PC platforms and Macintosh-based Mac OS platforms, since version 5.0 was published in 1993.
Excel offers many interface advantages compared to the spreadsheet programs that preceded it, but its essence is still the same as VisiCalc ( the first popular spreadsheet software ): Cells are arranged in rows and columns, and contain data or formulas with absolute or relative references. other cells.
Excel was the first spreadsheet program that allowed users to define how the spreadsheet they edited would look like: font, character attributes, and the appearance of each cell. Excel also offers intelligent cell recalculation, where only cells related to the cell will be updated (where other spreadsheet programs will recalculate the entire data or wait for a special command from the user). In addition, Excel also offers excellent graph processing features.
When it was first bundled into Microsoft Office in 1993, Microsoft also redesigned the interface used by Microsoft Word and Microsoft PowerPoint to match the appearance of Microsoft Excel, which at that time was the most popular spreadsheet application .
Following are some of the Microsoft Excel extensions:
*.xls, is the default Microsoft Excel format prior to Excel 12. It is still based on the BIFF format and can store VBA macros .
*.xlt, is a format for Microsoft Excel worksheet templates prior to Excel 12. Still based on the BIFF format and can store VBA macros .
*.XML, or what is known as an XML Spreadsheet . Not capable of hosting VBA macros .
*.xla, is the format for Excel Add-in prior to Excel 12. It is still based on the BIFF format and can store VBA macros, given that the goal is to add Microsoft Excel capabilities.
*.xlsx, is the default worksheet format for Microsoft Excel 12, which is based on XML. Unable to save VBA macros , due to security reasons. It is actually a compressed archive in ZIP format with a document structure consisting of XML text documents. Is a replacement for the
*.xlsm, is a Microsoft Excel 12 worksheet format , which is based on XML, but can store VBA macros .
*.xlsb, is a Microsoft Excel 12 worksheet format , which is based on XML, but is encoded in binary form. This format has the advantage of being faster to open and more efficient, considering that the purpose of this format is to handle very large worksheets consisting of tens of thousands of rows or hundreds of columns.
*.xltm, is a format for Microsoft Excel 12 worksheet templates , which are based on XML but are capable of storing VBA macros . It is a replacement for the format .
*.xlam, is a format for Excel Add-in to increase the capabilities of Excel 12. By default it is able to handle VBA macros , considering the goal is to add Excel capabilities.
Benefits of Excel Formulas in the World of Work
Excel formulas in the world of work basically often use these two formulas. For simple data using formulas, while the function formula for large-scale data.
The benefits of function formulas make it easier for users in terms of:
- Gets accurate calculation results from lots of data.
- Help presentation needs on data visualization.
- Tidying up large amounts of data so that it is neatly arranged.
- Save time when processing data.
- Processing big data becomes easier and more practical.
Understanding the IF Function in Excel
What is the meaning of the Excel IF function or IF formula?
The IF function or IF formula is one of the Excel functions that fall into a category or logical group that is often used to perform certain logical tests on Microsoft Excel formulas. The Excel IF formula will produce a certain value if the condition we specify to be evaluated is met (TRUE) and will produce another value if the logic test or condition is not met (FALSE).
In other words, this excel IF function will evaluate certain logical tests and then produce TRUE or FALSE values. The TRUE value is the value where the condition is met and the FALSE value is the value for the condition that is not met.
To do this logic test, the if function in Excel usually uses the Excel comparison operator that we discussed earlier. namely using the operators =, >, >= , < or <= and so on. Therefore, before further studying this logical function, it’s a good idea for you to first understand Excel operators. If you understand enough about comparison operators in Excel, then we will learn about how to use the if formula in Excel.
IF Formula Format and Function in Excel
Before discussing the types and uses of this formula, you should first know the function of this formula. This formula in Microsoft Excel is used to determine true or false values. It is said to be true if the value in a condition is met and said to be false if the value in the condition is not met.
There are two common formats for this formula in Microsoft Excel. First is the format of this formula using a comma separator (,). The general formula can be written as follows:
IF(condition, if_condition_true, if_condition_false)
Then the second format is a formula using a semicolon separator which is written as follows:
IF(condition; if_condition_true; if_condition_false)
Then what is the difference between the IF formula using a comma separator and a semicolon? Actually, the two formulas have the same function and results, it’s just that the difference is the region that is set on the computer.
If the regional setting on the computer is Indonesian, then you use this formula with a semicolon separator, whereas if the regional setting on the computer is English, then you use this formula with a comma separator.
Single IF Types and How to Use Them
One of the most familiar types of these formulas is probably a single IF. Single IF formula means using 1 formula without combining other formulas.
Generally, used to determine a graduation from a particular subject.
- A score of 70 to 100 is PASS
- Any value less than 70 is FAIL
The steps for using the Single IF formula are as follows:
1. The First Way
The first way you can do is as follows.
- Click the cell that will use the IF function. For example, Cell C2.
- Type the IF function in the Formula Bar, type =IF(to start the IF function).
- Create arguments to test the desired criteria.
- In this example, there are 2 test criteria, namely if >= 70, then PASS and if <70, then FAIL.
- Then, enter the first criterion for logical_test.
- To do this, click the cell to be tested (B2), type >=70,. Make sure the formula written becomes =IF(B2>=70,.
- Enter the value_if_true argument, meaning that B2 contains a value >=70, then Excel will give this result.
- Please type “PASS” or the formula becomes =IF(B2>=70,”PASS”,.
That’s the first way to use a single IF that you can use.
2. The Second Way
Furthermore, the second way that you can do is as follows.
- After that, enter the value_If_false values argument if logical_test is not true.
- So, if the value in cell B2 does not contain >=70, then Excel will return the result according to value_if_false.
- Please type “FAILED” then type a closing parenthesis and press Enter so that the IF formula becomes something like =IF(B2>=70,”PASS”,”FAILED”).
- Perform AutoFill for calculations automatically.
- Use the AutoFill feature to calculate the values in cells B3 to B6 without repeating the formula.
The method of using the single formula above can be used for numeric or text data.
Multilevel IF Formulas, Functions, and How to Use Them in Excel
Apart from single IF, there are also nested IFs which are often used in Microsoft Excel. The method that can be used for multilevel IF is by combining two or more IF functions in one cell.
This technique is suitable for use if it has more than one test criterion. Generally, the two types of multilevel formulas that are often used are IF “And” and IF “Or”.
1. IF “And”
The first formula in this nested IF is IF And. In this criterion, the combination of the IF and “And” functions for testing 2 or more criteria that are related or similar.
The nested IF formula “And” can be written as follows:
Students are only declared PASS if they meet the Minimum Passing Standards for both Grades (MID and UAS).
Conversely, if it does not meet the criteria it will be declared FAILED.
Following are the Minimum Passing Value Standards:
- MID value = 75
- UAS score = 80
The steps for using the Multilevel IF “And” formula are as follows:
- Enter the IF Function along with the logical_test and the formula separator operator ( , or ; ).
- The written formula will be =IF(B2>=75,
- Enter logical_test along with the formula separator operator ( , or ; ) for the second IF Function.
- The second formula written will be =IF(B2>=75,IF(C2>=80,
- Enter value_if_true or the formula becomes =IF(B2>=75,IF(C2>=80,“PASS”,
- Enter value_if_false written in the formula to =IF(B2>=75,IF(C2>=80,“PASS”,“FAILED”),
- Enter value_if_false and a closing parenthesis for the first IF Function or the formula becomes =IF(B2>=75,IF(C2>=80,“PASS”,”FAILED”),“FAILED”).
- Press Enter to see the results.
This means that from the word problem, if B2>=75 “and” If C2>=80 then PASS.
However, if one is not fulfilled then it FAILS, likewise if all are not fulfilled then it FAILS.
2. IF “Or”
Apart from IF And, there is also an IF Or formula which can be written like this:
- A score of 70 to 100 is PASS
- Any value less than 70 is FAIL
It can be seen that there are 2 criteria above that are not related to each other.
In unrelated calculations, the data tested needs to be the same, namely based on numbers.
The steps for calculating IF Excel “Or”, namely:
- Do a test Logical_test =IF(B7<70,
- If (true) the value in Cell B7 is less than 70, then Excel returns value_if_true.
- If the value in Cell B7 is not less than 70, then Excel will return the result of value_if_false.
- Enter this formula second with =IF(B7<70,”FAILED”,IF(B7<=100,”PASS”,”FAILED DATA”.
- If logical_test is true, then the result is value_if_true =IF(B7<70,”FAILED”,IF(B7<=100,”PASS”.
- If logical_test is false, then the result is value_if_false =IF(B7<70,”FAILED”,IF(B7<=100,”PASS”,”DATA WRONG”.