Found Blog

5-tips-again

5 Great Uses of the IF Formula in Excel (you may not know about)

Posted on in the categories

Hello, and welcome to my second post in this series, where I dare to stand up against some of the most common Excel challenges we face within this industry, and defeat them head-on with cold, hard, uncompromising logic. You may be well versed in Excel and know much of the below already, but have a look through as I’ll be touching on a number of additional hints and tips along the way as well..

I’ve also attached a worksheet to this post for you to see and work with the examples themselves. 

In my previous post I looked at some top tips to save you time in Excel, ranging from data manipulation, to cell formatting and general navigation. In this post I look to tackle the most common uses of the IF statement.

What IF…?

Getting acquainted with the many IF statements will start to open up a whole range of new opportunities for you when you’re looking to analyse and manipulate data. It can be used to quickly fetch results based around given criteria, to sort and catalogue data, or be used in conjunction with other formulas for some very inventive and powerful operations.

The main essence of the statement is to return a result when meeting certain criteria. So for example..

-          Return the sum of all clicks IF the month is March

-          Return the phrase “Out of Stock” IF the stock level field = 0

-          Lookup an associated cost IF a cell contains the word “forecast”

Excel will tell you that the basic IF statement follows this format:

Below I’ve applied this to some very simple statements:

=IF(A1=”March”,1,0)

If the value in cell A1 is equal to “March”, then return a 1, and if it isn’t then return a 0.

=IF(A1>10000,”high spend”,”low spend”)

If the number in cell A1 is greater than £10,000, return “high spend”, and if it isn’t then return “low spend”

Of course you can return more sophisticated results than a simple number or text string if the criteria is met, but we’ll  get into those later. Different numerical operators can also be used well in these types of statement. Equal to, Greater than, less than, greater than or equal to etc. can all be used to good effect.

So now the formula is laid down, let’s get into some of these great uses…!

1. Using SumIF, CountIF, and AverageIF

Some of the most common Excel IF formulas allow you to sum, count and average data based on set criteria. To demonstrate these I’ve put together a simple affiliate commission report below:

With these particular formulas you first supply the range that you wish to lookup, the criteria that you wish to match in this range, and then finally supply the associated result.

=SUMIF(B2:14,”Merchant 1”,D2:D14)

If column B contains the value “Merchant 1”, return a sum of column D (commission) for that merchant.

=AVERAGEIF(B2:14,”Merchant 1”,D2:D14)

If column B contains the value “Merchant 1”, return an average of column D (commission) for that merchant.

=COUNTIF(B2:14,”Merchant 1”)

If column B contains the value “Merchant 1”, return a count of the number of times it appears.

You won’t always want to apply a text string as the lookup value. In the example below, I wanted to summarise total commission by day and display it in a nice (new) table. You can see I’m looking up the previous data as before, but in this instance I’m summing where the date is equal to the cell next to the formula (F2).

Best Used: When you want to summarise data from a large table, and display it separately.

2. Using SumIFs, CountIFs, and AverageIFs

What’s that? You want to do the same thing but with multiple criteria? Well worry not, for the ‘Ifs’ formulas allow you to do just that. Keeping with the previous example, I’ve decided to break my daily commission out even further, and display it by merchant as well. This will give me a more granular overview of performance over time. I’ve adjusted my table so that merchants stretch across the top, with dates going down the side. I’ve also kept both the date format and merchant names exactly the same as in the commission report, so that I can refer to them by cell reference.

These are slightly different to the previous formulas, as you define the result first, and then supply multiple conditions afterwards..

=SUMIFS(D2:D14,A2:A14,F4,B2:B14,G3)

Sum the commission column (D2:D14) where the date column (A2:A14) is equal to the date in my summary table (F4) and the merchant column (B2:B14) is equal to the merchant in my summary table (G3).

It’s worth noting at this stage that my formula makes use of $ symbols as well. These lock cells in place, and stop them updating when you move your formula across. For instance, without $ symbols, my D:D reference would suddenly change to E:E if I moved the formula across one.

You can place $ symbols before both the letter and the number in a cell reference to lock each part individually. Doing it on both will lock the cell, column or row absolutely. If you look at the formula in the table below, you can see I’ve locked the commission table lookups absolutely, but allowed a bit of flexibility with my own table lookups so that the date and merchant update as well. Especially handy if you keep updating the table with new merchants or new dates!

Best Used: When you want to summarise and display data separately, but in a more granular format.

3. IF AND and IF OR

Sometimes there are scenarios where you have very specific criteria that you need to build into your Excel formulas. IF AND can help you define multiple conditions that all need to be met before returning the desired result, and IF OR can return a result when only one of multiple conditions is met.

To put it in the context of another typical, industry scenario..

I’m running a PPC campaign with a bonus scheme set around achieving a set number of sales in a month, and I want to analyse what bonus I’m likely to hit. I’ve started by building out a simple calculator that looks at current sales, and scales it by days in month to bring me an expected sales figure.

I want the bonus to update automatically throughout the month dependent on expected sales, and add it to the expected commission. There are many ways you can go about doing this, but one particularly tidy example uses IF AND formulas..

