Learn EXCEL advanced formulas and functions


Télécharger Learn EXCEL advanced formulas and functions

★★★★★★★★★★3.5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

Télécharger aussi :


Checking for Messages in Cells

Blair has a worksheet divided into two areas: data entry and data verification. The data verification area consists of formulas that check entries using IF statements. If a problem is located, a text message is displayed in a cell in the verification area, otherwise the formula returns a blank. The following is a typical verification formula:

=IF(A1<>5,"Value in A1 is not 5")

The problem is that the data verification area can be quite large, which means it is easy to miss one of the text messages. Blair wondered if there was a way to create a formula that examined the data verification area and returned a single message if there were any other messages in the area.

There are a number of different ways that this problem can be approached. If the data verification area is contiguous, then a simple array formula will do the trick. Enter the following in any empty cell on the worksheet:

=SUM((LEN(ValRange)>0)*1)

Make sure that ValRange is replaced with the range of cells in the data validation area. Also, make sure you enter the formula by pressing Shift+Ctrl+Enter (to denote it is an array formula). The formula returns a value that indicates how many cells in the range have a length that is greater than 0. In other words, it counts the number of cells that have messages visible.

If you prefer to not use an array formula, you can accomplish the same result by using the following regular formula:

=SUMPRODUCT((LEN(ValRange)>0)*1)

The result, again, is the number of cells that have a length greater than 0. Another approach is to use some of the COUNT functions provided by Excel:

=COUNTA(ValRange) - COUNTBLANK(ValRange)

This formula counts the number of cells in the range, and then subtracts the number of blank cells in the range. The result is the number of cells that are non-blank, or those that are displaying messages. A different formulaic approach can be used to determine a simple yes/no response:

=IF(COUNTIF(ValRange,"?*"),"","No ") & "Verification Messages"

If there are no messages in the ValRange, the formula returns "No Verification Messages." If there are messages, it strips the "No" and simply returns "Verification Messages."

It would also be a good idea to apply conditional formatting to your data verification area. While the formulas discussed so far will tell you if there are messages, it won't highlight where those messages are--conditional formatting can pinpoint each message. Select all the cells in the area that contain formulas, and then use conditional formatting to check the length of those cells. If the length is greater than 0, the cell could be formatted to show a red background. This will make any messages in the data verification area much harder to miss when scrolling through the worksheet.

If you are in the mood to completely redesign your worksheet, a more powerful approach would be to do away with the data verification area. You can achieve the same results (checking what is in the data entry area) by using data validation for each of the entry cells. Set up properly, data validation would make sure that the user entered acceptable values into each cell, removing the need for much of the data validation area.

Comparing Lists for Duplicates

Imagine for a moment that you have a worksheet that contains lists of part numbers. On one worksheet you have a list of part numbers, and on another worksheet you have a similar list. The lists are not identical, however, and you want to determine if a particular part number on one list also appears on the other.

One solution is to somehow combine the lists, but add some sort of indicator as to which original list the particular part number came from. This approach (or a variation thereon) is, in fact, the approach taken by many Excel users.

What if you don't want to combine the lists, however? In this case, there is a very easy way to do the comparison. Follow these steps:

1.   Make sure there is a blank column just to the right of each list of part numbers on each worksheet.

2.   Select the part numbers on the first worksheet and give them a name such as "PartList1". (Use Insert | Name | Define to assign the name to the list.)

3.   Select the part numbers on the second worksheet and give them a name such as "PartList2".

4.   Assuming that the first part number on the first worksheet is in cell A2, enter the following formula in cell B2:

=ISNUMBER(MATCH(A2,PartList2,0))

1.   Copy the formula down so that a copy appears to the right of each part number on the first worksheet.

2.   Repeat steps 4 and 5 on the second worksheet, but use the following formula:

=ISNUMBER(MATCH(A2,PartList1,0))

When you are done, either TRUE or FALSE will appear to the right of each part number on each worksheet. If TRUE appears, the associated part number appears on the other worksheet. If FALSE appears, then the part number is unique and does not appear on the other worksheet.

Another approach is to use an array formula to do the comparisons. You could follow the same steps shown above, but use the following formula in step 4 (and PartList1 variation in step 6):

=OR(EXACT(A2,PartList2))

Since this is an array formula, you would enter it by using Shift+Ctrl+Enter. The result is the same TRUE and FALSE designation described above.

Regardless of which formula approach you use, you can use the AutoFilter capabilities of Excel to limit what is shown on either worksheet. If you filter to show only the FALSEs, you will have a list of all unique part numbers. If you filter to show TRUEs, then you will have a list of duplicates.

Comparing Workbooks

Krishna Kumari asked if there was a way to compare the contents of two Excel workbooks. Unfortunately, there is no built-in comparison feature, as there is in Word to compare two documents. There are third-party programs available that can help you out, and a quick search of the Web can help to locate such programs.

Depending on your needs, there can be an easier way. If the worksheets in each workbook are laid out the same, and you just want to find differences between values in the cells of each worksheet, then you can use formulas to compare worksheets. Try the following steps:

1.   Create a new workbook called .

2.   In cell A1 of the first worksheet in , enter the following formula:

=IF([]Sheet1!A1<>[]Sheet1!A1,"Different","")

3.   Copy the formula from A1 into all the other cells that represent the range you want to compare. For instance, if you want to compare A1:G12 in both worksheets, then you would copy the formula from A1 into the full range of A1:G12.

These steps assume that the worksheets you want to compare are both named Sheet1, and they are in and , respectively. If you have other sheets in and to compare, you can use similar formulas in other sheets of .

When done, any cell that has the word "Different" in it represent cells that are different in the ranges being compared. Thus, if C7 had "Different" in it, then there is a difference between the cell C7 of Sheet1 in and cell C7 of Sheet1 in .

If you are comparing only numeric values between the two worksheets, you could use a different formula in step 2, above:

=[]Sheet1!A1-[]Sheet1!A1

The result is a worksheet that subtracts the values in one workbook from the other, which results in the numeric differences.

Problem:

Counting the number of unique numeric values or unique data in List1, disregarding blank cells.

Solution1:

To count the number of unique values use the SUM, IF, and FREQUENCY functions as shown in the following formula: = SUM(IF(FREQUENCY(A2:A13,A2:A13)>0,1))

Solution 2:

To count the number of unique data use the SUMPRODUCT and COUNTIF functions as shown the following formula:

=SUMPRODUCT((A2:A13<>"")/COUNTIF(A2:A13,A2:A13&""))

 

In the above screenshot, in the first formula, change N to IF.

Counting Unique Numeric Values or Unique Data in a List

Problem:

Counting the number of unique numeric values or unique data in List1, disregarding blank cells.

Solution1:

To count the number of unique values use the SUM, IF, and FREQUENCY functions as shown in the following formula: = SUM(IF(FREQUENCY(A2:A13,A2:A13)>0,1))

Solution 2:

To count the number of unique data use the SUMPRODUCT and COUNTIF functions as shown the following formula: =SUMPRODUCT((A2:A13<>"")/COUNTIF(A2:A13,A2:A13&""))

->David has a worksheet in which there is a list of countries. This list, in cells A1:A100, can contain duplicates. David wants to determine the number of unique countries in the list.

There are several ways you can go about deriving a count, without resorting to using a macro. The method you should use depends on the characteristics of the data in the list. A good place to start, however, is to define a named range that represents the list of countries. In the following examples, it is assumed that the range is named Countries. (Catchy name, huh?)

If the list contains only text entries, and does not contain any blank cells, then the following will provide a count:

=SUM(1/COUNTIF(Countries,Countries))

This should be entered as an array formula, by pressing Ctrl+Shift+Enter. If the list contains blank cells, then the formula becomes a bit more complex. The following long array formula will work if there are blanks:

=SUM(IF(FREQUENCY(IF(LEN(Countries)>0,MATCH(Countries,Countries,0),""),

IF(LEN(Countries)>0,MATCH(Countries,Countries,0),""))>0,1))

This approach--using the FREQUENCY function--is fully recounted in the Microsoft Knowledge Base:

If you prefer to not use array formulas (for whatever reason), you can utilize a blank column to the right of your list. This column will contain regular formulas that indicate if the value to its left is unique in the list or not. The first time a value appears, the formula returns the number 1. On each subsequent appearance of the same value, the formula returns a 0. Start by sorting your list, then place the following formula in cell B1:

=IF(ISNA(VLOOKUP(A2,$A$1:A1,1,)),1,0)

Just copy the formula from B1 to the range B2:B100. With these results in place, you can easily sum column B and have a count of the unique values in the list.

Delete Duplicates in Microsoft Excel

Sort your sheet by the column in which you're trying to find duplicates. For our example formula, our data is in column A.

