Tech2 Touch

Category: Excel Tips

Excel tutor on formulas, tips and tricks and VBA.

  • Maximum and Minimum Worksheet Functions in Excel

    There are many situation where we need to find the maximum and minimum in a range. Like many other worksheet functions, finding the maximum and minimum also there is a direct functions available. Let us have a quick look into the maximum and minimum excel worksheet functions.
    Finding Maximum in a range using the Excel worksheet function:
    As said earlier, we need to use max function to get the maximum in a range.
    The syntax of max worksheet function is
    =max(number1, number 2, number 3…….Number n)
    where number1, number 2, number 3…….Number in which we need to find the maximum value.
    Examples on MAX excel function:
    =max(23,18, 51, 49)  this will return 51
    =max(A1:A4)
    Finding Minimum in a range using the Excel worksheet function:
    The syntax of MIN function in excel is
    =min(number1, number 2, number 3…….Number n)
    Where number1, number 2, number 3…….Number n Number in which we need to find the maximum value.
    Examples on Excel MIN function:
    =min(23,18, 51, 49)  this will return 18.
    =min(A1:A4)
    We have seen how to find the simple minimum and maximum using the excel functions. But at times we need to find max in a range with a criteria. There is a conditional max function available.
    For finding the conditional max in range we need to use DMAX function.
    The syntax for DMAX is
    DMAX( database, field, criteria )
    Where
    database is the range of cells that you want to apply the criteria against.
    field is the column to find the largest number in. You can either specify the numerical position of the column in the list or the column label in double quotation marks.
    criteria is the range of cells that contains your criteria.

  • VLOOKUP in Excel Tips and Tricks

    We already learned on how to use VLOOKUP in Excel.  In my earlier different posts, i have shown, the different ways you can use vlookup functions. This post is the summarisation all the things which we have already discussed.

    VLOOKUP in Excel Tips and Tricks

    Here is the list of few VLOOKUP in Excel tips to use the excel worksheet function more effectively.

    1. Use named Ranges:  the vlookup function with normal range variables looks very complicated. To avoid this, use named ranges.  With named ranges, you can do dynamic ranges which saves lot of memory and by that, the time. You can read more dynamic ranges in excel here.
    2. 2 way or conditional vlookup: The default vlookup accepts only single criteria lookup. But using the match function, you use vlookup function which looks for the given value in row as well as in columns. The syntax is same as the normal vlookup apart from inserting match function instead of index number. The match function returns the reference number of the column. To learn more on this read my post on conditional vlookup.
    3. Handling error values in Excel: When the lookup value is not found, vlookup returns, #na. You can remove this by using iferror function. This will make your excel template more clean and easy to perform calculations
    =IFERROR(VLOOKUP(B16,A2:C6,2,FALSE),0)
    4. Use Wildcard characters for related lookup: You can use related lookup using the last argument as TRUE. but, it has a downside. The range should be in ascending order. The wildcard character will help in these situations. Below are the few examples.
    =VLOOKUP(“Galaxy*”,A2:C8,3,FALSE)- returns the value from the range which starts with Galaxy. If there are multiple Galaxy are there, then it will take the first one.
    =VLOOKUP(“????”,A2:C8,3,FALSE) this look for a value with the lookup value length is 4.
    Similarly you can use most of the wildcard characters in vlookup also.

    If you find these tips on VLOOKUP in Excel, or have any queries, feel free to leave feedback in the comment section below.

  • Creating conditional drop down list in Excel

    In my earlier post, I have shown you how to create a simple drop down list in Excel. That meets most of our day to day requirements. However, when we create a dashboard or a template, we need to create a dependent drop down list or a conditional drop down list in Excel. That second drop down content should be changed based first dropdown content. It is very is to implement. So let us explore the way to create conditional drop down list in EXCEL.

    Steps to Create conditional drop down list in Excel

    To create a conditional or a dependent drop down list you need to use basic HLOOKUP function if your lists are organised vertically or vlookup function if your list is organised horizontally.

    Let me you show you how to create a dependent or conditional drop down list with a simple example.

    We have three different mobile manufacturers name in the top and list of model names down the list in sheet1 as shown in the sample table below.

    First you define the lists with the name of the manufacturer. In this example, I have created the name ‘Samsung’ for the range A2: A10, ‘Nokia’ to the range B2:B10, ‘Sony’ to  C2:C10. Like this you can create any number of lists.  You create these named ranges by going to ‘Formulas->Define Name’.

    Now we need to create the first drop down list in sheet2 where we want to create the dependent conditional drop down.

    • The first drop down list is the simple one. Just go to Data->Data Validation->Data Validation.Under validation criteria, select list under ‘Allow’ and select the source as the headings of your lists. That is Sheet1, range A1:C1. Your first drop down list is ready.
    • Now the second drop down list is dependent list. To achieve this we use, INDIRECT function and HLOOKUP function.
    • Go to  Data->Data Validation->Data Validation. Select list and the source enter the formula as mentioned below and click Ok.
    • =INDIRECT(HLOOKUP($A$2,Sheet1!$A$1:$C$1,1,FALSE))
    • Your dependent drop down list shows now. As you change the first drop down, second drop down list changes.

    How this works

    The INDIRECT functions, tells the excel that, the source is a reference and not a value or text. HLOOKUP function returns the name of the group based on the first drop down. Similarly you can do it with VLOOKUP if your lists are organised in vertical lists.

  • Excel Tip To Calculate The Number of Work Days Between Two Dates

    There are many situation where we need to calculate the difference between two dates. We can get the no of days by subtracting the one date by one date. However, that gives the total days between these dates in which includes weekly offs and Holidays. To overcome this, excel provides a dedicated function called NETWORKDAYS.
    The Excel NETWORKDAYS function calculates the difference between two dates excluding weekend days(Saturday and Sunday).  You can even provide the list of holidays in between the two dates two excludes that many days. The holidays are different for different regions. So excel by default will not be able find which dates are Holidays. Because of this we need to give list of Holidays to get the exact working days excluding holidays and weekly offs.
    The Syntax of NETWORKDAYS Excel Function
    =NETWORKDAYS(Start_Date, End Date,[holidays])
    Where
    Start Date is the starting date and End Date is the closing Date or Finishing date.
    Holidays is the the range which contains the dates which falls under Holiday

    Example for NETWORKDAYS Function
    Let us take example in which we have 01-July-2013  in  Cell A1 and 31-July-2013 in cell B1. And the list of Holidays are kept in the range D1:D3. The formula to calculate the exact working days between these two days as mentioned below.
    =NETWORKDAYS(A1,B1,D1:D2) which gives result as 21. We have total 31 days between these days. In that we have 8 weekly offs and two Holidays. So the formula gives 31-8-2=21.

  • Format Cell using EXCEL function

    In EXCEL, we can format the numbers into different formats using the Format option. In some instances, we need to format the cell value which is a result of another formula. In other output from EXCEL format should be in the desired format. There is a EXCEL function called TEXT. This is useful for this. In this post we will learn the way to format cell using a EXCEL function TEXT.

    Format Cell Values using EXCEL Function

    We can use TEXT excel function to Format Cell in Excel. This method is useful when we need to implement formatting inside a EXCEl formula.

    Syntax of Excel TEXT Function

    =TEXT(value, format_text)

    Where
    Value is the number which we want to change the format.
    format_text is the required format.

    Examples of converting the format of values in Excel using formula

    We have a value 41479 in cell A1.
    =TEXT(A1,”dd-mmm-yyyy”)
    The above formula changes the format of the value in cell A1 to dd-mmm-yyyy format(24-Jul-2013).

    Similarly you can use any format in which you want to display the number. Below are some more example for the same.

    • =TEXT(A1,”#,##0″) this will give output as 41,479.
    • =TEXT(A1,”£#,##0;-£#,##0″) which gives output in the format £41,479.

    Like this you can use your own formatting using this excel formula. The biggest advantage of this excel function is that you can use this formula inside another formula or function to format the output.

    For example you want to concatenate date and a text . In Cell A1 we have “Today Date is” and in cell B1 we have 24-Jul-2013. Now if we use normal concatenation.
    =A1&B1 the output will look like this. Today Date is41479. So we need to use the text formula to convert the number in cell B1 to Date and then we can concatenate. The modified formula look like this.
    =A8&TEXT(B8,”dd:mmm:yyyy”) which shows the output as Today Date is24:Jul:2013.

    You can even use this formula with other excel formulas such as vlookup, hlookup. The formula to change the format of vlookup formula out is as mentioned below.
    =TEXT(VLOOKUP(B15,A13:B13,2,FALSE),”DD:MMM:YYYY”)

    Hope this helps to create your excel templates by using the Formula to Format Cell.