7 Excel ‘If’ Function Techniques You Need to Know

This is a guest article from Yoav Ezer. If you want to guest post on this blog, check out the guidelines here.

The IF() function is one of Excel’s super functions. It is a fundamental building-block of Excel formulas. You will find it present in almost any complex formula. There is a lot more power in Excel formulas conditions than just the basic IF() function, though.

Here are 7 conditional techniques that can help you create even more robust and useful Excel formulas:

1. Nested If Functions

This is the most basic type of ‘complex’ if() function. You can use an additional if function to create a more complex condition within your Excel formula.

For instance:

=IF(A1>10,IF(A1<20,"In range"))

The function above would test whether cell A1 contains a value that’s between 10 and 20. Only if both conditions are satisfied then the formula returns the value “In range”.

It is possible to use several levels of IF() function nesting. For example:

=IF(A1>10,IF(A1<20,IF(B2="HAS AMMO","FIRE!!!!")))

The formula above tests that A1 contains a number that is within range and that B2 holds the status ‘HAS AMMO’ and only if those three conditions are satisfied, it returns a value of “FIRE!”.

2. Logical-Boolean Functions

Nesting is powerful but it is complicated and often results in a formula that is difficult to read or change. A much better way to create complex conditions is to use Excel’s Boolean functions.

The AND() function will return true if all its parameters are true conditions.

So, the formula…

=IF(AND(A1>10,A1<20), "In range")

Will also check if cell A1 is between 10 and 20 pluse, it is much easier to understand (and to write) then the nested formula above.

The following formula…

=IF(AND(A1>10,A2<20,B1="HAS AMMO"),"FIRE!")

Is ten times easier to write/read then the corresponding nested IF() above

Another extremely useful Boolean function is the OR() function.

The formula…

=IF(OR(A1="CAT IS AWAY",A1="CAT IS BUSY"),"THE MICE PLAY")

Will return ‘THE MICE PLAY’ if A1 equals either ‘cat is away’ or ‘cat is busy’.

3. Countif, SumIf, Averageif, etc.

This group of functions allows you to apply a range function such as SUM(), COUNT() OR AVERAGE() only to rows that meet a specific condition.

For instance, you can sum or count all the sales that were made during the year 2001 as shown below:

sumif-function[3]

It goes without saying that these conditional functions are very useful.

4. Countifs, SumIfs, Averageifs, etc.

The COUNTIFS() and SUMIFS() function (and the rest of the multiple conditions aggregate functions) were introduced in Excel 2007.

These functions enable us to apply an aggregation function to a subset of rows where those rows meet several conditions.

For instance, we can use the SUMIFS() function to sum all the sales that were made in the January of 2001, with a single function…

sumifs-function

5. If and Array Formulas

Array formulas might be the most advanced of the formula techniques and while we can’t hope to cover the topic of array formulas in this article, it is important to mention that combining the IF() function with array formulas is a powerful tool.

In Excel versions prior to 2007, the formula AVERAGEIF() did not exist. So, if for example, you wished to average a range of numbers without including Zeros in the calculation, you needed to rely on an array formula:

averageif-array-formula[3]

Array formulas can also be used to mimic the working of countifs(), sumifs() and the rest of the xxxxxifs() functions, that simply did not exist in Excel versions before 2007.

They can also be used to implement new functions that does not exist such as MAXIF() and MINIF().

For a more in depth discussion of array formulas, look here.

6. IFError() function

A close relative of the IF() function is the IFERROR() function. It allows you return a valid value in case a formula returns an error. For instance, if you have a formula that might cause a division by zero error, you can use the IFERROR() function to catch this event and return a valid value, as shown below:

iserrorNote: It is better to use the IF() function to avoid an error then to use the ISERROR() function to catch an error. It is both faster (in terms of CPU) and better programming practice. So the same result we achieved with the ISERROR() function above can be achieved with an IF() function as shown here:
avoiding-iserror

But there are cases when you cannot pretest the formula parameters and in those cases the ISERROR() function can come in handy.

7. Information functions

Distant relatives of the IF() function are the information functions. This group includes several functions that give you information about the type of the value contained in a cell (if it’s a string, a number, an odd number or an even number), if a cell is empty or if it contains an N/A value and more.

These functions, when used in conjunction with the IF() function can be pretty handy, for example, they allow you to easily check whether a cell is empty:

information-function

Summary

Our examples in this article only scratch the surface, we would encourage you to experiment more into what you can do. These seven simple functions hide a great deal of power and utility.

They are the building blocks of some very useful macros. We hope we have inspired you to put them to use in your own projects!

More Excel tips:

1. How to work with Date functions in Excel
2. Create an Interactive Timeline with Microsoft Excel

Yoav is the CEO of Cogniview, a company that creates PDF to XLS conversion software. Prior to that, Yoav Ezer was the CEO of Nocturnus, a technology-centered software solution company. For more Excel tips from Yoav, join him on Facebook or Twitter.

Related articles

How To Disable Chrome Incognito Mode On Windows And Mac

How To Watch Netflix With Friends From Remote Distance

How To Mirror Or Flip Photos On iPhone And iPad

Fix Instagram Keeps Crashing Or Not Working Issue

12 comments

  1. rakesh says:

    i need to vat formula

  2. Abdul Rahim says:

    Best Formulas, But I want to ask u that if I want to put if formula where I am calculate the difference of two days hour then how I will put a formula on it ? any one help me ?

  3. Zam_achmed says:

    hi guys..
    can saomebody help me ..
    i want to fill data in range B2:D4 with my data from another sheet when condition in A1 is met.

    Thanks B4…

  4. Banino1234 says:

    I encountered another interesting problem that I am trying to solve and it is not easy.
    I have a range that contains unorderd numbers for example A10:A1000.
    I want to find the minimal number in range but only for cells that satisfy some logical criteria, for example only cells in range whos value is  lower than  some value.

    How can I do it without creating additional ranges?

  5. GNS says:

    Your calculation is wrong. 
    Your problem is (7-6)/2+7=13.5,13.5-9=2.5Step-I:   (7-6)/2 => let A —————– (1)Step-II: (1) + 7  => let B ——————(2)Equation   (1) to A = 0.5              then   Equation . (2) to 0.5 + 7 = 7.5then next one is ……. 13.5-9=2.513.5-9= 4.5So Your answer is wrong….
    from: GNS

  6. Thanhchingo says:

    I think the following formula will return minimal number from the range A10:A1000 after eliminating cells contains value less than “criteria”.
    ={MIN(IF(A10:A1000>”criteria”,A10:A1000))}
    This is an array formula, please press Ctrl+Shift+Enter when done.
    I hope it works.

  7. Stacy Obeng-Adjei says:

    pls I am a begginer and I just want you to start the lesson with me right from the start.pls it urgent for me pls help me.

  8. Tanweer06587 says:

    it was helpfull.

  9. Izziecool says:

    how can u input this .
    if a1+a2+a3 = >24 then 220, if not 240

  10. Nafie says:

    how to create a formula..for actual incentive achieved against target . when there is incentive given in one column for actual achievement of 100%.

  11. Toelle says:

    I am trying to format cell G24
    Here is what I want G24 to formulate:

    If F24 is 55 or greater, then G24=150
    If F24 is between 45 to 54, then G24=100
    If F24 is between 35 to 44, then G24=85
    If F24 is less than 35, then G24=0   

  12. Emile Meyer says:

    I need to tell if one cell is larger than the cell next to it!

Leave a Reply

Your email address will not be published. Required fields are marked *