In a spare column (we're using column B), use the following formula:

=IF(A1=A2,1,0)

Copy the formula down, using the fill handle.

Click anywhere in your data, and hit Data?Filter?Autofilter.

 

From the Autofilter dropdown in your spare column (we're using column B), choose 1. You'll see only those rows that returned a 1 as a result of the formula above, which means they're all duplicates.

 

Select all the rows you see, and delete them. Turn off the Autofilter by going to Data?Filter and uncheck Autofilter. Delete the column in which you typed your formula.

Easily Duplicate A Worksheet

Many times you will need to create a workbook that is made up of two or more identical sheets. Many Excel users create each worksheet from scratch. Others, insert a new worksheet and copy and paste using the original worksheet. The problem with this is that the print settings have to be manually duplicated - which can often be tedious and frustrating.

You can save yourself a lot of work by properly designing the first worksheet and then simply duplicating it (print settings and all) by holding down the CTRL key while dragging the sheet tab of the original. As you drag, you will notice a small sheet icon with a plus sign (indicating copying) and a small black arrow (indicating where the duplicated sheet will be inserted). Let up on the mouse button and CTRL key and you will have an identical copy of your original sheet.

When you have two large tables of data in an Excel Workbook you may need to compare the tables to try and spot the differences. Below we will look at two very easy ways this can be done which will save you hours of manual work.

For both examples I will use the two ranges shown in the screen shot below. Note that I have bolded the cells in the second table that are not the same as their counterpart in the first table  for easier viewing.

 

Method 1 - Using True or False

The first method involves entering a simple formula in another identically sized and shaped range. The best part of this method is that we can add the formulas in one step without having to copy and paste.

1.    Using the above ranges to compare select the range E1:G7 starting from cell E1. This ensures that E1 is the Active cell in the selection.

2.    With this range selected click in the Formula Bar and type in: =A1=A9

3.    Now enter this formula by pushing Ctrl+Enter at the same time

You should now see the range E1:G7 filled with True (the same) and False (not the same).

Method 2 - Using Conditional Formatting

The second method is often the preferred way as it is easier to make any needed changes once the comparison has been done.

1.    Using the above ranges to compare, select the range A1:C7 stating from cell A1. This ensures that A1 is the Active cell in the selection.

2.    With this range selected, go to Format>Conditional Formatting

3.    Select Formula Is and then type the formula =NOT(A1=A9)

4.    Click the Format button and choose the format to mark differences with. I have used an orange background.

 

Now simply click OK and all your differences will be formatted according to the format you have chosen. When or if you make any changes the cells format will automatically revert back to normal if the cell content is the same as the cell in the other table.

If you need to compare ranges or Worksheets from different Workbooks take a look atExcel Compare Workbooks

Filtering Columns for Unique Values

It is not unusual to acquire or develop data tables that have duplicate values in a column. If you want to see only the unique values, without the duplicates, you want to filter your data table. Excel makes this rather easy for must scenarios. For instance, let's say you have a data table in which you have part numbers in column A. If you want to filter the list so you see only unique part numbers, you can follow these steps:

1.   Select one of the cells in the list of part numbers.

2.   Choose Filter from the Data menu, and then choose Advanced Filter. Excel displays the Advanced Filter dialog box. (Click hereto see a related figure.)

3.   I always like to choose the Copy to Another Location option.

4.   In the Copy To field, specify the cell where you want the list of unique, filtered values to be copied.

5.   Make sure the Unique Records Only check box is selected.

6.   Click on OK.

How To Use Excel's AutoFilter

Excel has some great tools that allow you to do amazing things with your data. The sad thing is that most Excel users spend years working with Excel without ever knowing they even exist.

One such tool is Excel's AutoFilter. If you work with lists of data in Excel (and who doesn't?), AutoFilter is essential.

I'll cover the basics here.

First you must have a list. The first row must contain headings. The list can be any number of columns wide and any number of rows high. There should be no blank rows in the list. There should be a blank column to the right and a blank row to the bottom of the list. If the list doesn't start in row 1 there must be a blank row above the headings. If the list doesn't start in column A, there should be a blank column to the left of the list.

To get started you need to turn on AutoFilter. Select a cell in your list and from the Data menu select Filter, AutoFilter. You will see a dropdown arrow appear beside each column heading in your list.

Selecting either dropdown arrow will present a list you can choose from to filter your data to show just the rows that match the criteria you select.

All other rows are temporarily hidden. You can further filter your list by selecting another dropdown arrow.

Notice that blue dropdown arrows identify the columns you have applied filters to and blue row numbers identify the rows that meet the filter criteria..

To remove the filter applied to a column, click on that dropdown arrow and select (All) from the list.

To remove the filters applied to all columns, from the Data menu select Filter, Show All.

If you want to do further work with your filtered data, you can copy and paste it somewhere else.

To calculate amounts for your filtered data, add a SUBTOTAL formula two rows below your list in the column you wish to calculate. Do this before applying any filters. Your subtotal formula will look like =SUBTOTAL(9,D1:D50) where D1 is the first row in your list and D50 is the last row in your list. Now, when you apply filters to your list, the subtotal formula will automatically calculate the total for only those records matching your filter criteria.

(Bonus Excel Addict Trick: For ad hoc analysis of your data, instead of putting the subtotal formula below your data, insert a couple of rows above your data and add the subtotal formula there. Then you won't have to scroll all the way to the bottom after applying each filter.)

This is powerful!

To remove the filters applied to all columns and turn off AutoFilter, from the Data menu select Filter, AutoFilter.

Converting Units

Fortunately, Excel includes a function that will handle many different unit conversions for you. The CONVERT function is part of the Analysis ToolPak, and will handle dozens of conversions. The syntax for the function is as follows:

CONVERT(value, "from", "to")

You simply supply the value you want to convert, along with an abbreviation for the units you are converting from and to. For instance if you wanted to find out the equivalent of 300 BTUs when you convert to calories, you would use the following:

CONVERT(300, "BTU", "c")

The number of different conversions that can be handled by CONVERT is quite impressive, indeed. In fact, the list is so long that it cannot be included here. You can perform conversions in the areas of weight, volume, distance, time, pressure, energy, force, power, magnetism, and a few others. A complete list can be found in the Excel on-line Help system. (Simply search for "CONVERT worksheet function.")

You should note that if the CONVERT function does not work on your system, it means you have not installed or enabled the Analysis ToolPak. To enable it, follow these steps:

1.   Choose Add-Ins from the Tools menu. This displays the Add-Ins dialog box. (Click hereto see a related figure.)

2.   Make sure the Analysis ToolPak option is selected.

3.   Click on OK.

If you did not see an Analysis ToolPak option in step 2, it means that you did not install the option when you first installed Excel. You can rerun the Excel Setup program and choose to install the option. You must then enable the addin, and you can use the function.

Calculating Week-Ending Dates

here are two formulas you can use in order to calculate your week-ending dates. Let's assume, for the sake of this example, that your year is stored in cell A1. You could then figure out the first Saturday of the year by using this formula in cell A3:

=DATE(A1,1,1)+7-WEEKDAY(DATE(A1,1,1))

This works because the WEEKDAY function returns a value of 1 (Sunday) through 7 (Saturday) for any date. If you subtract that value from 7, then you have a value of 6 (Sunday) through 0 (Saturday). When you add that value to the DATE value for January 1 of the year, you end up with the first Saturday of the year.

If you prefer to have your weeks end on Fridays, then the formula needs to change a bit:

=DATE(A1,1,1)+7-(WEEKDAY(DATE(A1,1,1)+1))

Finally, if you prefer to have your weeks end on Sundays, then the formula needs to be like this one:

=DATE(A1,1,1)+7-WEEKDAY(DATE(A1,1,1),2)

This formula uses a parameter for the WEEKDAY function that calculates weekdays that range from 1 (Monday) through 7 (Sunday).

Once you have the first week-ending date for the year (in A3, remember?), then you can calculate the rest of the weekending dates for the year. Place the following formula in cell A4:

=IF(YEAR(A3+7)=$A$1,A3+7,"")

This checks to see if one week past the previous date is still in the year. If it is, then the new date is returned. If it isn't, then an empty string is returned. If you copy this formula from A4 down through A55, then you will have all the desired week-ending dates for the year. With the formulas in place, simply change the year in cell A1 to see how the dates change.

The range A3:A55 provides room for 53 week-ending dates, which is possible for any given year. Because you used the IF statement in the formula in cells A4:A55, then the very last value (A55) will be blank if there were only 52 weekending dates for the year.

Converting Numeric Values to Times

Sam has a lot of worksheets that contain times. The problem is that the times are in the format "1300" instead of the format "13:00." Thus, Excel sees them as regular numeric values instead of recognizing them as times. Sam wants them to be converted to actual time values.

There are several ways you can approach this task. One way is to use the TIME function to convert the value to a time, as shown here:

=TIME(LEFT(A1,2),RIGHT(A1,2),)

This formula assumes that the time in cell A1 will always contain four digits. If it does not (for instance, it might be 427 instead of 0427), then the formula needs to be modified slightly:

=TIME(LEFT(A1,LEN(A1)-2),RIGHT(A1,2),)

The formula basically pulls the leftmost digit (or digits) and uses them for the hours argument of the TIME function, and then uses the two rightmost digits for the minutes argument. TIME returns an actual time value, formatted as such in the cell.

A similar formulaic approach can be taken using the TIMEVALUE function:

=TIMEVALUE(REPLACE(A1,LEN(A1)-1,0,":"))

This formula uses REPLACE to insert a colon in the proper place, and then TIMEVALUE converts the result into a time value. You will need to format the resulting cell so that it displays the time as you want.

Another variation on the formulaic approach is to use the TEXT function, in this manner:

=--TEXT(A1,"00\:00")

This returns an actual time value, which you will then need to format properly to be displayed as a time.

Another approach is to simply do the math on the original time to convert it to a time value used by Excel. This is easy once you realize that time values are nothing more than a factional part of a day. Thus, a time value is a number between 0 and 1, derived by dividing the hours by 24 (the hours in a day) and the minutes by 1440 (the minutes in a day). Here is a formula that does that:

=INT(A1/100)/24+MOD(A1,100)/1440

This determines the hour portion of the original value, which is then divided by 24. The minute portion (the part left over from the original value) is then divided by 1440 and added to the first part. You can then format the result as a time, and it works perfectly.

All of the formulas described so far utilize a new column in order to do the conversions. This is handy, but you may want to actually convert the value in-place, without the need for a formula. This is where a macro can come in handy. The follow macro will convert whatever cells you have selected into time values and format the cells appropriately:

Sub NumberToTime()

    Dim rCell As Range

    Dim iHours As Integer

    Dim iMins As Integer

    For Each rCell In Selection         If IsNumeric(rCell.Value) And Len(rCell.Value) > 0 Then             iHours = rCell.Value \ 100             iMins = rCell.Value Mod 100 rCell.Value = (iHours + iMins / 60) / 24             rCell.NumberFormat = "h:mm AM/PM"         End If

    Next

End Sub

The macro uses an integer division to determine the number of hours (iHours) and stuffs the remainder into iMins. This is then adjusted into a time value and placed back into the cell, which is then formatted as a time. You can change the cell format, if desired, to any of the other time formats supported by Excel.

Parsing Non-Standard Date Formats

Subscriber Bill Palmer is faced with the challenge of importing data into Excel that was originally created in other applications. The problem is that the data contains lots of dates, but they are in a format that Excel doesn't understand. For instance, the dates may be in the format 02.01.04 or 2.1.2004, neither of which is treated as a date by Excel. Bill wants to know how to convert the non-standard dates to a date format that Excel understands.

If the dates are in the same sequence format that you use in your regional settings, then converting is a snap. For instance, if your regional settings use the date format MDY (month followed by day followed by year), and the date you are importing is in the same format, then you can simply select the cells and replace the periods with a slash. When Excel changes 2.1.2004 to 2/1/2004, it automatically parses the result as a date.

If the format you are importing doesn't match your regional settings, then you need to shuffle around the date into the same format. For instance, if the date you are importing is 02.01.04 (February 1, 2004), and your system would interpret this as January 1, 2004, then the easiest way is to separate the date into individual components, and then put them back together. Follow these general steps:

1.   Insert three blank columns to the right of the date column.

2.   Select the cells containing the non-standard dates.

3.   Using the Text to Columns Wizard (Data | Text to Columns), choose delimited data and use a period as the delimiter. After the wizard is done, you end up with three columns containing the month, day, and year. (Click hereto see a related figure.)

4.   In the remaining blank column, enter a formula such as the following:      =DATE(C1,A1,B1)

5.   Copy the formula down to other cells next to the dates.

6.   Select the cells containing the formulas you just created, then press Ctrl+C.

7.   Use Edit | Paste Special | Values to convert the formulas to results.

8.   Delete the three columns that contain the separated dates, and keep the column that contains the final dates.

Another solution is to simply use a macro to do the conversion. The following is a user-defined function that takes the non-standard date and converts it to a properly formatted date value. The macro also switches around the position of the month and day, as done in the Text to Columns technique.

Public Function Convert_Date(A As String) As Date

    Dim K As Long

    Dim K1 As Long

    Dim K2 As Long

    K = Len(A)

    K1 = InStr(1, A, ".")

    K2 = InStr(K1 + 1, A, ".")

    Convert_Date = DateSerial(Val(Mid(A, K2 + 1, _

      K - K2 + 1)), Val(Mid(A, K1 + 1, K2 - K1)), _

      Val(Mid(A, 1, K1 - 1)))

End Function

How can I show the day and date in the one cell in my Excel 97 workbook?

1.    Type the date into a cell, such as 9/8/99 (for 9th August 1999) then press (Enter).

2.    Click back on the cell and select the Format menu then Cells

3.    Select the Custom category on the left of the box, not the Date category, as you would in version 5.

 

4.    Click in the field under Type and delete the entry that’s there.

5.    Type in ddd, d/mm/yy as shown. The ddd tells Excel to display the abbreviation of the day, such as Mon.

The rest of the code tells Excel to display the date as usual.

 

6.    Click OK and the date will appear as Mon, 09/08/99.

Entering any other date into the cell will also display the new day and date.

Use the following examples to work out which code you need to enter to get the result you want:

 

How can I calculate the difference between two dates?

Excel stores dates as serial numbers. The number 1 represents January 1, 1900, the number 2 represents January 2, 1900, and so on. Formatting these numbers using a date format causes them to appear as actual dates. Therefore, if you have dates stored in two cells, you can simply create a formula that subtracts one from the other to get the number of intervening days. You'll want to make sure that the formula cell is formatted as a number, not a date.

You might also find the DATEDIF function useful. This function, which was not documented prior to Excel 2000, returns the difference between two dates, expressed in years, months, or days. You might use the DATEDIF function to calculate how many months the payment on an invoice is overdue or to determine a person's age when you know their birth date.

Excel's DATEDIF function takes three arguments. Its syntax is:

=DATEDIF(start_date,end_date,units)

In the syntax, start_date is a date or reference to a date, end_date is a date or reference to a date, and units is a one- or two-digit string (in double quotes) specifying the units for the difference between the two dates. Acceptable values for the units argument are shown below.

•    y returns the number of full years in the period.

•    m returns the number of full months in the period.

•    d returns the number of full days in the period.

•    md returns the number of full days in excess of the last full month.

•    ym returns the number of full months in excess of the last full year.

•    yd returns the number of full days in excess of the last full year.

For example, assume cells A1 and B1 contain dates. The formula below returns the number of full years between the dates (useful for calculating a person's age):

=DATEDIF(A1,B1,"y")

The formula below calculates the number of full months between the two dates:

=DATEDIF(A1,B1,"m")

Calculate A Person's Age

Here is an easy way to calculate someone’s age using Excel. In cell A1 enter someones date of birth and in another cell enter this formula (copy and paste it right from this page).

=DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, " & DATEDIF(A1,NOW(),"md") & " days" This will return a string like: 33 years, 9 months, 18 days

You could also use this technique in many other situations where you need to calculate the time between today and some any other date.

Problem:

Calculating the number of business days between the two dates listed in A2:B2.

Solution:

Use the NETWORKDAYS function, as follows:

=NETWORKDAYS(A2,B2)

In case the NETWORKDAYS function is not available (formula returns #NAME?), install the Analysis ToolPak Add-in:

Tools->Add-ins->select Analysis ToolPak->Click OK.

 

Calculate A Person's Age

Here is an easy way to calculate someone’s age using Excel. In cell A1 enter someones date of birth and in another cell enter this formula (copy and paste it right from this page).

=DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, " & DATEDIF(A1,NOW(),"md") & " days" This will return a string like: 33 years, 9 months, 18 days

You could also use this technique in many other situations where you need to calculate the time between today and some any other date.

Calculate the number of days in a month

Excel lacks a function for calculating the number of days in a particular month, so you'll need to construct your own formula.

If cell A1 contains a date, this formula will return the number of days in the month:

=DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1).

Creating A Series Of Workdays

If you want to create a series of dates in a column, consisting of only weekdays (Monday through Fridays), enter your starting date in a cell (A4, in the example), and then enter the following formula in the cell below that cell.

=IF(OR(WEEKDAY(A4+1)=1,WEEKDAY(A4+1)=7),A4+3,A4+1)

Then use Fill Down to fill out your entire series of dates.

Date Arithmetic

Adding Dates

You can add some number of days to a date by simply using the =SUM function. Since Excel stores dates as a number of days, no further work is required. For example, to add 5 days to 1/1/98, in A1, use =A1+5, which gives 1/6/98.

To add a number of months or years to a date, you first need to decompose the initial date into its year, month, and day components, add in the desired offset, and then have Excel put the components back together.

For example, say you have a date in A1, to which you want to add 3 months and 4 days. You'd use the following formula:

=DATE(YEAR(A1), MONTH(A1)+3, DAY(A1)+4)

Excel will automatically handle the situation which arises when you pass a number greater than 12 to the MONTH function, or a number greater than 31 to the DAY function.

For example, adding 6 months and 10 days to 8/25/97, in A1 with the formula

=DATE(YEAR(A1),MONTH(A1)+6, DAY(A1)+10)

gives 3/7/98.

Generally, you cannot add two dates that are in serial format. For example, adding 1/15/1998 and 6/15/1998 gives 6/30/2096, which is essentially meaningless.  If you want to add some number of days to a date, but exclude weekends and holidays,  you can use the WORKDAY function, which is part of the Analysis Tool Pack 

Note, however, that adding a month to a date may give you a result that you do not expect.  For example, suppose A1 contains the date 31-Jan-2002.  If you use the formula

=DATE(YEAR(A1), MONTH(A1)+1, DAY(A1))

you will get the date 3-March-2002, because the "31st" day of February, 2002, is 3-March.  The formula below will work around this issue, returning the last day of the next month if the date in A1 is a day that does not exist in the next month. 

=DATE(YEAR(A1),MONTH(A1)+1,MIN(DAY(A1),DAY(DATE(YEAR(A1),MONTH(A1)+2,0))))

For example, if A1 contains the date 31-Jan-2002, this formula will return 28-Feb-2002.  It is important that you and your users understand what "one month later" means in the context of your workbook.

Subtracting Dates

You can subtract some number of days to a date by simply using the SUM function. Since Excel stores dates as a number of days, not further work is required. For example, to subtract 5 days to 6-Jan-98, in A1, use =A1-5, which gives 1-Jan-98.

To subtract a number of months or years to a date, you first need to decompose the initial date into its year, month, and day components, subtract the desired offset, and then let Excel put the components back together. For example, say you have a date in A1, to which you want to subtract 3  months and 4 days. You'd use the following formula:

=DATE(YEAR(A1), MONTH(A1)-3, DAY(A1)-4)

Excel will automatically handle the situation which arises when you pass a number less than 0 to the MONTH  function, or a number less than 0 to the DAY function.

For example, subtracting 6 months and 10 days to 4/5/97, in A1 with the formula

=DATE(YEAR(A1),MONTH(A1)-6, DAY(A1)-10)  

gives 9/25/96.

Generally, you cannot subtract two dates that are in serial format.  If you want to subtract some number of days from a date, but exclude weekends and holidays,  you can use the WORKDAYfunction, which is part of the Analysis Tool Pack



Adding Times

You can add times using the =SUM worksheet function. Just enter all of your times as HH:MM:SS, and then use SUM to add them up. You may leave off the :SS if you prefer. By default, Excel will display the sum of times in "time-of-day" format, meaning that adding 12:30 + 12:45 will yield 01:15. You can prevent Excel from "rolling over" at 24 hours by formatting the result cell as [h]:mm which will cause it to display 25:15 rather than 01:15.

If you want to add up minutes and seconds, you must include a leading "0:" in your data. For example, enter "0:10:20" to indicate 10 minutes, 20 seconds. When you sum these times, Excel will display the sum in "time-of-day" format, meaning that adding 0:40:10 and 0:30:20 will yield 1:10:30. You can prevent Excel form "rolling over" at the hour by formatting the result cell as [m]:ss  which will cause it to display 70:30 rather than 1:10:30.

Another method of adding times is to use the TIME function. To add 1 hour, 35 minutes, 10 seconds to a time in A1, use the function

=A1 + TIME(1,35,10)

Subtracting Times

Subtracting one time from another is a little more difficult, since Excel does not handle negative numbers as times. When you enter a time without a date, Excel assumes this is that time of day on January 1, 1900, since it puts a 0 in for the date component of the serial number. For example, you cannot subtract 18 hours from 4:00 PM, since this would result in a negative number (0.67 - 0.75 = -0.83).

You can get around this by entering a full date and then subtracting a time from this, and the formatting the result as time-only. For example to subtract 18 hours from 4:00 PM, enter the 4:00 PM as "1/1/98 4:00 PM" and subtract 18:00 from this. Formatting the result as hh:mm  will result in "10:00 PM" which is what we would expect.

Another method is to use the TIME function. To subtract 1 hour, 35 minutes, 10 seconds from a time in A1, use the function

=A1 - TIME(1,35,10)

Time Intervals

You can determine the number of hours and minutes between two times by subtracting the two times.  However, since Excel cannot handle negative times, you must use an =IF statement to adjust the time accordingly.  If your times were entered without a date (e.g, 22:30), the following statement will compute the interval between two times in A1 and B1 .

=IF(A1>B1,B1+1-A1,B1-A1)

The "+1" in the formula causes Excel to treat B1  as if it were in the next day, so 02:30-22:00 will result in 4:30, four hours and thirty minutes, which is what we would expect.  To covert this to a decimal number, for example, 4.5, indicating how many hours, multiply the result by 24 and format the cell as General or Decimal, as in

=24*(IF(A1>B1,B1+1-A1,B1-A1)) Rounding Times

For many scheduling or payroll applications, it is useful to round times to the nearest hour, half-hour, or quarter-hour.  The MROUND function, which is part of the add-in module, is very useful for this. Suppose you have a time in cell A1.  In B1 , enter the number of minutes to which you want to round the time -- for example, enter 30 to round to the nearest half-hour.  The formula

=TIME(HOUR(A1),MROUND(MINUTE(A1),B1),0)

will return a time rounded to the nearest half-hour, either up or down, depending what is closest.  For example, 12:14 is rounded to 12:00, and 12:15 is rounded to 12:30.

To round either up or down to the nearest interval, enter the interval in B1, and use either of the following formulas:

=TIME(HOUR(A1),FLOOR(MINUTE(A1),B1),0)

to round to the previous interval (always going earlier, or staying the same).

=TIME(HOUR(A1),CEILING(MINUTE(A1),B1),0) to round to the next interval (always going later, or staying the same).

Calculating an age between two dates using the DATEDIF function

It is easy to determine the number of days between any two dates simply by subtracting one from the other, but it is extremely difficult to return a string that says something like '24 years, 6 months and 8 days'. The maths and logic involved in this apparently straightforward question are very complex. You may wish to try it using the MONTH() and DAY() functions, but make sure you thoroughly test your formula!

An Excel function called DATEDIF() will perform this calculation although it is not included within the Excel help files or the function wizard until Excel 2000. It does however exist in previous versions of Excel. It takes the following structure: =DATEDIF(Date1, Date2, OutputRequirement). The first two parameters are simple, although you must ensure that the second date is greater than the first. The OutputRequirement parameter is a character code (typed inside inverted commas) that specifies the type of value you wish to obtain.

same year.

Days excluding Months and The number of days between Date1 and Date2, as if Date1 and Date2 were in the same

"md"

                         Years                                     month and the same year.

Code Description

Explanation

"d"     Days

The number of days between Date1 and Date2.

"m"   Months

The number of complete months between Date1 and Date2.

"y"     Years

The number of complete years between Date1 and Date2.

"yd" Days excluding Years

The number of days between Date1 and Date2, as if Date1 and Date2 were in the same year.

"ym" Months excluding Years

The number of months between Date1 and Date2, as if Date1 and Date2 were in the

This function is ideally suited to calculating someones precise age on any particular date or the number of days until your next birthday. The first date can be your date of birth and the second date is either entered in another cell by the user, or automatically set to todays date with the NOW() function.

 

The diagram (right) illustrates the difference between the different types of day count. The first formula (row 26) counts the total number of days between the 2 dates. The next formula counts the days which are the remainder after the whole years are deducted. The final formula returns the number days after both the whole years and the months have been deducted.

 

The second diagram (left) shows how the function can be incorporated within a complex formula to return a sentence describing a time interval (in cell A38). The Textfunction is the correct way of incorporating both numeric values within a string of characters.

General information about Date and Timefunctions is also available within this site.

It is possible to convert whole phrases, including number values, into proper words (e.g. 'Twelve years and Four days'). This requires a user defined function such as shown here

How can I enter the current date into a cell so it doesn't change from day to day? When I use the NOW function, it

 

always shows the current date.

 

 

The easiest way to enter the current date into a cell is to use the Ctrl-; key combination (press the Ctrl key and type a

semicolon). Similarly, you can enter the current time by pressing Ctrl-Shift-; (press the Ctrl and Shift keys and type a

semicolon). Both of these key combinations enter the information as a volatile value, not as a formula.

 
       

Recently an ExcelTips subscriber asked if it was possible to use Excel to calculate the months of tenure for a series of employees, given the date at which the employee started. This can be done very easily. For the sake of this example, let's assume that column C contains the starting date for a list of employees. You could use the following formula in column D to determine each employee's tenure: =DATEDIF(C3,NOW(),"M")

The DATEDIF function calculates the difference between a starting date and an ending date. The "M" used in the formula indicates that you want the result in completed months.

To calculate the average tenure for your series of employees, simply include the following formula at the bottom of column C:

=AVERAGE(C3:C174)

Of course, you should replace the range in the function (C3:C174) with the actual range of employee tenures as determined by the DATEDIF formula.

This page describes a few methods for working with intervals of dates.   Specifically, it address the questions of whether a date falls within an interval, the number of days that two intervals overlap, and the how many days are in one interval, excluding those days in another interval.  These formulas can be quite useful for scheduling applications, such as employee vacation schedules. 

Is A Date In An Interval?

Suppose we have 3 dates -- a start date, and end date, and a test date.   We can test whether the test date falls within the interval between start date and end date.   In this formula, we will use three named cells:  TDate1 for the start date, TDate2 for the end date, and TDate for the test date.  This formula will return either TRUE or  FALSE, indicating whether TDate falls in the interval.

=AND((TDate>=MIN(TDate1,TDate2)),TDate<=MAX(TDate1,TDate2))

For example if  TDate1 is 1-Jan and TDate2 is 31-Jan , and TDate is 15-Jan , the formula will return TRUE, indicating that  TDate falls in the interval.

In this formula, it does not matter whether TDate1 is earlier or later than TDate2.

Number Of Days In One Interval And Not In Another

We can also work with two date intervals, and determine the number of days that fall in one interval, and not in another.  This can become complicated because of how the intervals may overlap.  For example, the main interval may complete contain the exclusion interval.  Or, the exclusion interval may completely contain the main interval.  Moreover, only part of the main interval may be contained within the exclusion interval, either at the starting or the ending end of the interval. Finally, the two intervals may not overlap at all. 

Below is a summary of the various interval types.  The Dates values are the days we wish to count.  The VDates values are the days we wish to exclude from the Dates interval.   The complexity of the formula is due to the fact that it must handle all of the interval types.

 

For this formula, we will have 4 named cells, as shown below:

                                 Name                   Description

Date1       The starting date of the main interval.  The main interval is the dates we want to work count.

                                 Date2       The ending date of the main interval.

VDate1      The starting date of the exclusion interval.  The exclusion interval is the dates that we want to exclude from the count of the main interval.

                                 VDate2      The ending date of the exclusion interval. 

A list of holiday dates.  Used in the second version of the formula, which uses the

                                  NWRange    NETWORKDAYS function.

For this formula, we require that Date1 is less than (earlier than) or equal to Date2, and that VDate1 is less than (earlier than) or equal to VDate2.

=IF(OR(Date2<VDate1,VDate2<Date1),Date2Date1+1,IF(OR(AND(Date1<=VDate1,Date2>=VDate2), AND(Date1>=VDate1,Date2<=VDate2)),MAX(0,(Date2-Date1)-(VDate2-VDate1)), IF(OR(AND(Date1<=VDate1,Date2<=VDate2),AND(Date1>=VDate1,Date2>VDate2)), MAX(0,(VDate1-Date1))+MAX(0,Date2-VDate2),NA())))

 Here are some examples. 

                        Date1       Date2       VDate1 VDate2 Result      Description

There are 20 days between 1-Jan and 9-Jan (9 days) and 21-

                                                                       10- 20-     Jan and 31-Jan (11 days). The 11 days between 10-Jan and

                          1-Jan 31-Jan         20 20-Jan are subtracted from the 31 days between 1-Jan and 31-

Jan Jan

Jan.  In this example, the entire exclusion interval (the

VDates) is included within the main interval (the Dates).

                                                                                       31-  Here, the entire main interval is included within the exclusion

10-Jan 20-Jan 1-Jan 0 interval.  There are no days between 10-Jan and 20-Jan that Jan

do fall outside the 1-Jan and 31-Jan. 

In this case, the ending segment of the main interval (1-Jan to

                                                                       10- 20-    15-Jan) overlaps with the beginning segment of the exclusion

                          1-Jan 15-Jan           9 interval (10-Jan to 20-Jan).  There are 9 days (1-Jan to 9-Jan)

Jan Jan

in the main interval that do not overlap with the exclusion interval. 

                                                                                       15-    Ending segment of the main interval overlaps the exclusion

                        10-Jan 20-Jan 1-Jan       5 interval. There are 5 days (16-Jan to 20-Jan) in the main

Jan

interval that are not included in the exclusion segement.

Note that the dates here are inclusive.  There are 10 days between 1-Jan and 10-Jan.  This is one day different that what you would get from simply subtracting the dates. 

The formula above does not treat weekend days differently from working days.   In other words, Saturdays and Sundays are included in the calculations.  If you want to count only weekdays, excluding weekends and holidays, use the modified version below, which calls the NETWORKDAYS function to compute the number of working days in the intervals.  This function adds another name ranged to the mix.  This name, NWRange, refers to a range containing a list of holidays.  If you do not use holidays, you can either point this name to an empty cell, or eliminate it from the formula entirely. =IF(OR(Date2<VDate1,VDate2<Date1),NETWORKDAYS(Date1,Date2,NWRange),

IF(OR(AND(Date1<=VDate1,Date2>=VDate2),AND(Date1>=VDate1,Date2<=VDate2)), MAX(0,NETWORKDAYS(Date1,Date2,NWRange)-NETWORKDAYS(VDate1,VDate2,NWRange)), IF(OR(AND(Date1<=VDate1,Date2<=VDate2),AND(Date1>=VDate1,Date2>VDate2)), IF((Date1>=VDate1),0,NETWORKDAYS(Date1,VDate1-1,NWRange))+ IF((Date2<=VDate2),0,NETWORKDAYS(VDate2+1,Date2,NWRange)),NA())))

The NETWORKDAYS function is part of the Analysis Tool PackAdd-In, so you must have this module installed in order to use this formula.  For more information about using formulas to create the dates of holidays, see the Holidayspage.

Tangent:  The reason the named cells are VDate1 and VDate2 is that I originally wrote this formula for a Vacation timekeeping application, and the V refers to "Vacation".   Of course, you can name your cells anything that works with your application, or you can simply use cell references.

Number Of Days Common To Two Intervals

The  previous section worked with a logical NOT condition -- dates in one interval and NOT in another.  This section describes a formula for working with the inverse of that -- the number of days that are in BOTH of two intervals. 

For this formula, we will have 4 named cells, as shown below:

                                         Name            Description

                                         IDate1   The starting date of the first interval.

                                         IDate2   The ending date of the first interval.

                                         RDate1   The starting date of the second interval.

                                         RDate2   The ending date of the second interval. 

A list of holiday dates.  Used in the second version of the formula, which uses

NWRange the NETWORKDAYS function.

For this formula, we require that IDate1 is less than (earlier than) or equal to IDate2, and that RDate1 is less than (earlier than) or equal to RDate2. The formula below will return the number of days that are in both intervals. 

=IF(OR(IDate2<RDate1,IDate1>RDate2),0,(MIN(IDate2,RDate2)-MAX(IDate1,RDate1)+1))

 Here are some examples. 

IDate1 IDate2 RDate1 RDate2 Result Description

There are 11 days common to the intervals.   Since

                                                                  31- 10- 20-          the RDates are contained within the IDates, the

                                           1-Jan                   11

                                                                  Jan Jan Jan         result is the number of days between 10-Jan and

20-Jan, or 11 days.

                                             10- 20-         31-        Since this is an AND condition format, we can

                                                                              1-Jan        11

                                             Jan Jan         Jan       reverse the dates between IDates and RDates, and

get the same result as above, 11 days. 

                                                                  15- 10- 20-        Here, there are 6 days common to the two intervals

                                           1-Jan                   6

                                                                  Jan Jan Jan         -- the dates 10-Jan to 15-Jan fall in both intervals.

10- 15- 20- The result here is 0, because there are no dates in 1-JanJan Jan Jan     0 the IDate interval (1-Jan to 10-Jan) than fall in the

RDate interval (15-Jan to 20-Jan).

Note that the dates here are inclusive.  There are 10 days between 1-Jan and 10-Jan.  This is one day different that what you would get from simply subtracting the dates. 

The formula above does not treat weekend days differently from working days.   In other words, Saturdays and Sundays are included in the calculations.  If you want to count only weekdays, excluding weekends and holidays, use the modified version below, which calls the NETWORKDAYS function to compute the number of working days in the intervals.  This function adds another name ranged to the mix.  This name, NWRange, refers to a range containing a list of holidays.  If you do not use holidays, you can either point this name to an empty cell, or eliminate it from the formula entirely.

=IF(OR(IDate2<RDate1,IDate1>RDate2),0, ABS(NETWORKDAYS(MIN(IDate2,RDate2),MAX(IDate1,RDate1),NWRange)))

The NETWORKDAYS function is part of the Analysis Tool PackAdd-In, so you must have this module installed in order to use this formula.  For more information about using formulas to create the dates of holidays, see the Holidayspage.

A final note:   These formulas are designed to illustrate the concept of the date "intervals", and how they need to be handled by the Excel date arithmetic.  Logicians can probably reduce the formulas to simpler forms by consolidating the ANDs and ORs.  But the point here is illustration and education, not the simplest possible formula.  

                               A                                                                            B                                                                  C

1    Old Date Formula Used            New Date

2    112303    =VALUE(LEFT(A2,2)&"/"&MID(A2,3,2)&"/"&RIGHT(A2,2))     11/23/03

3    031123    =VALUE(MID(A3,3,2)&"/"&RIGHT(A3,2)&"/"&LEFT(A3,2))     11/23/03

4    231103    =VALUE(MID(A4,3,2)&"/"&LEFT(A4,2)&"/"&RIGHT(A4,2))     11/23/03

                               A                                                                            B                                                                  C

1    Old Date Formula Used            New Date

2    112303    =VALUE(MID(A2,3,2)&"/"&LEFT(A2,2)&"/"&RIGHT(A2,2))     23/11/03

3    031123    =VALUE(RIGHT(A3,2)&"/"&MID(A3,3,2)&"/"&LEFT(A3,2))     23/11/03

4    231103    =VALUE(LEFT(A4,2)&"/"&MID(A4,3,2)&"/"&RIGHT(A4,2))     23/11/03

Problem:

Creating new IP addresses, composed of the first three segments in the original address and the last segment in the original address multiplied by 2.

Solution:

Use the LEFT, MID and FIND functions to extract the last segment of the original address, multiply it by 2 and join that new segment back to the remaining three.

Following is the formula:

=(LEFT(A2,FIND(""."",A2,FIND(""."",A2,FIND(""."",A2)+1)+1)))&(MID(A2,FIND(""."",A2,FIND(""."",A2,FIND("".

"",A2)+1)+1)+1,99)*2)

 

Installing The Analysis Tool Pack

Since the ATP is provided free with Excel, you already have it on your computer.  To install it and make its functions available in your workbooks, go to the Tools menu, and select the Add-Ins item. 

This page describes a number of worksheet functions for working with dates and times in Excel.           

Working Days And Hours Between Two Dates And Times

Suppose you have a schedule start date and time and an end date and time.  Simply subtracting the end-time from the start-time will return the total number of days and hours between the times.  However, suppose you want to know how many work days and hours the project will actually take.  For example, suppose a project starts on Monday, 25-October99 at 13:00 and ends on Thursday, 28-Oct-99 at 15:00, and that your normal working hours are 9:00 to 17:00.  In this case, there are 3 days and 2 hours between the start and end times (2 full days, Tuesday and Wednesday, plus 4 hours on Monday and 6 hours on Thursday, for 2 days and 10 hours, or, since there are 8 hours in a workday, 3 days and 2 hours).

The following formula will return the number of working days and hours between two dates and times.  It returns a string like "3 days 2 hours".

=IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT(StartDT), HolidayList,0)))),"0 days 0 hours", IF(INT(StartDT)=INT(EndDT), "0 days " & ROUND(24*(EndDT-StartDT),2)&"hours", MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+ INT(24*(((EndDT-INT(EndDT))-(StartDT-INT(StartDT)))+ (DayEnd-DayStart))/(24*(DayEnd-DayStart)))& " days "&MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+ (24*DayEnd-(24*(StartDT-INT(StartDT)))),2), ROUND((24*(DayEnd-DayStart)),2))&" hours "))

