How to use the IF Function in Excel

Once you've got the hang of the basics in Excel you'll normally require the use of the IF function to carry out slightly more complex actions.

At first glance, the IF function can appear a little daunting but after some time it'll become second nature and enable you to create spreadsheets that will begin to deliver significant benefits.

The IF function usually incorporates 3 elements (arguments), the logical_test, the value_if_true and the value_if_false. You'll notice from the square brackets below that the last two arguments are optional. If you are new to the IF function I would recommend supplying all 3 arguments to help understand what the formula is doing.

logical_test

This part of the formula, is usually a comparison, the simplest form is A1 = B1 where A1 and B1 are 'tested' to see if they match. The output of this test is one of two possibilities, true or false. Some other logical tests could be A1 > B1, A1 = "apples" or even A1 = B1 = C1. The logical test element can expand to become considerably more complex

value_if_true

This part of the formula is the output when the logical test is true, for example, if the desired response when A1=B1 is to output 1, the formula would begin =if(A1=B1,1...

value_if_false

Similarly, this part of the formula is the output when the logical test is false. Expanding on the previous example, if the desired response when A does not equal B is 0 then the completed formula would be =if(A1=B1, 1,0)

Note - Remember that when using text in formulas it needs to be contained within the " character for the Excel to recognise it as text and not a reference. This is not required for numbers as you'll see in the following example.

Example

Practical example

Here we're going to create a formula to test the contents of a cell to see if it matches the text "This".

This will now test the contents of cell B2 to see if it matches "This" if it does match (true) the desired response is a 1. If it does not match (false) the desired response is a 0.

Here you can see the output of the formula in cell C2 is a 1 because B2="This"

When the formula is filled down to the next cell, the test is applied to the subsequent cell, C3 which does not contain the text "This" so the result of the test will be False and the response from the formula will be a 0

Comments

How to combine multiple files with Power Query (with no VBA and just 10 mouse clicks!)

The need to combine information from multiple files is one that most users of Excel will have come across at some point in the use of Excel. I've personally spent far too many hours aggregating data from multiple files, that are identical in structure, so that I can analyse larger datasets and provide insights into products and processes. For anyone who has also done this and not yet discovered Power Query you'll probably be amazed how simple the process has become. I realise there might be some who will say "just use VBA, its easy once you learn how to code..." and they would be right. The method using Power Query provides a zero code solution that is an evolution of the Excel interface that many will already be familiar with. In this example, I've created a sample file and created a number of duplicates of the file which I've saved in a folder. The folder contains only these files and i'd recommend you do the same if you're looking to try out this pr...

Extracting data from Word (.docx) files into Power Query

Word and Excel don’t usually get along too well so it's no surprise that Power Query isn't directly compatible with its estranged cousin Word either. If you are presented with the need to import data from Word into Power Query you'll be please to hear it is possible however it requires a couple of manual steps to make it work. The manual steps could fairly easily be completed by a batch file which would automate the process further. Here is the Excel data pasted 'as values' in a Word file which i'll use for the first example Here is the Excel data pasted with 'keep source formatting' which i'll reference a couple of times in the article. Although the steps I've covered below aren't complex, this whole process has some unknowns around it so you may find the result in your instance varies from mine. The Word file I've used contains the contents of a range of excel cells that I deliberately pasted as values into Word to create a test file f...

How to automate the import of all files in a Google Drive folder to PowerBI, now updated, please read the first paragraphs!! (PowerQuery)

Update!!! This method no longer works although there is a new Google Sheets connector for PowerQuery that is currently in beta. If you're using PowerBI, you'll need to enable the preview features to enable it. https://docs.microsoft.com/en-us/power-query/connectors/googlesheets In the interest if demonstrating how it 'did' work, the original post is provided below. If you attempt to replicate this, you'll quickly realise the website doesn't behave as it used to. (here is the original article) Using PowerQuery to access multiple files within the same folder on a local or network drive is a game-changing feature that will almost certainly save many people hours of effort. This functionality is great if your data exists in a place that is easily accessible but what do you do if your data is somewhere less accessible like Sharepoint, OneDrive or even Google Docs? I have previously connected to data on SharePoint and found it fairly straight forward which raises the...

Red Gig

Search This Blog