Skip to main content
Excel Some of Useful Functions
How to Use IF Function Formula in Excel
Excel IF function Formula
IF
function Formula is one of the Excel's logical function Formula that
evaluates a certain condition and returns the value you specify if the
condition is TRUE, and another value if the condition is FALSE.
The syntax for Excel IF Function Formula is as follows:
IF(logical_test, [value_if_true], [value_if_false])
As you see, the IF function has 3 arguments, but only the first one is obligatory, the other two are optional.
Some List of Example :
Example : 1
* logical_test -
a value or logical expression that can be either TRUE or FALSE.
Required.In this argument, you can specify a text value, date, number,
or any comparison operator. For example, your logical test can be
expressed as or A1="Yes", A1<200, A1=5000 or A1>500.
* value_if_true -
the value to return when the logical test evaluates to TRUE, i.e. if
the condition is met. Optional.For example, the following formula will
return the text "Yes" if a value in cell A1 is greater than
5000: =IF(A1>5000, "Yes")
* value_if_false -
the value to be returned if the logical test evaluates to FALSE, i.e.
if the condition is not met. Optional.For example, if you add "No" as
the third parameter to the above formula, it will return the text "Yes" if a value in cell A1 is greater than 5000, otherwise, it will return "No": =IF(A1>5000, "Yes", "No")
Here the Below Screenshot Image
Screenshot Image Link
Example : 2
* logical_test -
a value or logical expression that can be either TRUE or FALSE.
Required.In this argument, you can specify a text value, date, number,
or any comparison operator. For example, your logical test can be
expressed as or A1=5000, B1=5000
* value_if_true -
the value to return when the logical test evaluates to TRUE, i.e. if
the condition is met. Optional.For example, the following formula will
return the text "TRUE" if a value in cell A1 And B1 Equal 5000: =IF(A1=B1,"TRUE","FALSE")
* value_if_false -
the value to be returned if the logical test evaluates to FALSE, i.e.
if the condition is not met. Optional.For example, if you add "FALSE as
the third parameter to the above formula, it will return the text "TRUE" if a value in cell A1 And B1 Equal 5000, otherwise, it will return "FALSE": =IF(A1=B1,"TRUE","FALSE")
Here the Below Screenshot Image
Screenshot Image Link
Example : 3
* logical_test -
a value or logical expression that can be either TRUE or FALSE.
Required.In this argument, you can specify a text value, date, number,
or any comparison operator. For example, your logical test can be
expressed as or A1=Saree, A2=Saree Blouse
* value_if_Without Blouse Piece - the value to return when the logical test evaluates to Without Blouse Piece, i.e. if the condition is met. Optional.For example, the following formula will return the text "Without Blouse Piece" if a value in cell A1 is Saree: ==IF(A1="Saree Blouse","With Blouse Piece",IF(A1="Saree","Without Blouse Piece"))
* value_if_With Blouse Piece - the value to return when the logical test evaluates to With Blouse Piece, i.e. if the condition is met. Optional.For example, the following formula will return the text "With Blouse Piece" if a value in cell A1 is Saree Blouse :=IF(A1="Saree Blouse","With Blouse Piece",IF(A1="Saree","Without Blouse Piece"))
Here the Below Screenshot Image
Screenshot Image Link
Combining the IF function Formula with Other Logical Functions Formula
need
to specify more complex conditions when writing your formula in Excel.
You can combine the IF function Formula with other logical functions
Formula such as AND, OR, etc. Let's explore this further.
Combining the IF Function With AND function Formula
The
IF function can be combined with the AND function Formula to allow you
to test for multiple conditions. When using the AND function Formula,
all conditions within the AND function Formula must be TRUE for the
condition to be met. This Function Formula comes in very handy in Excel
formulas.
Here the Below Screenshot : 1
Screenshot Image Link
IF((AND Formula 1 is =IF(AND(A2>="Saree",B2>40000), "Party", "Casual)
Here the Below Screenshot : 2
Screenshot Image Link
IF((AND Formula 2 is =IF(AND(B2>=25000,B2<=50000), "Great", "Average")
Combining the IF Function With OR function Formula : -
The
IF function Formula can be combined with the OR function Formula to
allow you to test for multiple conditions. But in this case, only one or
more of the conditions within the OR function needs to be TRUE for the
condition to be met.
Here the Below Screenshot : 1
Screenshot Image Link
IF(OR Formula 1 is =IF(OR(A2="Saree",A2="Dress Material"), "Ethnic Wear", "Western Wear")
Here the Below Screenshot : 2
Screenshot Image Link
IF(OR Formula 2 is ==IF(OR(A2="Saree",B2>=5000), 999, "N/A")
Combining the INDEX Function Formula with MATCH Function Formula In Excel
INDEX Function Formula With MATCH Function Formula
need
to specify more complex conditions when writing your formula in Excel.
You can combine the INDEX function Formula with MATCH functions Formula
This
Function Formula is Most Useful to Reverse LOOKUP Find, Look like
Similar to VLOOKUP Function Formula, VLOOKUP Find Right Side Column
Value and INDEX With MATCH Function Formula Find Left Side Column Value
For Example :
A B C D
Qty Product Name Product Name Qty (Result)
5854 Saree Tunic 552
8582 Dress Material Shirt 2524
2854 Tops Saree 5854
2524 Shirt Dress Material 8582
552 Tunic Tops 2854
INDEX With VLOOKUP Function Formula is
=INDEX(A:A,(MATCH(C:C,B:B,0)))
=INDEX(A:A,(MATCH("Shirt",B:B,0))) For Use Only one Individual Data Find
How to Use VLOOKUP Function Formula in Excel
VLOOKUP
Function Formula is one of the lookup and reference functions, when you
need to find things in a table or a range by row.
For
example, look up an Product Quantity, Product Details or Product
SKU, find any phone number by looking up Their last name (just like a
telephone book Directory).
The
secret to VLOOKUP Function Formula is to organize your data so that
the value you look up (Product Quantity) is to the left of the return
value you want to find (Product Quantity's).
VLOOKUP Function Formula Syntax : -
=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
For Example : -
Argument name Description
lookup_value (required) Which value you want to look up. Which value you want to look up must be in the first column of the range of cells you specify in table-array (Lookup Value is C)
For example, if table-array spans cells A:B, then your lookup_value must be in column C. See the below Details. Lookup_value can be a value or a reference to a cell
table_array (required) The range of cells in which the VLOOKUP will search for the lookup_value and the return value (Table Array is A:B)
The first column in the cell range must contain the lookup_value (for example, A:B) The cell range also needs to include the return value
col_index_num (required) The column number starting with 1 for the left-most column of table-array that contains the return value (Co_index No.2)
range_lookup (optional) A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match: (Range Lookup optional is FALSE)
* FALSE searches for the exact value in the first column
* TRUE assumes the first column in the table is sorted either numerically or alphabetically, and will then search for the closest value. This is the default method if you don't specify one.
A B C D
Product Name Qty Product Name Qty (Result)
Saree 5854 Tunic 552
Dress Material 8582 Shirt 2524
Tops 2854 Saree 5854
Shirt 2524 Dress Material 8582
Tunic 552 Tops 2854
VLOOKUP Function Formula : -
=VLOOKUP(C:C,A:B,2,FALSE)
=VLOOKUP(C2,A:B,2,FALSE)
=VLOOKUP("Tops",A:B,2,TRUE) For Use Only one Individual Products Quantity Result Find
How to Use SUM Function Formula Using in Excel
SUM function Formula in Excel
The SUM function Formula is one of the Most Useful function,
Syntax: SUM(number1,[number2],...)
For example 1: Adding + Count
A B C (Result)
2111 25447 27558
Adds the value in cells A1 and B1. The result is 27558
Formula : =SUM(A1+B1)
=SUM(A1:B1)
For example 2: Deducting - Count
A B C (Result)
25447 2111 23336
Adds the value in cells A1 and B1. The result is 23336
Formula : =SUM(A1-B1)
Example 3
A B C (Result)
15 25 40
Formula =SUM(A2,A3)Adds the value in cells A1 and B1. The result is 40.
How to Use COUNT Function Formula in Excel
The COUNT Function Formula is counts the number of cells that contain numbers, and counts numbers within the list of arguments.
Use the COUNT function
to get the number of entries in a number field that is in a range or
array of numbers. For example, you can enter the following formula to
count the numbers in the range A1:A20:
For Example : 1
A
582
258
2458
584
585
655
698
4785
145
1256
2256
2545
2564
2548
1525
Result = 15
COUNT Function Formula
=COUNT(A1:A15)
For Example : 2
A B C (Result)
582 252 2
258 352 2
2458 452 2
584 552 2
COUNT Function Formula
=COUNT(A1:B1)
How to Use IF Function Formula in Excel
Excel IF function Formula
IF
function Formula is one of the Excel's logical function Formula that
evaluates a certain condition and returns the value you specify if the
condition is TRUE, and another value if the condition is FALSE.
The syntax for Excel IF Function Formula is as follows:
IF(logical_test, [value_if_true], [value_if_false])
As you see, the IF function has 3 arguments, but only the first one is obligatory, the other two are optional.
Some List of Example :
Example : 1
* logical_test -
a value or logical expression that can be either TRUE or FALSE.
Required.In this argument, you can specify a text value, date, number,
or any comparison operator. For example, your logical test can be
expressed as or A1="Yes", A1<200, A1=5000 or A1>500.
* value_if_true -
the value to return when the logical test evaluates to TRUE, i.e. if
the condition is met. Optional.For example, the following formula will
return the text "Yes" if a value in cell A1 is greater than
5000: =IF(A1>5000, "Yes")
* value_if_false -
the value to be returned if the logical test evaluates to FALSE, i.e.
if the condition is not met. Optional.For example, if you add "No" as
the third parameter to the above formula, it will return the text "Yes" if a value in cell A1 is greater than 5000, otherwise, it will return "No": =IF(A1>5000, "Yes", "No")
Here the Below Screenshot Image
Screenshot Image Link
Example : 2
* logical_test -
a value or logical expression that can be either TRUE or FALSE.
Required.In this argument, you can specify a text value, date, number,
or any comparison operator. For example, your logical test can be
expressed as or A1=5000, B1=5000
* value_if_true -
the value to return when the logical test evaluates to TRUE, i.e. if
the condition is met. Optional.For example, the following formula will
return the text "TRUE" if a value in cell A1 And B1 Equal 5000: =IF(A1=B1,"TRUE","FALSE")
* value_if_false -
the value to be returned if the logical test evaluates to FALSE, i.e.
if the condition is not met. Optional.For example, if you add "FALSE as
the third parameter to the above formula, it will return the text "TRUE" if a value in cell A1 And B1 Equal 5000, otherwise, it will return "FALSE": =IF(A1=B1,"TRUE","FALSE")
Here the Below Screenshot Image
Screenshot Image Link
Example : 3
* logical_test -
a value or logical expression that can be either TRUE or FALSE.
Required.In this argument, you can specify a text value, date, number,
or any comparison operator. For example, your logical test can be
expressed as or A1=Saree, A2=Saree Blouse
* value_if_Without Blouse Piece - the value to return when the logical test evaluates to Without Blouse Piece, i.e. if the condition is met. Optional.For example, the following formula will return the text "Without Blouse Piece" if a value in cell A1 is Saree: ==IF(A1="Saree Blouse","With Blouse Piece",IF(A1="Saree","Without Blouse Piece"))
* value_if_With Blouse Piece - the value to return when the logical test evaluates to With Blouse Piece, i.e. if the condition is met. Optional.For example, the following formula will return the text "With Blouse Piece" if a value in cell A1 is Saree Blouse :=IF(A1="Saree Blouse","With Blouse Piece",IF(A1="Saree","Without Blouse Piece"))
Here the Below Screenshot Image
Screenshot Image Link
Combining the IF function Formula with Other Logical Functions Formula
need
to specify more complex conditions when writing your formula in Excel.
You can combine the IF function Formula with other logical functions
Formula such as AND, OR, etc. Let's explore this further.
Combining the IF Function With AND function Formula
The
IF function can be combined with the AND function Formula to allow you
to test for multiple conditions. When using the AND function Formula,
all conditions within the AND function Formula must be TRUE for the
condition to be met. This Function Formula comes in very handy in Excel
formulas.
Here the Below Screenshot : 1
Screenshot Image Link
IF((AND Formula 1 is =IF(AND(A2>="Saree",B2>40000), "Party", "Casual)
Here the Below Screenshot : 2
Screenshot Image Link
IF((AND Formula 2 is =IF(AND(B2>=25000,B2<=50000), "Great", "Average")
Combining the IF Function With OR function Formula : -
The
IF function Formula can be combined with the OR function Formula to
allow you to test for multiple conditions. But in this case, only one or
more of the conditions within the OR function needs to be TRUE for the
condition to be met.
Here the Below Screenshot : 1
Screenshot Image Link
IF(OR Formula 1 is =IF(OR(A2="Saree",A2="Dress Material"), "Ethnic Wear", "Western Wear")
Here the Below Screenshot : 2
Screenshot Image Link
IF(OR Formula 2 is ==IF(OR(A2="Saree",B2>=5000), 999, "N/A")
Comments
Post a Comment