Where the following names are used:

 

To return the total number of working hours, use the following formula:

=IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT(StartDT),HolidayList,0)))) ,0,ABS(IF(INT(StartDT)=INT(EndDT),ROUND(24*(EndDT-StartDT),2), (24*(DayEnd-DayStart)* (MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+ INT(24*(((EndDT-INT(EndDT))(StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+ MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+ (24*DayEnd-(24*(StartDT-INT(StartDT)))),2), ROUND((24*(DayEnd-DayStart)),2))))))

This will return the number of working hours as a numeric value, which can be used in other calculations.  For example, if there are 8 hours in a working day, and the duration is 3 days, 6 hours (from the first formula), the formula above will return 30 (3*8 + 6).

Since these formulas use the NETWORKDAYS worksheet function, you must the installed in order to use the formula.

NOTE:  These formulas will not work properly if the working day starts at a later time of day than it ends.  For example, if your working day crosses midnight, the formulas will not work properly.

Days In Month

To return the number of days in a month, use the following formula.  Suppose cell A1 contains the date 15Jan-1998.

=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))

This will return 31, the number of days in January, 1998.

First Day Of Month

You can use the following formula to get the date of the first day of the month. Suppose the date in cell A1 is 15-Jan-1998.

=DATE(YEAR(A1),MONTH(A1),1)

