Example on how to use the IF function
Let's test if the values in the first column in the table below are greater or equal to 100 and write an answer next to each cell according the outcome. First make a following table to your Excel sheet:
1. Click on the first cell under heading "True or false?" Next Click the fx button under the ribbon and search for IF function. Double click on IF in the search results list.
2. In the opening dialogue: in Logical test argument input field you have to set up a simple true / false test. Click on the first value cell and type >= 100. This tests if the value in the cell is greater than or equal to 100. (Example: A2>=100).
Write "value is greater than or equal to 100" to the Value if true field.
Write "value is less than 100" to the Value if false field.
Press OK and drag copy the values down to fill the table.
You should see following results:
You can also write the function manually, the syntax is (note: depending on your regional settings, use comma (,) instead of semicolon (;) as separator):
=IF(LOGICAL TEST;VALUE IF TRUE;VALUE IF FALSE)
In our example the function syntax would have been:
=IF(A2>=100;"value is less than 100";"value is greater than or equal to 100")
Multiple IF functions  chaining IF functions
With Excel's IF function you can test only one condition at a time. You can use a chain of multiple IF functions to test more conditions.
Remember the syntax of the IF function (note: depending on your regional settings, use comma (,) instead of semicolon (;) as separator):
=IF(LOGICAL TEST;VALUE IF TRUE;VALUE IF FALSE)
When we need to test multiple conditions, we start each new IF function in the Value if false part of the previous IF function. Like this (marked with red):
=IF(LOGICAL TEST ONE;VALUE IF ONE IS TRUE;IF(LOGICAL TEST TWO;VALUE IF TWO IS TRUE;VALUE IF BOTH TESTS ARE FALSE))
You can chain up multiple IF functions, if you just remember to start every new IF function in the value if false part of previous IF function.
=IF(LOGICAL TEST ONE;VALUE IF ONE IS TRUE;IF(LOGICAL TEST TWO;VALUE IF TWO IS TRUE;VALUE IF BOTH TESTS ARE FALSE))
Example 2: chaining up multiple IF functions
Let's make two IF functions that test: if the value in the first column is greater than 50 and write "over 50" to the next column
 if the value in the first column is greater than 100 and write "over 100" to the next column
 if both arguments are false, just write false to the next column
Value

Over 50 / Over 100 / False


80
 
140
 
50
 
200
 
99

The key thing to remember here is that when any of the logical arguments is true, Excel stops running the functions and prints out the value if true of that function.
So if you start testing if the value is greater than 50 Excel will never get to test if it is greater than 100, because the first logical test was true. So here is the right syntax:=IF(A2>100;"over 100";IF(A2>50;"over 50";"false"))
Multiple conditions with AND function
With Excel's IF function you can test only one condition at a time. Use the AND function to test multiple conditions inside one IF function. Here are instructions on how to use AND function with IF function.
Remember the syntax of IF function (note: depending on your regional settings, use comma (,) instead of semicolon (;) as separator):
The syntax for AND function is :
AND function results to TRUE only if all conditions are true.
Remember the syntax of IF function (note: depending on your regional settings, use comma (,) instead of semicolon (;) as separator):
=IF(LOGICAL TEST;VALUE IF TRUE;VALUE IF FALSE)
The syntax for AND function is :
=AND(CONDITION A;CONDITION B; CONDITION C;....)
AND function results to TRUE only if all conditions are true.
Example 3: Excel IF function with AND function
Look at the table below and copy it to your Excel sheet, starting from cell A1.
Let's test if value 1 is greater than 100 and value 2 is YES. Let's write TRUE in the third column only if both conditions are true.
Value 1  Value 2  True or false? 

100  YES  
30  YES  
50  NO  
30  YES  
230  NO 
 Click on the first cell under heading "True or false?"
 Write =IF(AND(A2>100;B2="YES");"TRUE";"FALSE") and press enter.
 Drag copy the function down rest of the cells.
 You should see following results:
Value 1

Value 2

True or false?


100

YES
 TRUE 
30

YES
 FALSE 
50

NO
 FALSE 
230

YES
 TRUE 
230

NO
 FALSE 
Let's see that function again:
=IF(AND(A2>100;B2="YES");"TRUE";"FALSE")
Here marked with red, you can see the AND function inside the Logical test argument. After the AND function follows Value if true and Value if false accordingly.
Try not to become a man of success, but rather try to become a man of value. See the link below for more info.
ReplyDelete#value
www.ufgop.org
Good day! I was so impressed to see a very wellpresented dishes that is absolutely tasty
ReplyDeleteand delicious. You've done a great job.Well, thank you for sharing your talent and article
it is very well appreciated. You can also visit my site if you have time.
n8fan.net
www.n8fan.net