Formula Fridays are short reads, spotlighting a single formula that you can start using straight away.
There is a lot that can be done with the IF function. A LOT.
From a simple statement of ‘if x is greater than y, put true. If not, put false’.
To something a little more elevated such as ‘if x is greater than y, look to a different data set to find the relevant classification. If not, put false’.
And onto the super complex of, ‘if x is greater than y, and y is less than z, put true. If not look for the classification of z’.
If I have lost you, don’t worry we will cover all of these in their own time. This post is just going to focus on the basics of an IF statement.
It’s like our brain in Excel form.
The first thing you have to do is input the logical test. This is where those mathematical signs you learnt at school come into play. You know the ones ‘<’ less than, ‘>’ greater than, and ‘=’ equal to – I always pronounce this equals to, don’t ask me way.
If you want to get fancy, greater than or equal to is ‘>=’, less than or equal to is ‘<=’ and not equal to is ‘<>’.
Next you need to decide what to do if the logical test is true, and what to do if the logical test is not true, as known as false.
An example formula would look like this
=IF(10>5,”Y“,”N“)
You can replace any element of an IF statement with another Function or formula.
So instead of saying IF 10 is greater than 5. You could say if the result of this VLOOKUP is greater than 5. That formula would look like this:
=IF(VLOOKUP(A2,A8:B15,2,0)>5,”Y“,”N“)
Any text inputs need to be within quotation marks, as with other functions, or the formula will result in an error.
The result if true and the result if false doesn’t have to be a text input. They can also be formulas.
=IF(VLOOKUP(A2,A8:B15,2,0)>5,VLOOKUP(A2,C8:D15,2,0)*10,VLOOKUP(A2,C8:D15,2,0)/10)
It all depends on what you want the outcome to be.
You can string multiple IF statements together. This is something called a nested if. But we will cover that in a different post as it takes quite a lot of brain power to get your head around.
Until next time
Ax
SUMIFS. The People’s favourite Function Formula Friday #12 Formula Fridays...
Read MoreHow to make a countdown widget in Excel Formula Friday...
Read MoreFinding an exact match for your needs on the internet can, at times, feel like trying to find the missing pair to your odd sock. You know it’s in the drawer somewhere, but you just can’t see it for love nor money. That’s where I can help, think of me as your Excel Agony Aunt, send in your lonely heart letters and I will find your perfect formula partner.
I can help bring your ideas to life. Let’s talk about what we can build together.
