# IF Function

The IF function is used when you have to display different result in function of values of other cells.

## The IF function

The IF function needs 3 parameters

• The test
• The result if the test is TRUE
• The result if the test is FALSE

=IF(test,if test is TUE,if test is FALSE)

The IF function compares data and returns 2 values (AND ONLY 2) : TRUE or FALSE

## Writing

If you want to display text inside a cell, you must write the result of the True and False parameter between double quote.

=IF(test;”Pass”;”Fail”)

It’s also with the symbol double-quote that you can express an empty string. If you omit the third parameter and that the result of your test is false, Excel will display FALSE inside your cell. So, to avoid this, it is better to write "" instead of nothing.

=IF(test;"Pass";"")

For numbers or formulas, you must write your second and third parameters without double-quote.

=IF(test;A1-B1;A1-C1)

## Logical Test

The first parameter of the IF function is a logical test. A logical test compares 2 data. You can have these possibilities:

## Example

Imagine the case of the result of a football match. You want to display Win or Lose when the team in column A has a better result than the team in column D

For the cell E2, you will write

=IF(B2>C2;"Win";"Lose")

Fill the following spreadsheet (directly inside this document)

But, as you know, it is also possible to have a draw. So, you must manage not 2 but 3 states with your IF function.

To solve this, you must include another IF function inside your first IF function. In your first function, you replace the first False parameter by your second IF function. So the cell E2 becomes:

=IF(B2>C2;"Win";IF(B2<C2;"Lose";"Tie"))

## Exercise : Return a grade

In column E, you have the average result for each students for an exam. In column F, write a function which returns if students Pass or Fail if the average mark is greater or equal than 10.

• Fail (when the average is below 10)
• Pass (when the average is between 10 and 12)
• Good (when the average is between 12 and 14)
• Very Good (when the average is between 14 and 16)
• Excellent (when the average is beyond 16)

=IF(E2<10;”Fail”,IF(E2<12,”Accepted”;IF(E2<14,”Good enough”,IF(E2<16,”Good”,”All right”))))

You can also use the VLOOKUP function (value approximate) to avoid writing many IF functions.

## Exercise : Management of the stocks

You manage inventory and orders of a company. You can not honor orders if you do not have the full amount requested.

So you create an IF function that will check that you have the quantity in your stock. If your stock is lower, you can not deliver the amount you have.

For example you can write the following function:

=IF(C3<B3,C3,B3)

For the second part of the exercise, you initiate an order with your suppliers if you could not fulfill the entire order. You can write the formula in different ways for an equivalent result

=IF(D4<B4,B4-D4,"")

or

=IF(D4=C4, B4<D4,"")

## Exercise : Equity portfolio

You have a stock portfolio. In column C you have the purchase price and column D the last price. Write the result in column E if you win money or in column F if you are losing. The result should also take into account the number of shares you own.

Then you make the sum of columns E and F and then in cell E9, you make the difference in cell E8 and F8 to see if you gain or loss.

(Solution in the other tab in the workbook)

#### 1 ping

1. ##### Anonymous

this is shit kill me now im never going to use this in life

2. ##### Anonymous

this website is cancer

3. ##### Anonymous

9/11 was an inside job

4. ##### john

sam I can see you

lol, im good

6. ##### Anonymous

I would r8 8/8 m8

hey Lydia

8. ##### Anonymous

jajajajajajajajajaj a

9. ##### margret

blah blah blah

shit

12. ##### Aaron Proudlock

It was super fun , would 1/11 recccomend this to anybody Desting to fail , lol jokes top fail 😉

13. ##### Anonymous

Great help. Thanks!

14. ##### Anonymous

superb knowledge

15. ##### Kimberly Long

Wow, this was super useful and helpful, thank you!!

16. ##### sivasankarithirumeni

It is nice and easy to learn it

1. ##### Anonymous

FUCKING HANG YOURSELF

17. ##### Colin Galley

Mistake in the IF statement provided in the examples it should be a comma(,) not a semi-colon( ;);

1. ##### Frédéric LE GUEN

It’s not a mistake. It depends on the setting of your computer (Control Panel > Region & Languages)