304 North Cardinal St.
Dorchester Center, MA 02124
The IF function in MS Excel is one of the basic functions in Excel and it is very easy to learn.
It is used to test whether it is true or false to a specified condition. For example if your test score is less than 50, you FAIL and if 50 and above you PASS the test.
This time, you will run a policy with only one IF function to be used.
Bambang is a teacher to 40 primary school students. After examining the test papers, he entered the mark values into MS Excel sheets in the computer, for each student for ten subjects in all.
Then, he looks at each score earned and types PASS or FAIL in the RESULT column. It takes quite a long time to look at one by one the marks obtained by each student for each subject.
There were 10 subjects and a total of 40 students, so he had to look at 10 pieces of Excel with each containing 40 rows of student names. It is very tiring.
All right, let’s start now. As an example of the use of the IF function this time, we use a table that shows the scores obtained by five 5 out of 40 students under Bambang’s teacher guidance.
Column D is the column that will be filled with student test results using the IF function.
There are two main ways to use the IF function, namely using a wizard and how to enter the IF function directly into an Excel cell.
Method 1 is more suitable for those who are new to MS Excel . Although the steps are very many and require more time, it can avoid mistakes.
Method 2 is for those who are already proficient but you don’t have to worry because when entering “= if (“, you will be able to see what needs to be entered next.
You only need to enter the syntax and test value if true and if not true.
Syntax : IF(logical_test,value_if_true,value_if_false)
From the Excel menu click Formulas and then Insert Function.
Select IF and click OK. Here we can see the syntax of the if function is IF (logical_test, value_if_true, value_if_false).
Just click the up arrow to select the cell that tests the test logic.
Select the cell used for the purpose of obtaining a PASS or FAIL result. Here if C3 <50, the result is FAIL, so we click column C3 first.
After clicking C3, Function Arguments shows C3 automatically.
Add the sign <50 and this makes the condition function as if C3 <50. Next click the down arrow.
Note: In Excel the <sign means smaller.
Enter a value if logical test C <50, correct. In this example, FAIL. While if the value is false, it is a failure. Then click OK.
Now we can see, result = PASS because C3 is greater than 50. Very easy. Here, it will give a “PASS” result because the score obtained by Ali is 50. For a score less than 50, example 49.5 which will give a “FAIL” result.
Click and hold the mouse and drag down to copy the formula to the cells below it.
After copying down to everything, release the mouse and the if function will be copied automatically.
And this is the result we will get and we can see that the result of “PASS” or “FAIL” is very accurate. So, if we are used to doing it, it is very easy and saves time.
You don’t need to remember the syntax above but need to understand what each parameter means that needs to be included in the syntax.
Logical_test: The condition for the test, for example in the example above, if the value of cell C3 is less than 50. Just use the smaller operator sign (<). So enter the sign C3 <50.
value_if_true: Value if the condition for logical_test is true. You can enter numbers or text. If the text needs to be marked “cover the text. If the number does not need any sign. If C3 <50 is correct enter “FAIL”
value_if_false: The value if the condition for logical_test is false. It means whether the value if C3<50 is wrong, in other words, the value is 50 or greater. So in this example, the value is “PASS”.
Note: If you enter a value of 1000 in C3 it still gives a PASS result. This time is just an example. To limit the maximum values that can be entered in cell C3, we can use Data Validation which we will study in the next subject.
Enter the = if function (and automatically, we can see the syntax.
Click on cell C3 and it will automatically appear in cell D3.
Then enter a lowercase sign and a value of 50 along with a comma (,).
Enter a value if the condition in cell D3 is true. In this example, “FAIL” is followed by a comma (,). You can replace it with anything like “FAILED”.
Enter a value if the condition is incorrect. In this example, “PASS” and followed by the closing parenthesis “)”. Replace with the word you like if you want like “PASSED”.
Press the ENTER button on the keyboard and you can see the result showing PASS.
To copy the function from cell D3 directly to C6, just click and hold the mouse and drag it down to cell D7.
What about you? Did you manage to do it? You can also use another method for this formula and it gives the same answer.
You can try and click the link below to download the Excel file.