This will return 1-Jan-1998.

Last Day Of Month

You can use the following formula to get the date of the last day of the month. Suppose the date in cell A1 is 15-Jan-1998.

=DATE(YEAR(A1),MONTH(A1)+1,0)

This will return 31-Jan-1998.

First Weekday Day Of Month

You can use the following formula to get the date of the last weekday (Monday thru Friday) of the month.  

Suppose the date in cell A1 is 1-June-2002.

=DATE(YEAR(A1),MONTH(A1),1)+CHOOSE(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),1,0,0,0,0,

0,2)

This will return 3-June-2002.

Last Weekday Day Of Month

You can use the following formula to get the date of the last weekday (Monday thru Friday) of the month.   Suppose the date in cell A1 is 6-Nov-2002.

=DATE(YEAR(A1),MONTH(A1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)-5))

This will return 29-Nov-2002.  Another formula, given a month and year, is the following array formula:

=MAX(ROW(INDIRECT(DATE(year,month,1)&":"&DATE(year,month+1,0)))*(WEEKDAY(ROW(INDI RECT(DATE(year,month,1)&":"&DATE(year,month+1,0))),2)<6))

Last Day Of Previous Month

You can use the following formula to get the date of the last day of the previous month.  Suppose the date in cell A1 is 15-Jan-1998.

