How to Write If Statement in Excel

If statement has three parameters;

• Condition; condition is the value which you want to test.

• Value_If_True; It is the value that is returned if condition evalutes to True.

• Value_If_False; It is the value that is returned if condition evalutes to False and this parameter is optional. you should pay attention if you do not write anything in the formula the program will place the word “False”, if the result of condition be false.

Here we are going to explain If statement with mixture of these commands in different example;

Sum, Exact, And, OR, Len, Left, Right, isnumber(search).

And at the end we will finish this subject with one example about Nest statement and adding symbol with if statement.

How to Write If Statement in Excel;

Before we start our examples it is good to review some comparison operators;

1- > Greater than

2- >= Greater than equal to

3- < Less than

4- <= Less than equal to

5- <> Not equal to

Example One

We want to define this formula

if the text value of the cell is equal to “Red” then write “TRUE” in the cell, but if it was different write “FALSE”.

for that, we should write this formula;

=If($D$4=“Red”,True,False)

in this formula, we fix the value of the cell D4 by selecting it in the formula and press F4 key in the keyboard or write it in the formula like this “$D$4”.

Example Two

We want to define this formula

if the number value of the cell was greater than 10000 then write “TRUE” in the cell but if it was equal or less than 10000 write “FALSE”.

for that we should write this formula;

=If($F$4>10000,True,False)

How to Find Cells with Specific Text Value Content

We want to define this formula

if the text value of the cell is equal to “Red” then write “TRUE” in the cell but if it was different write “FALSE”.

for that we should write this formula;

=If($D$4=“Red”,True)

Note that “Value_If_False” is optional and if you do not write anything on that part, automatically the program will write “False” if our condition is false.

How to Use Sum Command in the If Statement;

We want to define this formula

If the Sum value of the column cell is greater than 20000 then write “Ok” in the cell but if it was equal or less than 20000 keep the cell empty.

for that we should write this formula;

=if(sum($F$4:$F$5)>25000,“Ok”,"")

Note:

If you do not write anything in the “Value_If_False” in the formula, the program will place “False”, If the False condition is correct. so if you do not want to write anything in the cell you should place " " in the “Value_If_False” in the formula.

How to Use Exact Command in the If Statement;

Sometimes you want to Check the text in the cell and for you capital letter is important.

In this case, you can use “Exact command” in the if statement formula.

In this example, if the content of the cell is “Red”, with the “R” capital letter, the value will be “TRUE”, otherwise the value of the cell will be “NOT OK”.

For this question you should write this formula;

IF(EXACT(“Red”,D4),TRUE,“Not OK”)

As you see in the first example, the result of “IF statement” was true, but when you use “Exact” in the “IF statement” formula, the result is false because the capital letter now important for the formula.

How to Use AND Command in the If Statement;

Sometimes If two status at the same time will be correct then the “If statement formula” can be correct.

In this situation, you can use from “And” command in the if statement formula.

In this example, if the value of the cell D4 is equal to “RED”, and at the same time, the value of the cell F4 is equal to 10000, the result of “If Statement” will be “OK”, otherwise the result will be “NOT OK”.

For this example you should write this formula;

IF(AND($D$4=“RED”,$F$4=10000),“OK”,“NOT OK”)

How to Use OR Command in the If Statement;

Sometimes If we have two conditions and only one of them will be correct, the result of “If statement” can be acceptable for us.

In this situation, we can use OR command in the “If formula”.

In this example, if the value of the cell D4 is equal to “RED”, or the value of the cell F4 is equal to 10000, the result of “If Statement” will be “TRUE”, otherwise the result will be “FALSE”.

How to Use Len Command in the If Statement;

Sometimes we want to find the cells which they have a specific number of letters or numbers or characters or the number of their characters is higher or less than a specific number.

For that we use Len command.

Question

Find the cells which their characters are higher than 3.

For this question, we should use this formula

IF(LEN(D4),TRUE,“NOT OK”)

How to Use Left Command in the If Statement;

Sometimes we want to find which cells in the table started with a specific word.

you can use Left and If commands for finding these cells in the table.

Example:

In the column “H” find all cells which started with the word “WE”.

The LEFT command has two-part, the first part is the address of the cell which you want to search it and the second part is the number of letters which you want to search from the beginning of the sentence,

Here because our word is “WE” and this word has two letters we should specify number two for this part, and we say by this formula, only search in the first and second letter in the cell from the left side and find the word “WE”.

