↑ Return to Condition

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:

Function_IF_1

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"))

Fill the following spreadsheet

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.

Change your function to display

  • 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)




22 comments

1 ping

Skip to comment form

  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

  5. harry

    lol, im good

  6. Anonymous

    I would r8 8/8 m8

  7. Anonymous

    hey Lydia

  8. Anonymous

    jajajajajajajajajaj a

  9. margret

    blah blah blah

  10. Anonymous

    shit

  11. Anonymous

    thankyou so much! really helpful

  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)

  18. Anonymous

    This page was useful. Thanks.

  19. Anonymous

    alright we get it

  20. Anonymous

    ok

  1. XLS or XLSX file ? - Excel Exercise

    […] IF Function […]

Leave a Reply

%d bloggers like this: