What is the IF function?
Microsoft Excels IF function is a logical function that is able to use a logical operation to compare two values and output new values, or even a new operation, depending on if the output of the comparison is TRUE or FALSE.
Why would I use the IF function
Suppose you are trying to compare two numbers to see if one number is larger than the other, then you would use the Excel IF function to do this. It might seem obvious that you can know which number is larger by looking at the two numbers but suppose you have thousands of comparisons to make, and then suppose you have to perform an additional calculation if you find that your comparison is TRUE. The power of the Excel IF function is that it can do this using automation and can make the process of doing comparisons easy!
The Excel IF function gets even more power in that it can perform additional operations if the comparison is False or can make multiple comparisons by nesting IF statements. Warning, nesting more than two or three IF statements can lead to a very complex and difficult to troubleshoot formula. It is recommended that if you need additional logic from an IF function, you use the Excel IFS function.
How do I use the IF function
- You need a input value that will be used for the logical test.
- You need a logical test value that will be compared against your input value.
- A logical test (>, <, =, >=, <=) and so on.
- You need to specify a value, text, or operational output if TRUE.
- Optionally, you will need a value, text, or operation to output if FALSE.
How do I type the IF function into Excel
The formula syntax for VLOOKUP is:
=IF(logical_test, [value_if_true], [value_if_false])
What values will the IF function require
logical_test – This the comparison that will be made. Generally a cell on your spread sheet will be compared against a known value, text, cell, or formula to see if the result is True or False. There are many logical tests that can be performed by Excel, greater than (>), less than (<), equal to (=), and combinations such as greater than or equal to (>=).
value_if_true – This is what you want the function to output if the logical test evaluates as true. This can be another value, text, or formula.
value_if_false – This is what you want the function to output if the logical test evaluates as False. This can be a value, text, or formula. This input is optional.
Is there anything else I need to know about the IF function
The IF function can be very powerful and make short work of comparisons. The IF function can be nested for multiple comparisons but this feature should be used with caution because of the exponential nature of the outputs and errors that can result.
Example use of the IF function
In this example, we are going to do a basic comparison to find out if a number equals 1 or if it is some other number. We will start with a table that has a vertical column of values equal to either 0 or 1. If it logically evaluates as True for being equal to 1, we will output the text “TRUE”. By default, the function will output “FALSE” if the logical test doesn’t evaluate to “TRUE”.
As can be seen, all the cells with a number one report “TRUE”, and those with zeros report “FALSE”. The equal sign “=” was used as the logical comparison, as in, equal to 1. The equation used to obtain this result is:
=IF(B3=1,”TRUE”)
The worksheet with all the equations shown looks like.
Lets add some complexity to this example by also specifying the True the False output value, but instead of the default “FALSE”, we will have it say “Equals other than One”.
=IF(B3=1,”Equals One”, “Equals other than One”)
Here we told it what message to output for both logical test outputs. If the number equals 1, then it outputs the text “Equals One”, otherwise, it outputs, “Equals other than One”. For clarity, see the next spreadsheet screen shot.
How to use the IF function to round between Zero and One
In this example, the Microsoft Excel IF function is used to round a value between zero or one. Any value greater than or equal to 0.5 is output as 1, any value less than 0.5 is 0.
With formulas.
=IF(B3>=0.5,1, 0)
How to use the IF function to add shipping charges to an order
In this example, the Microsoft Excel IF function will be used to add shipping charges to the order spreadsheet. If the Shipping field/cell is filled with the text “Yes”, a $10 shipping fee is added to the order subtotal before the final total is calculated. This example shows some of the more powerful ways to the the IF function.
The equation used to add the shipping charge is:
=IF(D8=”Yes”, 10, 0)
If the field is left blank or something other than “Yes” is input, no shipping charges are added.
The if function has saved me lots of time comparing prices.