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

Popular posts from this blog

IFERROR With VLOOKUP And LEN AND LENB FUNCTION

Combining the IFERROR Function Formula with Logical VLOOKUP Functions Formula IFERROR Function With VLOOKUP Function Formula Description : -  Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula. Use the IFERROR Function With VLOOKUP function to trap and handle errors in a formula Below Following Values if Error : - #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL! For Example A                          B                    C                         D Description         Value          Description       Value Lookup Product               #N/A            Product                0 Produ...
32 comments
Read more

Discount Formula in Excel

  Discount Formula in Excel To Discount Formula   an original price minus 5 is the same as Calculate to 95 of that value so if you have the Product Price (4585) in A2 use the Discount Formula Here the Below Example for Discount Formulas : -   5% Discount formula is =A2*0.95 10% Discount formula is =A2*0.90 15% Discount formula is =A2*0.85 20% Discount formula is =A2*0.80 25% Discount formula is =A2*0.75 30% Discount formula is =A2*0.70 40% Discount formula is =A2*0.60 50% Discount formula is =A2*0.50 Another Way to Calculate Discount Formula in Excel How to Find Discounted Amount in Excel Column A is Product Price, Column B is Discount Percentage, Column C is Discounted Amount and Column D is After Discount Amount     Discounted Amount Formula is  =A2*5%   After Discount Amount Formula is =A2-C2 Another Easiest Way to Calculate Discount F ormula  Col umn A is Product Pr ice, Col umn B is Discou...
21 comments
Read more
Excel Some Useful Shortcuts Excel Some Useful Shortcuts Shortcut key Action Menu equivalent comments Alt or F10 Menu Go to Menu bar Alt+’ Format Style dialog box Format the Style Alt+= AutoSum Sum Formaula Showing in Cell Alt+Down arrow Display AutoComplete list Displaying Auto Complete Data List Alt+F1 Insert Chart Insert the Chart Alt+F11 Visual Basic Editor Tools, Macro, Visual Basic Editor Alt+F2 Save As Save File to Save As Alt+F4 Exit File Exit Alt+F8 Macro dialog box Macro dialog box Tools Alt+Shift+F1 New worksheet Insert New Worksheet Alt+Shift+F2 Save Save the File Ctrl + Shift + L Filter Filter the Row Ctrl W Close Close the Open File Ctrl+- Delete Delete the Rows, Columns and Cells Right or Down Depends on selection Ctrl+! Format cells dialog box Font Tab Open in F...
20 comments
Read more