Tech2 Touch

Category: Advanced Excel Formula

  • Excel If Function – Syntax And Examples

    Excel IF function is one of my favorite function in Excel. It is very useful and very simple to use. It does wonders when you want to create some complicated excel Formulas. You can extend the functionalities of IF function by nesting it or using along with other Excel Functions such as VLOOKUP, AND, OR etc.
    How Excel IF Function Works?
    Excel IF Function  checks for a condition(Criteria) and returns a value if the condition is met or returns another value if the criteria is not met. The criteria can be anything such as comparison, text check or error check etc. You can use nested IF functions upto 98 times in a excel worksheet.

    Syntax of Excel IF Function

    =if(condition, value if the condition is met, value if condition is not met)

    Examples:
    =if(A1>10, “Greater Than 10”, “Less than 10”)

    The Above IF formula returns Greater than 10 if the cell A1 has the value greater than 10 else returns Less than 10.

    Using IF function with AND

    As said earlier, you can use AND function inside IF  while you want a value to be written provides multi conditions were met. AND function returns TRUE if and only if  all the conditions are TRUE.For example, you have a value in cell A1. You want to check whether the number is in between 10 to 15. You can use the IF function as mentioned below.
    IF(AND(A1>10,A1

    Using IF function with OR

    IF and OR is used when you want to check any of the conditions is met. The OR Function returns true if any of the condition is met. For example you have want to check out of range if the number in cell A1 is less than  0 or greater than 100. You can use IF function with OR to calculate this as mentioned below.
    IF(OR(A1>100,A1

    Using Nested IF Function

    Using IF functions inside another IF function is called as nested IF functions. It is useful when you want to dig into the more detailed checks. For example you want to check if the number you have entered is a negative or positive in the cell A1.  First we need to check whether the entered value is number. If it is a number, then we need to check whether it 0. Then we need to check whether it is greater than 0 or less than 0 to find out the value entered is positive or negative. The nested IF function in Excel looks like as mentioned below.
    =IF(ISNUMBER(A1), IF(A1=0,”NIL”,IF(A1>0,”Positive Number”,”Negative Number”)),”It is not a Number”)
    The above uses ISNUMBER function to check for the whether the entered value is number or not.
    To learn how to use IF function with vlookup read my post on how to do vlookup on different ranges based on criteria.

  • Excel Vlookup Formula with IF Function-Condition Based vlookup

    I have already written on basic vlookup function and conditional vlookup function. In this Excel tutor, i will show you how you can use vlookup function along with IF function. Recently i came across a situation where i need to use different vlookup formula when there is a different scenarios. So i thought even this one will help you also. This excel tips guide will help you on how to perform different range conditional vlookup in excel.

    Using VLOOKUP formula with Nested IF function

    Actually, i came across this formula, while i was working on a annual salary revision data for a company. There it had all the countries employees with different grades and bands in one sheet. I have been provided with a grid which has increment amount for each grade, grade upgrade and promotion for different countries.  The employee data has country details, previous grade, current grade and the band.  One way is to split the employee data into three different sheets and performing. But i had a restriction on that. I should do for all countries in one sheet. Below is the steps I have done.
    Nested IF and Vlookup Formula Example
    1. I have created one more table for the Proposed Salary increment Grid. A unique id created by concatenating the previous grade, current grade and Band with the help of separating operator !. Please refer the below excel sheet. From A1: F12 contains original data table. From H2: K 12, the calculated salary increments for the different scenarios.
    2. Then created the unique code in the employee sheet too with the same logic of concatenating  previous grade, current grade and Band with the help of separating operator !. In the example, employee data is in A15 to G21. Added unique code in Column G.
    3. Now, in the increment amount field column F17, inserted the vlookup formula with Nested If function as mentioned below.

    =IF(B17=”India”,VLOOKUP(G17,$H$4:$I$12,2,FALSE),IF(B17=”US”,VLOOKUP(G17,$H$4:$J$12,3,FALSE),VLOOKUP(G17,$H$4:$K$12,4,FALSE)))

    4. Copied the same  formula to all the employees. The employee sheet has the increment amount for all the employees based on their grade upgradation or promotion and their countries.

    The above logic is simple, Tested the condition and while the condition is met, executed one set of vlookup formula while that condition is not met, tested one more criteria and ran different set of vlookup formula and so on.  By default you can Nested if for 7 times. For using IF formula more than 7 times, you need to take help of Excel VBA custom function.

    Share your thoughts on the same. If you have any better solution for this or if you need any clarification on this, share it in the comment section below.

  • Using VLOOKUP Formula in Excel effectively using MATCH Worksheet function

    In my earlier Excel tutorial post, we have learned how to use vlookup function. In this post, I will show you simple tweak through which you can make VLOOKUP function more effective. This will be useful for you when you want to map huge data.

    For example you have base table with more than 20 or 30 columns. From that, you need to fetch selected 5 or 6 columns in different part of the sheet or in different sheet itself. For doing this we need to use MATCH worksheet function along with VLOOKUP function.

    Using Excel VLOOKUP with Match Formula:

    To make this simple I am taking a small table which has 4 columns for better understanding.  From there we need to fetch only second column data and the 4 th column data.
    Excel VLOOKUP Tips and Tricks
    So i am using the VLOOKUP formula as mentioned below.
    =VLOOKUP($A9,$A$1:$D$6,MATCH(B$8,$B$1:$D$1,1)+1,FALSE) in column B9 and copy the same in C9, B10 and C10 also. In cell B9 it returns 31% which is the % of Commision of Banana from the table and in C9 it returns 120 which is quantity. Similarly, it returns 15% and 50 in B10 and C10 respectively.
    Explanation on the Above VLOOKUP formula with MATCH function:
    The lookup value column is made constant and the data range both rows and columns made constant. So where ever we paste the data, the lookup range should be the same and lookup value will be on the same column but it may be in different rows as we are not made row index constant.
    The third argument of vlookup,  ‘index_number’ which refers to the column from where vlookup should fetch the data, we have used MATCH  function. The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. In our example, match function returns relative position of the required column header in the headers of the our main database headers. The MATCH function position number starts from 0 and VLOOKUP starts with 1. So we are adding 1 to the relative position returned by MATCH function.
    As I said earlier, this is the one of  simple yet very useful combination of  formulas in a real time excel profession. I have faced many situation in my professional life, where it takes a huge time if we use classic vlookup function to fetch few data from a huge database. This saves our precious time also accuracy as there is very less chances for the error. Do let me know, if this Excel tip helpful for you and also if you face any problem using this tip.

  • Counting Non Duplicate Entries in a Range in Excel

    When we are doing analysis in excel or preparing the reports, we will face situations where we need to calculate the unique entries. There is no direct function in Excel to calculate only non duplicate entries in a Range. However this can be achieved through array functions of Excel. Below is the example through which we will learn to use Excel function to calculate non duplicate entries count.

    Count non duplicate values

    The above example has list of gadgets in Column A2 to A10. However there are duplicates in List. Our goal is calculate unique number of Gadgets. So the formula to count non duplicate entry is
    =SUM(1/COUNTIF(A2:A10,A2:A10)) which returns 5. i.e out of 9 entries only 5 items are unique.
    Note: For using array functions, press Ctrl+Shift+Enter(Not Just Enter). Excel surrounds the formula with braces to remind you that it is a array formula.
    This formula is first used by David Hager and then passed around on various web sites and newsgroups. The  above array formula works fine unless the range contains one or empty cells. To overcome this short fall we should use below formula with new IFERROR function.
    =SUM(IFERROR(1/COUNTIF(A1:A10,A1:A10),0))
    The preceding formula work with both values and text. If the range contains only numeric values or blank cells(But no text), we can use the below mentioned formula(This is not array formula) to count the number of non duplicate or unique values.
    =SUM(N(FREQUENCY(A1:A10,A1:A10)>0))