Posts Tagged ‘excel 2002’

Formulas To Determine Quarters For Calendar And Fiscal Years (XL2000-2010)

Sunday, March 6th, 2011

If you ever need to determine in which quarter of the year a date falls, it’s pretty easy, right? Jan-March is Quarter 1, April-June is Quarter 2, etc…

But what if you need to determine this value for many date cells in your worksheet? Or maybe you have formula in your worksheet whose calculation is based on the quarter. Here’s a formula that makes determining which quarter of the year a date falls a snap.

=ROUNDUP(MONTH(A2)/3,0)

In this example, A2 is the cell containing a date.

Formula to determine quarter of year

In many areas of business it is often necessary to determine in which quarter of a Fiscal Year a date falls. Here is a formula that you can use…

=MOD(CEILING(22-FY_Start_month+ MONTH(Cell_Ref),3)/3,4)+1

In this formula, the variables are FY_Start_Month, which is the month in which your Fiscal Year begins and Cell_Ref is the cell containing the date you want to evaluate.

In this example, the fiscal year begins in October, and the date you want to evaluate is in cell A2. So the formula is…

=MOD(CEILING(22-10+MONTH(A2),3)/3,4)+1

Formula to determine fiscal quarter of year

Microsoft Excel VLOOKUP Can Save You Many Hours Of Work (Excel 2007, Excel 2003, Excel 2002, Excel 2000)

Sunday, May 10th, 2009

There’s a very powerful function in Microsoft Excel that many Excel users don’t know about.

Whether you are using Excel 2007, Excel 2003, Excel 2002 or Excel 2000, it could be saving you hours and hours of work.

It can reduce many hours of manual work down to just a few seconds once you’ve got it set up. I recently showed it to a new co-worker of mine and it reduced two days work down to a few seconds work.

Imagine what that would be worth to you.

How much time would that save you in the run of a year?

What would that be worth to your company?

VLOOKUP is a function that is used in a worksheet to return a value from a table (either in the same sheet, another sheet or another workbook) that is related to the value you give it.

Say you have a parts list in a table on one sheet containing thousands of parts numbers and their related information. The table shows the part number in the first column, the part name in the second column, the part price in the third column. On a separate sheet you have an invoice with columns for the Quantity, the Part #, the part Description, the Price and the Total.

Excel VLOOKUP example

Reduce hours of work down to seconds when you use Excel's VLOOKUP function


The Description and Price columns in the Invoice sheet contain VLOOKUP functions. When you enter the part number in the Part # column on the Invoice, the VLOOKUP function in the Description column looks down the first column of the parts table until it finds a match for the part number you entered, it then looks across the row for the related Description and returns it to the Invoice sheet. A similar function finds the price for the part number.

Here’s the syntax of a VLOOKUP Function:

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

In this sample the formula is =VLOOKUP($D19,parts_list!$A$1:$C$22,2,0)

The lookup_value is the value to search in the first column of the table.

The table_array can be two or more columns of data. You can use a reference to a range or a range name. The values in the first column of the table are the values searched by lookup_value. These values can be text, numbers, or logical values.

The col_index_num is the column number in table from which the matching value must be returned. A col_index_num of 1 returns the value from the first column in table; a col_index_num of 2 returns the value from the second column in table, and so on.

Range_lookup specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE (or 1), an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned. The values in the first column of table must be placed in ascending sort order; otherwise, VLOOKUP may not give the correct value. If FALSE (or 0), VLOOKUP will find only an exact match. In this case, the values in the first column of table do not need to be sorted. If there are two or more values in the first column of table that match the

lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned.

To avoid #N/A errors when a VLOOKUP function does not find a match, modify the formula with an IF statement

=IF(ISNA(formula),””,formula)

=IF(ISNA(VLOOKUP($D19,parts_list!$A$1:$C$22,2,0)),”",VLOOKUP($D19,parts_list!$A$1:$C$22,2,0))

I hope this helps you understand the power of the VLOOKUP function.