Posts

VLOOKUP using Approximate Match

/0 Comments/in /by

In a lot of cases, we use VLOOKUP to find exact matches, based on some kind of unique id. But there are many situations where we can perform VLOOKUP using Approximate Match. A classic case is using VLOOKUP to find a Grade for the students where marks are provided.

Suppose we are provided with Test scores of students and have been asked to find the grades for each students base on different slabs of scores. In such cases, we generally use nested IF and the formula goes too long.

We can replace the same with VLOOKUP using Approximate Match by using TRUE or 1 in the last argument that is [range_lookup].

vlookup-using-approximate-match

[range_lookup] is the most critical or we can say the most important argument of VLOOKUP. If we use TRUE, then it finds the closest minimum match from the Master Table.

The most important task to get the desired result is to prepare the table_array in a smart, VLOOKUP friendly format and in the sorted in ascending order.

Download Example Workbook

Generating a List of File Names

/0 Comments/in /by

This tip describes how to retrieve a list of file names in a folder and display them in a worksheet.This technique uses an Excel 4 XLM macro function in a named formula. It’s useful because it’s a relatively simple way of getting a list of file names into a worksheet – something that normally requires a complex VBA macro.

Start with a new workbook and then follow these steps to create a named formula:

  1. Choose Formulas Tab ➜ Define Name to display the New Name dialog box (CTRL + F3).
  2. Type FileList in the Name field.
  3. Enter the formula in the Refers To field =FILES(Sheet1!$A$1)
  4. Click OK to close the New Name dialog box.

filelist_name_manager

Note that the FILES function is not a normal Worksheet Function. Rather, it’s an old XLM style macro function that is intended to be used on a special macro sheet. This function takes one argument (a directory path and a file specification) and returns an array of file names in that directory that match the file specification. A normal worksheet formula cannot use these old XLM functions, but named formulas can. After defining the named formula, enter a directory path and file specification into cell A1.

For example:

  • F:\Future in Hands\Training\*  to display all files
  • F:\Future in Hands\Training\*xl* to display all Excel files

Then this formula displays the first file found: =INDEX(FileList, 1)

If you change the second argument to 2, it displays the second file found, and so on.

Below figure shows an example. The path and file specification is in cell A1. Cell A2 contains this formula, copied down the column: =INDEX(FileList,ROW()-1)

The ROW function, as used here, generates a series of consecutive integers: 1, 2, 3, and so on. These integers are used as the second argument for the INDEX function. When you change the directory in cell A1, the formulas update to display the new file names.

generating-a-list-of-file-names

Note

If you use this technique, you must save the workbook as a macro-enabled file (with an *.xlsm or *.xls extension).

 

Join multiple text quickly

/0 Comments/in /by

We sometime comes with the challenge to join multiple text in Excel. The first thing that comes into our mind is to use the CONCATENATE Function. The major challenge in using this function that we cannot select a continuous Range such as CONCATENATE(A1:A10). To get the result we need to specify all the cells individually.

To do this quickly and smartly, we can use TRANSPOSE with CONCATENATE Function. Below are the steps for doing this. Let say the cells you want to combine are in A1:A10.

  1. In a blank cell, where you want to concatenate all the values type =CONCATENATE(TRANSPOSE(A1:A10))
  2. Don’t press enter yet.
  3. Select the TRANSPOSE(A1:A10) portion and press F9. This replaces the TRANSPOSE(A1:A10) with its result.
  4. Now remove curly brackets { and }
  5. Press Enter

 

DATEDIF function

/0 Comments/in /by

DATEDIF calculates the difference between two dates and expresses the result in terms of months, days, or years.

The DATEDIF function, which isn’t documented in the Excel Help system, is one of the little Excel mysteries. Although the Excel 2000 Help system has an entry for DATEDIF, the function is not documented in earlier or later versions.

The syntax for the DATEDIF function is

=DATEDIF(StartDate,EndDate,Interval)

StartDate and EndDate are standard dates (or a reference to a cell that contains a date). The third argument, Interval, is a text string that specifies the unit of time that will be returned.

Valid interval codes are described in this list:

  • m:  The number of complete months between StartDate and EndDate.
  • d:  The number of days between StartDate and EndDate.
  • y:  The number of complete years between StartDate and EndDate.
  • ym:  The number of months between StartDate and EndDate. This interval excludes years, so it works as though the two dates are in the same year.
  • yd:  The number of days between StartDate and EndDate. This interval excludes years, so it works as though StartDate and EndDate are in the same year.
  • md:  The number of days between StartDate and EndDate. This interval excludes both month and year, so it works as though StartDate and EndDate are in the same month and the same year.

This function is majorly used for Age calculation. To calculate the current Age, you can use the below formula:

=DATEDIF(DOB,TODAY(),”y”) & ” years, “&DATEDIF(DOB,TODAY(),”ym”) & “months, “&DATEDIF(DOB,TODAY(),”md”) & ” days”

Where DOB is the Date of Birth. You can use a cell reference or manually write your DOB. Ex: “17-Jul-81″

This formula returns a text string, like this: 33 years, 8 months, 17 days

Count Unique records in a Range

/0 Comments/in /by

In some situations, you may need to count unique records in a range. The below figure shows an example. Column A has a list of cities, and the goal is to count the number of unique cities in the list. The formula in cell C3 returns 7, which is the number of unique cities.

count-unique-records

To get the result we need to use Array Formula. This formula is: =SUM(1/COUNTIF(A1:A10,A1:A10))

Press Ctrl+Shift+Enter after writing the formula to get the desired output.

WEEKDAY Function

/0 Comments/in /by

WEEKDAY Function tells you the day of week from a given date.

Syntax of Weekday formula: =WEEKDAY(Date)

Example 1: =WEEKDAY(“01-Apr-2014″) = 3 (3 for Tuesday)
Example 2: =WEEKDAY(TODAY()) = Current Day of week

WEEKDAY function returns the day number of a week as follows:

1 : Sunday
2 : Monday
3 : Tuesday
4 : Wednesday
5 : Thursday
6 : Friday
7 : Saturday

Insert Random Dates in Excel

/0 Comments/in /by

To insert a Random Date between 05-Jan-2014 and 30-Jun-2014, use the below formula. You can customize the formula as per your requirement.

=RANDBETWEEN(DATE(2014,1,5),DATE(2014,6,30))

Note: After you insert the formula you need to set the number format to Date, otherwise you’ll just see numbers.