=DATE(YEAR(A1),MONTH(A1),0)

This will return 31-December-1997.

Nth Day Of Week For A Month And Year

This formula will return will return the date of Nth day-of-week for a given month and year. For example, it will return 26-March-98 for the 4th Thursday of March, 1998.   Days-of-week range from 1 to 7, with Sunday = 1 and Saturday = 7.

=DATE(Yr,Mon,1+((Nth-(DoW>=WEEKDAY(DATE(Yr,Mon,1))))*7)+ (DoW-WEEKDAY(DATE(Yr,Mon,1))))

Where Yr, Mon, Nth,  and DoW are cell references or values indicating Year, Month, Nth, and Day-OfWeek.

Nth Day Of Week For A Year

This formula will return will return the date of Nth day-of-week for a given  year. For example, it will return 11April-2001 for the 15th Wednesday of the year 2001.   Days-of-week range from 1 to 7, with Sunday = 1 and Saturday = 7.

=DATE(Yr,1,1+(Nth-(Dow>=WEEKDAY(DATE(Yr,1,1))))*7)+

Dow-WEEKDAY(DATE(Yr,1,1))

Where Yr,Nth, and DoW are cell references or values indicating Year, Month, Nth, and Day-Of-Week.

Number Of Mondays In Period

If you need to return the number of Monday's (or any other day) that occur within an interval between two dates, use the following Array Formula:

=SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))

This formula assumes the following:

A2 contains the beginning date of the interval

B2 contains the ending date of the interval

C2 contains the day-of-week number (1=Sunday, 2=Monday, ,7=Saturday)

Week Differences

You cannot use the =DATEDIF function of return the difference of of two dates in weeks, so you can use the following function return the number of weeks and days between two dates:

=TRUNC((B1-A1)/7)&" Weeks "&MOD(B1-A1,7)&" Days"

What Day Is It?

If you need to know the day-of-week for a specific date, the =WEEKDAY function gives you a number between

1 and 7 indicating the day, (1=Sunday, 2=Monday, , 7=Saturday).   To convert this number to the actual name of the day ("Sunday", for example), use the =TEXT function:

=TEXT(A1, "ddd") returns the three character abbreviation for the day, e.g., "Mon".

=TEXT(A1,"dddd") returns the full name of the day, e.g., "Monday".

To determine if a date is a work day (Monday - Friday), create a name called WorkDays which refers to

={2;3;4;5;6}

Then following function will return TRUE or FALSE, indicating whether the date in A1 is a work day:

=IF(ISERROR(VLOOKUP(WEEKDAY(A1),WorkDays,1,0)),FALSE,TRUE)

What Quarter Is It?

To return the quarter of the year, use the formula  =ROUNDUP(MONTH(A1)/3,0) .

What Week Is It?

If you need to know what week of the year at date falls in, use the following formula:

=TRUNC(((A1-DATE(YEAR(A1),1,1))/7))+1+ IF(WEEKDAY(DATE(YEAR(A1),1,1))>WEEKDAY(A1),1,0)

This will return a number between 1 and 53, indicating the week of the year for the date in A1.

Last Weekday Of Month

There are several ways that this can be done in Excel, and you should pick the method that is best for your purposes. The first method is to simply add a column to your worksheet that will be used for the alert. Assuming your due date is in column F, you could place the following type of formula in column G:

=IF(F3<(TODAY()+7),"<<<","")

The formula checks to see if the date in cell F3 is earlier than a week from today. If so, then the formula displays "<<<" in the cell. The effect of this formula is to alert you to any date that is either past or within the next week.

Another approach is to use the conditional formatting capabilities of Excel. Follow these steps:

1.   Select the cells that contain the document due dates.

2.   Choose Conditional Formatting from the Format menu. Excel displays the Conditional Formatting dialog box. (Click hereto see a related figure.)

3.   Make sure the first drop-down list is "Cell Value Is." (This should be the default.)

4.   Make sure the second drop-down list is "Less Than."

5.   In the formula area, enter "=TODAY()" (without the quote marks).

6.   Click the Format button. Excel displays the Format Cells dialog box. (Click hereto see a related figure.)

7.   Using the Color drop-down list, choose the color red.

8.   Click OK to close the Format Cells dialog box.

9.   Click Add. The Conditional Format dialog box expands to include a second condition.

10.  Make sure the first drop-down list for the second condition is "Cell Value Is." (This should be the default.)

11.  Make sure the second drop-down list is "Less Than."

12.  In the formula area, enter "=TODAY()+7" (without the quote marks).

13.  Click the Format button. Excel displays the Format Cells dialog box.

14.  Using the Color drop-down list, choose the color blue.

15.  Click OK to close the Format Cells dialog box. (Click hereto see a related figure.)

16.  Click OK to close the Conditional Formatting dialog box.

This is a two-tiered format, and you end up with two levels of alert. If the due date is already past, then it shows up as red. If the due date is today or within the next seven days, then it shows up in blue.

Extract The Year, Month, or Day From A Date

Do you use lots of dates in Excel? If you do you probably have needed to use the year, month or day referred to in a date cell in a separate cell. The solution is simple.

Lets say that cell A1 contains the date July 21, 2004 (entered as 21/7/2004 or 7/21/2004 depending on your date system). You want to extract the year (i.e. 2004) from that date and put it in cell C1. Simply enter the formula =YEAR(A1) in cell C1. Same thing for month and serial number of the day. Use =MONTH(A1) or =DAY(A1).

To know the day: =TEXT(A1,”dddd”)

Roger Middleton is keeping track of invoices in an Excel worksheet. All of the invoices need to be submitted with a due date of the 28th of the month, and Roger wondered if there was a way to force a date to always "jump forward" to the next instance of the 28th.

The cleanest way to force dates forward is to create a formula that will examine a date in a cell, and then force that date to the next desired date, such as the 28th. The following formula is a good one to start with:

=IF(DAY(A1)>28,DATE(YEAR(A1),MONTH(A1)+1,28),DATE(YEAR(A1),MONTH(A1),28))

This formula examines the date in cell A1. If the DAY value of the date is greater than 28, then the formula constructs and returns a date that is equal to the 28th of the next month. If it is less than or equal to 28, then the 28th of the current month is returned.

There is an even shorter way to render an acceptable formula, however--one that entirely gets rid of the IF function:

=DATE(YEAR(A1),MONTH(A1)+(DAY(A1)>28),28)

This uses the current year as the year, and the day is always 28. The month uses a Boolean calculation. If the day is greater than 28 then (Day(A1)>28) will be TRUE and will calculate as a 1, thereby adding 1 to the current month. If it is less than or equal to 28 it will be FALSE and calculate as a 0, just calculating the current month.

If you don't want to be "strict" giving some people only a day (9/27/2005 will give a due date of 9/28/2005), you could plan on giving them at least a week with the formula:

=DATE(YEAR(A1),MONTH(A1)+(DAY(A1)>21),28)



This would give the 28th of the current month for the 1st thru 21st, but for later dates it will jump to the 28th of the following month.

Multiplying Times values and numbers:

 

Problem:

Column C:C contains time values representing worked hours. Column D:D contains rates.

In order to calculate the payment, we would have to multiply each time value by its matching numerical rate.

Solution:

To calculate the payment, the time value must be multiplied by 24 as well as by the rate, as follows:

=A2*B2*24 Working With Overtime Hours In Excel

Excel is often used to manage employee time records -- number of hours worked, meal breaks, regular and overtime pay, and so on.  This page examines a number of related formulas for a simple daily timekeeping record.  

Suppose we have 5 employees, and we want to keep track of the time each comes into work, breaks for lunch, returns from lunch, and leaves for the day.  Also, we need to keep track of the regular and overtime hours for each employee, and pay the the overtime hours at a different rate.  The sample below shows what the worksheet might look like:

Now, lets look at the values and formulas in each column.  Column B is simply the Name of each employee.  Columns C, D, E, and F are the times that each employee started work, left for lunch, returned from lunch, and left work for the day.   Pay attention to the hours worked by Emma, in row 8.  Her shift extended over midnight (she started at 10PM and left at 7AM), and her lunch break also extended over midnight.  Our formulas will take this into account, and calculate the proper times.

Cell H2 contains the number of regular, non-overtime, hours per shift.  Here, the 8 indicates that a normal shift is 8 hours.  Cell K2 contains the rate at which overtime hours are paid.  The 1.5 indicates that overtime is paid at time-and-a-half.  

The total hours are in column G.  This is the difference between the start time in column B and the end time in column F.  This value does not pay any attention to the time taken for lunch -- we'll do that later. The formula in G4 is:

=(F4-C4+(F4<C4))*24

This formula subtracts the start time in C4 from the end time in F4.  Then, it compares the end time to the start time.  If F4 is less than, or earlier than, C4, the expression (F4<C4) will evaluate to True or 1.  If not, it will evaluate to False or 0. Adding this comparison result will calculate the number of hours properly in the case where the start time is later in the day than the end time.  This is exactly the situation with Emma.  Finally, the result is multiplied by 24, to get the actual number of hours, rather than a time value -- e.g., 9.00 rather 9:00. (Since times are stored as a fraction of a 24-hour day, multiplying a time by 24 will return the number of hours).  

NOTE: You only need to use the +(F4<C4)part of the formula when the start and end times are just times, without a date.  If you are entering a complete date and time, then you don't need the comparison formula, because the date part of the value will allow Excel to properly calculate the difference. 

Column H contains the number of hours that were actually worked, after taking out the time taken for lunch.  The formula in H4 is:

=((F4-C4+(F4<C4))-(E4-D4+(E4<D4)))*24

This formula is very similar to the formula in G4, expect that it subtracts the time between D4 and E4, the start and end times of the lunch break.  Again, we compare the start and end times to ensure that the number of hours is calculated properly if the time period crosses midnight. Look at Emma's hours again -- she left for lunch at 11PM and returned at 12:30 AM.   Multiplying the result by 24 gives us the actual number of hours worked, rather than a time value. 

Column I contains the number of regular hours worked by each employee.  If the actual worked hours in column H is less than the Regular Hours value in H2, we want to use the actual number of worked hours. However, if the actual worked hours in column H is greater than the Regular Hours value in H2, we only want to count H2 hours as regular time.  The formula in I4 is:

=MIN($H$2,H4)

This formula simply takes the minimum of the allowed regular hours in H2 and the number of hours actually worked in H4. 

Column J contains the number of overtime hours for each employee.  If the employee worked less than the allowed regular hours, the overtime hours will, of course, be zero.  However, if they worked more than the allowed regular hours, the overtime hours will be the difference between the hours actually worked and the allowed regular hours.  The formula in cell J4 is:

=MAX(0,H4-$H$2)

This is simply the maximum of 0 and the difference between actual hours and allowed regular hours.

Column K is just the hourly wage for each employee.  Of course, these may be different for each employee, but they are the same in our example. Column L is the amount of regular, non-overtime wages paid.  This is just the product of column I, the regular hours, and column K, the hourly wage.  Cell L4 has the formula:

=I4*K4

Column M is the amount of overtime wages paid.  This is the product of column J, the overtime hours, column K, the hourly wage, and cell K2, the rate at which overtime hours are paid. Cell M4 has the formula:

=J4*K4*$K$2

Finally, column N is just the total wages paid, the sum of the regular and overtime wages.  Cell N4 has the formula:

=L4+M4

The worksheet and formulas are summarized below:

You can downloada workbook with these formulas. Additional Resources:

John Walkenbach has an example timesheet here.

David McRitchie has an example timesheet here.

Show The Current Date In A Cell

Last week I showed you how to insert time and date stamps into your worksheets. Those were fixed values. This weeks tip will show you how to add a date function to your worksheet that will update with the current date.

The formula to show the current date is =TODAY().

One thing to keep in mind is, this formula will only update when the worksheet is recalculated or when you reopen the workbook. That's usually not a problem since the date only changes once a day. Also, you may have to format the cell to give you your desired date format.

If you'd like to show the current time, you can use the formula =NOW(). This formula gives you both the date and time in the same cell (i.e. 19May-03 9:00pm), however, is less useful because the time in the cell won't change until your worksheet recalculates or when you reopen your workbook. If you just want the cell to display the time only, you'll have to change the number format by selecting Format, Cells, Number, Time, (select a desired time format), OK.

Summing time values that are separated into hours and minutes in different columns Problem:

Columns B:C contains numbers representing hours and minutes accordingly. How could we sum up the numbers in both columns to a single time value?

Solution:

To get a time value representing the sum of hours and minutes in columns B:C use the following Array Formula: {=SUM(TIME(B12:B14,C12:C14,0))} Example:

Hours___Minutes

5_______20

6_______50

3_______10

Result: 15:20

Formula: {=SUM(TIME(B12:B14,C12:C14,0))}

Notes:

To perform an Array Formula: Insert the formula, press F2 and then press Ctrl+Shift+Enter simultaneously. The format in the cell contains formula is:[HH:MM]

 

Excel Functions: Text, Abs and number or date formats

The Text and '&' functions are very useful for including cell values within meaningful sentences which can be understood by a layman. Statements can be constructed which combine words and numeric values (e.g. "The budget for April is £23,000 overspent").

The '&' symbol is not strictly a function. It is an operator which can be used to join together two strings or pieces of text. For example: ="The variance is " & D2 where cell D2 contains either "Adverse" or "Favourable". If you join together a piece of text and a number or date the appearance and formatting of the number can be unpredictable unless you use the Text function.

The Text function converts a number into text and also formats it to the required appearance. It takes the structure: =TEXT(value, "FormatCode"). The complicated aspect of this function is understanding the FormatCodes. Some of them are shown in the following example and tables. Alternatively use Excel help and search for 'Formatting numbers : Custom formats'.

The Abs function changes negative values to positive. = ABS(value)

 

cell M3

= NOW()

cell M4

text entered by user

cell K6

="Out Patient Activity: " & M4

cell K11

=M4 & " has undertaken " & TEXT(ABS(L9),"0") & IF(L9<0," less", " more") & " episodes"

cell K12

="This represents " & IF(L9<0," an adverse", " a favourable") & " variance of " & TEXT(ABS(L9/L7),"0.0%")

cell K13

="Reporting month: " & TEXT(M3,"mmmm yyyy") Formulae used in the example

 

Examples of Format Codes

Note that the equivalent function to Text in Access is called 'Format'.

 

These format codes give a flavour of how numbers can be displayed as text. If you want to format the appearance of numeric, date or text values in cells and retain their original data type use the menu command Format | Cells | Numbers and make your choice.

Unfortunately many of the default formats within Excel are useless and you may be advised to create your own custom formats - either see the section on Configuring Excelor use the Custom number category in the format cells dialog box (right).

Dates and times in Excel are stored as numbers (e.g. 36536.799). These numbers sometimes appear unexpectedly when cell formats have been accidentally changed. You may change the cell number formatting to replace the numbers with a more conventional appearance such as '11/01/2000'.

To change a number format back to a date, select the required range of cells and then choose Format | Cells | Number Category: Date.

If a cell value unexpectedly appears as a series of hashes (#) as in cell F2 (see diagram to the right) this is probably due to the column being too narrow to display the value in the selected format. Try widening the column by moving the cursor to the faint line between the column titles and then dragging it to an appropriate size (or use Format | Column | Width).

Using the FORECAST Function

The FORECAST function can be used whenever you have an existing set of data pairs (x-values and y-values) and you want to calculate an estimated y-value to a new x-value. Excel performs a linear regression based on the existing values and then inserts the x-values into the expression for the regression which gives you an y-value. The new x-value can have any value and is not restricted to be larger than the existing x-values.

The FORECAST function uses the following syntax:

=FORECAST(X, Yrange, Xrange)

In this usage, X is the X value for which you want FORCAST to return a Y value. The Yrange and Xrange parameters are sets of know Y and X values.

As and example, let's say that you are going on a diet, and you decide to keep track of your weight each day. Every day you enter the date into column A and the weight for that day in column B. After getting about 10 days or so of measurements, you can use these data pairs to forecast when you will hit your target weight. If your target weight is 160 lbs., you could use the following formula:

=FORECAST(160, A2:A11, B2:B11)

The result is the anticipated date when you will reach the target weight.

Excel calculates the "trend line" (using linear regression) of the points in A2:B11 (i.e., it assumes there is a linear relationship between the dates and the weights. (This trend line is the same as you would get from plotting the data pairs and adding a trend line to the chart.)

Evalute Function

A user had cells with contents like;

A1= 21+69+89+25+31

A2= 21*25

A3= 100/10

A4= 100/10*(10*10+10)

A5= 100/10*10*10+10

None of these cells had an equal sign and as such the cells were seen as Text by Excel. The person wanted to leave the original cell contents intact and use Column B to return the result of the equations.

The usual suspect of = "="&A1 was tried in cell B1 but only resulted in B1 showing =21+69+89+25+31 and NOT evaluating the formula. It was at this point the word EVALUATE came into my mind.  At first I tried

=EVALUATE(A1). Excel did not like this and came back with an error message "That Function is not valid". After this, the penny dropped fully I remembered how it had to be done. See the steps below

1)         Select cell B1

2)         Go to Insert>Name>Define

3)         Type the name Result (can be any valid range name) 4) In the Refers to: box type: =EVALUATE($A1) 5) Click Add then OK.

It is very important to note that I selected cell B1 and used a Relative Row reference for $A1.

I then simply entered =Result into cell B1 and copied it down and it worked! It even obeys the use of Parenthesis as in the case of: 100/10*(10*10+10) A user had cells with contents like;

A1= 21+69+89+25+31

A2= 21*25

A3= 100/10

A4= 100/10*(10*10+10)

A5= 100/10*10*10+10

None of these cells had an equal sign and as such the cells were seen as Text by Excel. The person wanted to leave the original cell contents intact and use Column B to return the result of the equations.

The usual suspect of = "="&A1 was tried in cell B1 but only resulted in B1 showing =21+69+89+25+31 and NOT evaluating the formula. It was at this point the word EVALUATE came into my mind.  At first I tried

=EVALUATE(A1). Excel did not like this and came back with an error message "That Function is not valid". After this, the penny dropped fully I remembered how it had to be done. See the steps below

1)         Select cell B1

2)         Go to Insert>Name>Define

3)         Type the name Result (can be any valid range name) 4) In the Refers to: box type: =EVALUATE($A1) 5) Click Add then OK.

It is very important to note that I selected cell B1 and used a Relative Row reference for $A1.

I then simply entered =Result into cell B1 and copied it down and it worked! It even obeys the use of Parenthesis as in the case of: 100/10*(10*10+10)

Anyway, a newsletter subscriber (named Graham Gidney) emailed me and showed me yet another way. In fact, his way is even easier! All you need to do is enter = "="&A1 into B1, copy down to B5, then select B1:B5 and Copy, then Edit>Paste Special - Values. Thanks to Graham for this very quick and easy way.

Excel: DSum Function

In Excel, the DSum function sums the numbers in a column or database that meets a given criteria.

The syntax for the DSum function is:

DSum( range, field, criteria ) range is the range of cells that you want to apply the criteria against.

field is the column to sum the values. 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.

For example:

Let's take a look at an example:

 

Based on the Excel spreadsheet above:

            =DSum(A4:D8, "Unit Cost", A1:B2)           would return 7.99

            =DSum(A4:D8, 3, A1:B2)                              would return 7.99

            =DSum(A4:D8, "Quantity", A1:A2)            would return 20

            =DSum(A4:D8, 2, A1:A2)                             would return 20

Using Named Ranges

You can also use a named range in the DSum function. For example, we've created a named range called orders that refers to Sheet1!$A$4:$D$8.

 

Then we've entered the following data in Excel:

 

Based on the Excel spreadsheet above:

            =DSum(orders, "Total Cost", A1:B2)               would return 60.88

            =DSum(orders, 4, A1:B2)                                   would return 60.88

To view named ranges: Under the Insert menu, select Name > Define.

 

= DAVERAGE(A3:C9, 3, E3:E4)

42.67

in cell D11

= DCOUNT(A3:C9,"Age", E3:F4)

2

in cell D12

Change formulas to values

Ron de Bruin (last update 31 march 2005)

Go to the Excel tips page

Instead of  PasteSpecial PasteValues, I use the value property in the examples. See the Excel help if you want to use PasteSpecial.

This will change all the cells in the ActiveSheet to values

Sub Values_1()

With ActiveSheet.UsedRange

.Value = .Value

End With

End Sub

This will change only the CurrentRegion of the ActiveCell to values

Sub Values_2()

With ActiveCell.CurrentRegion

.Value = .Value

End With

End Sub

This will change all cells in a range to values

This will not work correct on a range with more then one Area.

Sub Values_3()

Range("A1:D1").Value = Range("A1:D1").Value

End Sub

For a range with more then Area use this

Sub Values_4()

    Dim smallrng As Range

    For Each smallrng In Range("a1:c10,e12:g17").Areas         smallrng.Value = smallrng.Value

    Next

End Sub

This will change some cells in the Activecell row

This will change the cells in the columns A:D to values

In the

Sub Values_5()

    With ActiveSheet.Range(Cells(, "A"), Cells(, "D"))

.Value = .Value

    End With

End Sub

This will change a Row or Rows to values

Sub Values_6()

Rows("1").Value = Rows("1").Value

End Sub

Sub Values_7()

Rows("1:3").Value = Rows("1:3").Value

End Sub

This will change a Column or Columns to values

Sub Values_8()

Columns("A").Value = Columns("A").Value

End Sub

Sub Values_9()

Columns("A:C").Value = Columns("A:C").Value

End Sub

This will change the selection with one or more areas to Values

Sub Values_10()

    Dim smallrng As Range

    For Each smallrng In Selection.Areas         smallrng.Value = smallrng.Value

    Next

End Sub

 

            HomeExcel TipsSample Spreadsheets

Excel -- Worksheet Functions -- VLookup

Select a Location for a Lookup Table

Create a Lookup table

VLOOKUP Function Arguments

Create a VLookup formula

Create a VLookup formula for a range of values

Combine IF and VLOOKUPTroubleshoot the VLOOKUP formulaDownload sample workbook

In a workbook, you can create a table that stores information about your products, or employees, or other data you want to refer to frequently. From other cells in the workbook, you can use formulas to look up data from the master table.

Select a Location for a Lookup Table

It's a good idea to store each lookup table on a separate worksheet in the workbook. Then, as you add and delete rows in the lookup tables, you won't accidentally add or delete rows in any other table.

In this example, the lookup table is on a sheet named Products.

Create a Lookup Table

Lookup formulas can work vertically, looking for

values down a column, or they can work horizontally, looking for values across a row. In this example, the information will be stored vertically, with values down a column.

1.   Enter the headings in the first row

2.   The first column should contain the unique key values on which you will base the lookup. In this example, you can find the price for a specific product code.

3.   If you have other data on the worksheet, leave at least one blank row at the bottom of the table, and one blank column at the right of the table, to separate the lookup table from the other data.

Note: To make it easier to refer to the table, you can name the range. There are instructions here: Naming a Range

VLOOKUP Function Arguments

The VLOOKUP function has four arguments:

 

1.   lookup_value: That value do you want to look up? In this example, the product code is in cell A7, and you want to find its product name.

2.   table_array: Where is the lookup table? If you use an absolute reference            ($A$2:$C$5), instead of a relative reference (A2:C5), it will be easier to copy            to formula to other cells. Or, name the lookup table, and refer to it by name.

3.   col_index_num: Which column has the value you want returned? In this example, the product names are in the second column of the lookup table.

4.   [range_lookup]: Do you want an exact match? Is an approximate match okay?

If you use TRUE as the last argument, or omit the last argument, an approximate match can be returned. This example has FALSE as the last argument, so if the product code is not found, the result will be #N/A. (Note: Excel is rather forgiving, and will accept 0 instead of FALSE, and 1 instead of

TRUE.)

Create a VLookup formula

 

Once you have created the lookup table, you can create other formulas in the workbook, and pull information from the product list. For example, to prepare an invoice, you can enter a product code, and formulas will get the product name or price from the product table.

In this example, the invoice is created on a sheet named Invoice. The VLOOKUP formula should find an exact match for the product code, and return the product name.

1.    Select the Invoice sheet

2.    Enter product code A23 in cell A7 3. In cell B7, start the VLOOKUP formula:

  =VLOOKUP(

4.   Click on cell A7 to enter its reference.

5.   Type a comma, to end the first argument

6.   Select the Products sheet

7.   Select cells A2:C5 (the product list)

8.   Press the F4 key, to change the cell references to absolute. The formula should now look like this:

   =VLOOKUP(A7,Products!$A$2:$C$5

9.   Type a comma to end the second argument.

10.  Type a 2, which is the column in the lookup table that contains the Product name.

11.  Type a comma to end the third argument.

12.  Type FALSE, to specify that an exact match for the product code is found, and add the closing bracket.

13.  The formula should now look like this:

   =VLOOKUP(A7,Products!$A$2:$C$5,2,FALSE)

14.  Press the Enter key to complete the formula. The product name will be displayed.

Note: To return the product price, create a VLOOKUP formula that refers to column 3 of the lookup table. For example, enter the following formula in cell C7:

          =VLOOKUP(A7,Products!$A$2:$C$5,3,FALSE)

 

Create a VLookup formula for a range of values

In some situations, an approximate match is

            preferred, so several values will return the same                                         

result. For example, when grading student papers, all papers with a grade of 85 or over should receive an A grade.

In this example, the lookup table is created on a sheet named Grades. To create the lookup table, enter the minimum score for each grade in column A. Enter the matching Grade in column B. Sort the Scores in Ascending order.

Cells A2:B6 were named GradeList.

The scores are entered on a sheet named Report Card, where a VLOOKUP formula calculates the grade.

1.   On the Report Card sheet, in cell B4, enter the score 77.

2.   In cell C4, enter the VLOOKUP formula:

  =VLOOKUP(B4,GradeList,2)

3.   Press the Enter key, and the grade for English is returned.

bine IF and VLOOKUP can use an IF formula with VLookup to return exact values if found, and an empty string if not found.

On the Invoice sheet, in cell A8, enter the product code A28. If the VLookup formula in cell B8 has FALSE as the fourth argu #N/A, because there is no exact match for the product code in the lookup table.

Wrap the VLookup formula with an IF formula (in this example the product list has been named):

  =IF(ISNA(VLOOKUP(A8,ProductList,2,FALSE)),"",VLOOKUP(A8,ProductList,2,FALSE))

Press the Enter key, and cell appears blank. Because no exact match was found, the VLookup formula returned an #N/A. The converted this to an empty string.

ember that Excel performs the deepest nested function first, so looking at the formula above, you will see:

The first thing Excel does is perform the first VLOOKUP (since that's the one that is nested deepest)

Then it performs the ISNA function on the result of the VLOOKUP to see if the VLOOKUP gave a #N/A error or not.  The IS returns a value of true if it sees the #N/A error and a value of false if it doesn't

Then Excel performs the IF function on the result of the ISNA function.  If it sees a "true", it returns nothing (which is what th "false", it returns the result of the VLOOKUP function, which is what the last VLOOKUP function is telling it to do if the val

he formula below is drafted in a slightly different way. In it in first vlookup it is asking to retrieve value from first column of tab ext vlookup asks it to retrieve the value from second column of table array.

her pointer for suppressing #N/A error:

OOKUP(lookup_value,table_array,col_index_num,range_lookup) nge_lookup can be TRUE or FALSE, if omitted the default is TRUE

OOKUP will work with a list where the table arguments are sorted, and you will get the closest match to a table argument ed your lookup value. sorted lists use TRUE or default for a *close* match)

OOKUP will work with a list where the arguments are unordered, and you either get an exact match or fail with #N/A!. ether sorted or not when an *exact* match is required so is the use of FALSE)

uppress N/A errors:

ISNA(VLOOKUP( , , ,False)),"Item not found",VLOOKUP( , , ,False)) use of #VALUE! error is a zero value for value in the function.

ot mix cells defined as numbers with cells defined as text in the argument column of your table.  Some tips on determinin of your data.  Your table must be consistent, but your lookup value can be forced to look like the table by using

ese (Peo Sjoblom 3003-01-15).

=VLOOKUP(TEXT(A1,"00000"),Table,2,FALSE)      or =VLOOKUP(A1+0,Table,2,FALSE)

 lookup table contains any blank cells, a VLOOKUP formula will return a zero, instead of a blank cell. You can use nested IFs t

ts, and the empty cell results. For example:

IF(ISNA(VLOOKUP(A8,ProductList,2,FALSE)),"",

IF(VLOOKUP(A8,ProductList,2,FALSE)="","",

VLOOKUP(A8,ProductList,2,FALSE)))

Troubleshoot the VLOOKUP formula

Your VLOOKUP formula may return an #N/A, even though the value you're looking for appears to be in the lookup table.

Text vs. Number

A common cause for this error is that one of the values is a number, and the other is text. For example, the lookup table may contain '123 (text), and the value to look up is 123 (a number).

If possible, convert the text to numbers, using one of the methods shown here:      Convert Text to Numbers

If you can't convert the data, you can convert the lookup value within the VLOOKUP formula:

Lookup values are Text, and the table contains Numbers

If the lookup table contains numbers, and the value to look up is text, use a formula similar to the following:

=VLOOKUP(--A7,Products!$A$2:$C$5,3,FALSE)

The double unary (--) converts text to a number, and will work correctly even if the lookup values are numbers.

Lookup values are Numbers, and the table contains Text

If the lookup table contains text, and the value to look up is numeric, use a formula similar to the following:

=VLOOKUP(A7 & ""),Products!$A$2:$C$5,3,FALSE)

OR =VLOOKUP(TEXT(A7,"00000"),Products!$A$2:$C$5,3,FALSE)

The TEXT function converts a number to text, and will work correctly even if the lookup values are text. In the first example, the & operator creates a text string from an unformatted number. In the second example, a number formatted with leading zeros (e.g. 00123) would match a text "number" with leading zeros.

Spaces in one value, and not the other

Another potential cause for no matching value being found is a difference in spaces. One of the values may contain leading spaces (or trailing, or embedded spaces), and the other doesn't. To test the values, you can use the LEN function, to check the length of each value.

For example:   =LEN(A7)     will return the number of characters in cell A7. It should be equal to the number of characters in the matching cell in the lookup table.

If possible, remove the unnecessary spaces, and the VLOOKUP formula should work correctly. If you can't remove the spaces, use the TRIM function in the VLOOKUP, to remove leading, trailing or duplicate spaces. For example:

    =VLOOKUP(TRIM(A7),ProductList,2,FALSE)

Excel  Easter Eggs

This is just one of the many Easter Eggs you can get from  If you like these kinds of things, I recommend you go there and find some more.  (These instructions come from this website.) Car racing game:

•    Boot Excel 2000

•    Under file menu, do 'Save as Web Page'.

•    Say 'Publish Sheet' and 'Add Interactivity'

•    Save to some htm page on your drive.

•    Load the htm page with IE. You should have Excel in the middle of the page.

•    Scroll to row 2000, column WC. Select row 2000, and tab so that WC is the active column.

•    Hold down Shift+Crtl+Alt and click the Office logo in the upper-left.

•    If you have DirectX, you will be playing what looks like spy hunter. Use the arrow keys to drive, space to fire, O to drop oil slicks, and when it gets dark, use H for your headlights.

Note: Not everyone has been able to make this work and I'm researching why.  For example, it works when I try it at school using IE5, but not when I try it at home, using IE 5.5.  At home, I get a Microsoft Web Components box. (?)  It's possible it's because I run Personal Web Server (for FrontPage)?? I also have the Office 2k SR-1 patch and maybe this is stopping it?  Or, perhaps it's because my home computer is on a cable modem network and therefore, always connected to the internet?  But, for those of you who can do it ..have fun!!  If you have problems getting it to work and figure them out, let me know so I can pass the info onto others.

For those of you still using Excel 97, and haven't yet found its Easter Egg, try this and you will get a flight simulator:

•    Open a new workbook

•    Press F5

•    Enter X97:L97 and press Enter

•    Press Tab once, to move to column M

•    Hold Control and Shift, then right-click the Chart Wizard icon on the Standard toolbar



4