NKW6 PT1 - IF

Introduction

I recently pulled together an Excel session to go through the fundamentals of the IF, VLOOKUP, INDEX and MATCH functions.  To do this I started out by putting together a bit of an explanation and then recording, with sample data, how these functions can be used and common pitfalls.  This is the first session.

Part 1 IF

The IF function can allow you to interrogate the information in a cell or cells and then instruct the programme to return a response, which can be text a number, a cell reference, another function or a calculation. 

It sounds rather complex, another way to think about it might be to set yourself a problem for example If the total cost of a project exceeds £3m then I should abandon it otherwise I will go ahead.  How does this look when written out?

 

·         =IF(A1>3000000,”Abandon”,”Proceed”)

·         The =IF tells the programme what function you are using

·         All functions are enclosed in brackets ()

·         A1 is where to figure for the total cost is found, this could be replaced with multiple cell references in a calculation eg A1+B3-F5, depending upon where the data is.

·         The > symbol tells the programme that the if A1 is bigger than 3000000, effectively setting the criteria for what follows.  This could be less than <, equal to =, less than or equal to <= or greater than or equal to >=.

·         The 3000000 is your limit and can be adjusted as you see fit.

·          “Abandon” is what you want the programme to return if your criteria is met, in this case the cost of the project will exceed £3m.

·         “Proceed” is what you want the programme to return if your criteria is not met ie if the project does not cost more than £3m.

 

Now all of the above elements of the function in the brackets can be replaced with references to cells eg =IF(A1>H3,J7,K9)  the cells referred to will contain pre-written text/figures which the programme can then use to produce the results.

The best way to get to grips with this is to do it so watch the video below and then have a go using some data that you have.  Try to think about things in terms of asking a yes/no or true/false question of your data and then what answer you would then give to reflect those outcomes.

Things cat get really complicated when, instead of false or no you ask a further question and so on.  then we get into the realms of multiple nested IF functions, fun?  Maybe another time...




Comments

Post a Comment

Popular posts from this blog

Conditional Formatting

Image
  What is conditional formatting?    In literal terms it is getting the format of a cell to change depending upon the content of the cell linked to a rule of some sort. In layman’s terms or to the person on the street it is a helpful way to point out a specific thing in your table of information.    Examples might include entries on an arrivals board at an airport that are late in arriving, examination results of the class that are above and below average and the lists of competitors in triathlon by country or team. Wouldn’t it be really handy when scanning those departure boards at the airport if the flights to Newcastle had a black background and white lettering?    You would be able to pick out the flights pretty quickly if that was the case.    Usually red lettering, numbers or background are the only indications we are given about flight status. So why use conditional formatting? Well, speaking from my own experiences, I use conditional formatting as a method of highlighting error
Read more

Power Query: Merge and Append

Image
  Power Query: Merge and Append Discover the efficiencies and benefits of connecting and combining your data sources to drive your governance and performance measurement! Don`t be put off by the terminology, Power Query is simply sorting and organising data that you wish to import into your Excel spreadsheet and in doing so you can save a lot of time and reduce the scope for errors. ; The merge and append bit is talking about taking different data sources and adding them together to form a single source from which you can then do all sorts of interesting things! First of all we need to define what we mean by  merge  and  append . ; To  merge  a data set in this case means to add one data set to another by adding additional fields. For example: If we have a table of data that contains details of employee departments we might want to add it to another data table that contains details of their salaries. ; In effect we will create a single table with more data fields in the form of additio
Read more