The IF formula

This is a very short post showing the syntax of the IF formula in excel. The IF formula is basically used to see if a condition is met and returns a result depending on whether the condition has been met or not.

The syntax is as follows:

=IF(logical_test,[value_if_true],[value_if_false])

As you can see the function takes three arguments, out of which two are optional. 

The first argument is the logical test. Here you provide the condition This can for example be:

A1>5 (The value of cell A1 is greater than 5)

The second argument is the result to provide if the condition is met. Let's say the A1 is 9. Now if the value in A1 is greater than 5, you want to subtract 5 from it. There the second argument will be:

A1-5

The third argument is the result to provide if the condition is not met. In this case if the vaule is not greater than 5, you want to take the value from A1 as it is. Therefore the third argument will be:

A1

Combining all this into one function gives:

=IF(A1>5,A1-5,A1)

(I read it aloud as If A1 is greater than five, subract 5 from A1 else return A1.)

Now remember the last two arguments as optional, however it wise to always enter the arguments as you might not get the result you expect if you miss out one of the arguments. Excel will give you an error if you only provide the condition. If you simply want to return a true of false, you can omit the IF function completely and just type the following in the formula bar:

=A1>5

This will return a true or false depending on the whether the condition is met. The same can be achieved using the IF function by entering TRUE or FALSE as the last two arguments.

You can't do away with the second argument because then what you are keeping as the third argument will be the second argument for excel. Putting nothing in between the commas as the second argument will return 0 if the condition is met. If you want to return a blank if the condition is true, you simply put in:

=IF(A1>5,"",A1)

You can completely do away with the third condition i.e. you might not keep the second comma at all. In this case if the condition is not met, you get the result as FALSE.

To avoid complexities, it is better to enter all the three arguments. A very simple and basic formula to use once you get hang of it. 

Only show the result when input is entered

There are times where you may have column set up with formulas so that all the columns gets calculated when the inputs are entered.

Take the example of the below table:

Column C, D, E, and F are all filled with formulas so that the columns get calculated once the net price is entered.

However, what if you want to show any kind of output only when the net price is entered. You can add a simple formula to check if the net price has been entered, any only calculate the gross price if output has been entered otherwise show a blank. I am sure you know where I am getting to.

The formula to use is the IF formula.

 IF (INPUT IS NOT BLANK, YOUR FORMULA HERE, EMPTY STRING)

 In C3, the following can be entered:

 =IF(B3<>"",B3*3%,"") 

This can be repeated along the columns and the formulas can then be dragged down, providing a much cleaner work sheet: