HOT TOPICS
How to use If Function in Excel, Example for Text, Numbers etc
In this blog you will learn how to build excel using If statement to test a condition and return one value if the condition is met and another value if the condition is not met.
You will also learn the syntax and common usage of the excel IF function and also take a close look at some examples.
IF FUNCTION IN EXCEL
The common syntax of the IF function is as follows IF(logical test, [value if true, value if false])
Logical test - The condition to test.
Value if true (optional) - The value to return when the logical test evaluates to TRUE, ie, the condition is met.
Value If False (optional) - The value to return when the logical test evaluates to false, ie the condition is not met this value must be set.
example = IF(B2>80, "Good", "Bad") In case you wish to return a value only when the condition is met or not met, otherwise nothing if nothing is the case then use an empty string (" ") example
= if (B2>80, "Good", " ")
using text if (B2 = "O+", "Blood group match", "Not match")
<> not equal to operator: the not equal to operator can be used to achieve the same thing.
If (c2<> "delivered", "call", "No")
IF STATEMENT WITH DATES
Unlike many other function, If does recognize dates in logical tests and interpretes them as mere text strings. In other words you cannot supply a date in the form of "1/1/2020" or "1/1/2020".
To make if function recognize date, you need to wrap it in Date value function.
=If(B2>Datevalue("7/18/2022"), "Coming Soon", "Completed") of course, there is nothing that would prevent you from entering the target date in a predefined cell (say E2) and referring to that cell just remember to lock the cell address with the $ sign to make it an absolute reference for instance =If(B2>$E$2, "coming soon", "completed" The formular evaluates the dates in column.
To compare a date with the current date use the today () function for example =If(B2>Today(), "Coming soon", "Completed")
LOGICAL EXPRESSION BLANK
Using the logical expression = (Equal to Blank) or <> "" (not equal to blank) example =IF(isBlank (A1), 0,1)
FORMULAR THAT RUNS ANOTHER FORMULA
Example If(E135>=400, 5%*E135, "No Discount"). Example 2 =If(E11>=400, 5%*E11, 10%*E11)
TO CHECK IF TWO CELLS CONTAIN THE SAME TEXT.
To check if two cells contain the same text including the letter case, make if formular case sensitive with the help of the exact function. for instance, to compare the password in A2 & B2 and returns "Match" (if the two strings are exactly the same "Do not Match")
Example =If (exact(A2,B2, "Match, "Don't Match")
If (exact(A3,B3, "Match, "Don't Match")
If (exact(A4,B4, "Match, "Don't Match")
TO COUNT CELLS
syntax is countif (Range, Criteria)
Example countif (A1:A16, "ngozi")
This scans the content of cell A1 to A16 which contains Ngozi and counts them.
countif (A1:A16, "AC13")
This scans the content of cell A1:A16 and checks if they contain the content in cell AC13, then counts them.
COMBINING Exact and Countif
=Sum(--Exact(A1:A16, "NGOZI")
This scans the content on cell A1:A16 and checks if they contain the upper case "NGOZI" only then counts them.
= Sum (--Exact(A1:A16, "AC13")
This scans the content on cell A1:A16 and checks if they contain the content in cell AC13 then counts them.
USING MULTIPLE IF STATEMENTS