To tell if tier 1 has been hit: =IF(AND(C11>99,C11<200),50,”")

If expected sales are greater than 99 and less than 200, return £50 (tier 1) else return nothing.

To tell if tier 2 has been hit: =IF(AND(C11>199,C11<300),100,”")

If expected sales are greater than 199 and less than 300, return £100 (tier 2) else return nothing.

To tell if tier 3 has been hit: =IF(C11>299,200,”")

If expected sales are greater than 299, return £200 (tier 3) else return nothing.

You can build these into a table next to the bonus tiers and include them in a further calculation to show commission with bonus.

The IF OR formula works in much the same way, only with this formula only one of the conditions needs to be true to trigger the output. In the following example I have a breakdown of sales revenue by type of merchant. I want to only calculate revenue for PPC and SEO and display it in a separate column. The IF OR formula can help here..

=IF(OR(B2=”PPC”,B2=”SEO”),C2,””)

If cell B2 is equal to PPC or SEO, return the revenue amount, else return nothing.

Best Used: When you want to be a bit more selective with the data you select.

4. Nested Ifs

With Excel, you can actually use the ‘value if false’ option to include further IF statements. If the condition in the first IF formula isn’t met, it will move onto the next IF formula, and so on..

Let’s return to the bonus tier example I gave in point 3. I previously calculated bonus tiers with IF AND statements individually, and returned a number next to them. It looks nice. However if you preferred you could actually scrap this and calculate the whole thing with nested Ifs instead, returning the result in a single cell..

=IF(C11>299,200,(IF(C11>199,100,(IF(C11>99,50,0)))))

If expected sales is greater than 299, return £200 (tier 3) else if expected sales is greater than 199, return £100 (tier 2), else if expected sales is greater than 99, return £50 (tier 1), else return £0.

Note: In this example I had to calculate the tiers in reverse order. If you were to calculate IF sales > 99 in the first IF formula, the condition would be met and it wouldn’t trigger the other IF statements.

Best used: When you want to provide multiple conditions, and return a distinct result for each.

5. Blank out those horrible looking errors with IFERROR

And so on to our final tip for this session, and a really nice formula which can help turn an awful looking table into a much cleaner and more presentable one. I use this formula all the time.

Here is a very simple table that might look quite familiar to you. A simple conversion rate calculation has thrown up two errors. An incorrect entry of “no sales” has forced an incorrect value error, and a ‘divide by 0’ error has been thrown by the last row where there have been 0 clicks.

I don’t know about you, but whenever I see these I like to eradicate them upon sight. A simple IFERROR formula appended to your existing formula gives you a lot of control in doing this, and allows you to supply an alternate value.

So where my conversion rate formula previously looked like this:

=(C3/A3)

It now looks a little like this:

=IFERROR((C3/A3), “N/A”)

..where the “N/A” is the value I’ve chosen to replace the error messages.

 

There. Doesn’t that look better? I’ll be sure to win that client pitch now!

It’s really very simple to apply, and can be used on even the most complicated and long-winded formulas. You can choose anything to replace it with, be it a numerical value, a text value, or even another formula – not bad!

Best used: If you’re sending out data to clients or bosses, and don’t want them sighing in despair.

Stay tuned for yet more Excel blog posts from me in the near future, where I’ll be looking to put even more formulas and shortcuts to good use. Remember to check out the attached worksheet for further info, and my previous post - 5 Great Uses of the 5 Great Time-Saving Excel Tips (you may not know about)

Got a great idea for further blog posts that you’d like me to cover? Why not email me?

  • http://twitter.com/eagle8 Keith Horwood

    @jamie_brady:twitter  - You are developing into a fine Excel Guru! All this stuff is essential to know when using excel…

  • Jamie Brady

    I realise that my IF AND example wasn’t exactly
    the best in the post above, especially given that I kind of undermine it later
    by doing the same thing only more easily. As such, consider the following where
    it’s absolutely the best use (in fact why not try it at home!?)

     

    “A PPC Analyst needs to pass both the
    standard Adwords exam and one of the advanced exams before they can become a
    fully qualified Adwords professional. The pass rate is 80% on each. You have a
    list of analysts in column A, with their two exam scores in column B and C
    respectively. How can you use the IF AND formula in column D to indicate a pass
    or fail for each analyst?”

     

    Ps: This is also in the attached
    spreadsheet.

  • Pingback: 10 Great Excel Shortcuts (You might not know about) » Found | Found

  • http://www.exceldashboardcentral.com/ Phil Kowalski

    I personally do think that the examples above are quite nice BUT the nested if example is a no-no. At first sight it always looks easier to simply nest some if’s within each other but honestly, this can really lead to trouble. I prefer a different method here and that is simply using mutlipliers or boolean operations for numerical calculations and the additional REPT function for text comparisons.

    How does that work? Let’s have a short look at my approach to the aforementioned problem:

    =(C11>299)*200+(AND(C11>199,C1199,C11299)

    Excel handles TRUE as the number 1 and FALSE as the number 0. So that means in Excel’s view the complete formula e. g. for C11 would be

    =0*200+1*100+0*50

    which results in 100. As we are talking about ranges here we had to utilize the AND function.

    As this approach has not been first described by me, I refer to Daniel Ferry’s (Excel MVP) blog Excel Hero. Search Google for Excel Hero I heart IF (the name of the corresponding blog post) and ensure you read the comments, where he describes how to approach non-numerical evaluations.

    Hope that helps. Thanks for your post

    • FoundLuke

      /approve

    • Richa Priyanka

      I have a situation where in a column I have 3 values, NA, less than 0.7 and more than 0.7. I want a formula which can return=NA for NA, Fail for less than 0.7 and PASS for more than 0.7.

  • Manoj Thapliyal

    Thanks dude….really helpful…. Thanks once again..

  • rudi

    hi

    I need help with a formula

    =if(a2<30, a2/100*90, a2/100*85)
    even if its greater or less than 30 it always does a2/100*85
    please help