For that you should use this formula;

IF(LEFT(H4,2)=“WE”,TRUE,FALSE)

specify this formula to other cells of the column H.

How to Use Right Command in the If Statement;

Sometimes we want to find the cells which they ended with a specific word, For that, you can use Right and If command together.

Question;

In the column “H” find all cells which ended with the word “God”.

For that you should use this formula;

IF(RIGHT(H4,3)=“God”,TRUE,FALSE)

specify this formula to other cells of the column H.

As you see this formula said;

In the cell “H4” from the right side in the first and second and third letter search and if you find the word “God” say TRUE and if you do not find say False.

In the first line, you can see the answer is False because we have a dot at the end of the cell and the first and second and third letter from the right in this cell is “od.”.

How to Use Search Command in the If Statement;

If you want to find a specific word in the sentence in one cell you can use “search” and “if” command together.

Pay attention when you use the “search” command, for you, it is not important, a capital letter in the word.

Question;

In the column “H” find all cells which contain the word “trust”.

For that you should use this formula;

IF(ISNUMBER(Search(“TRUST”,H4)),“YES”,“NO”)

In this formula, we said to search the word “trust” in the cell, if you find this word in the cell, write “YES”, and if you do not find write “NO”.

How to Use Find Command in the If Statement;

If you want to find a specific word in the sentence in one cell and it is important for you the capital letter in the word, you should use “Find” and “if” command together.

Question;

In the column “H” find all cells which contain the word “TRUST”.

For that you should use this formula;

IF(ISNUMBER(FIND(“TRUST”,H4)),“YES”,“NO”)

In this formula we said, find the word “TRUST” in the cell, if you find it write “YES” and if you do not find, write “NO”.

Here it is important for us, a capital letter in the word so we use the “Find” command.

As you see in the second cell we have the word “Trust” but because all the letters are not capital, the result of the “IF” statement is “False” and we have the word “NO” in front of that cell too.

How to write Nest Statement in Excel;

If we have an If statement inside another If statement, we say this formula Nest statement.

Here we are going to explain this subject with an example;

Example:

In this example, we have a monthly income of a company in the year 2018 and 2019.

• In the first step, we should calculate the annual income growth ratio in the years 2019 and 2018.

• In the second step, we should find the months of the year in which the growth rates are higher than 15% or lower than -15%.

• In the last step, we want to put “▲” sign near the cells with the growth rates are higher than 15% and “▼” sign near the cells with the growth rates are lower than -15%.

How to insert “▲” & “▼” Symbol in excel.

You can copy these symbol from internet pages or other Office Program like Microsoft Word and Paste them to the Excel.

The other way is inserting them from the symbol part of excel.

for that you should follow these steps;

• Select Insert tab and choose Symbol.

• In the Symbol dialogue choose Symbols tab, in the Font part choose “Arial” and in the Subset part click on the “Geometric shape”.

• Here you can find these symbols select them and click Insert.

Solution

For solving this question we need three extra columns for specifying 3 different formula.

At first, we should calculate the income growth ratio.

For that we should use from this formula;

we will specify this formula in column I.

((Income 2019 / Income) 2018 -1), this formula for our table in excel equal to

=E3/D3-1

Specify this formula to other cells of the column “I” too.

Now we need to find the cells in column “I” which their growth rate are higher than +15% or less than -15%.

For that we should use this formula;

=IF(OR(I5>15%,I5<-15%),I5,"")

In this formula, we said If the income growth rate is higher than +15% or less than -15%, write that value in the cell otherwise write nothing in the cell.

Now we should specify “▲” sign near the cells which their growth rates are higher than 15% and “▼” sign near the cells with the growth rate lower than -15%.

For that we need to use one If statement inside the other If statement or use nest statement.

For that we should use this formula;

=IF(I5>15%,$K$2,IF(I5<-15%,$K$3,""))

As you see in the picture “▲” sign is placed in the cell with the “K2” address and “▼” sign is placed in the cell with the “K3” address.

but we need to fix this cell because If these cells were not fixed when we specified this formula to other cells of the G Column, the address of these cells would change in the formula and we don’t want that.

for fixing this problem, we should select “K2” and press “F4” key in the keyboard or write “$k$2” in formula manually.

we need to repeat this action for “K3” cell too.

After that specify this formula to other cells of the column “G”.

Now you can see these sign in column “G” in front of our desired rate.