Excel Some of Useful Functions

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