Excel Page 8


MsOffice Index 1
| MsOffice Index 1I | Microsoft PowerPoint 97 | Microsoft Bug of the MonthExcel Page 7 |   Updated 11/14/05

Page Index:


Use Excel to calculate the hours worked for any shift

With Excel, you can create a worksheet that figures the hours worked for any shift. Here's how.

To calculate in Excel how many hours someone has worked, you can often subtract the start time from the end time to get the difference. But if the work shift spans noon or midnight, simple subtraction won't cut it.

However, you can easily create a worksheet that correctly figures the hours worked for any shift. Follow these steps:

1. In A1, enter Time In.
2. In B1, enter Time Out.
3. In C1, enter Hours Worked.
4. Select A2 and B2, and press [Ctrl]1 to open the Format Cells dialog box.
5. On the Number tab, select Time from the Category list box, choose 1:30 PM from the Type list box, and click OK.
6. Right-click C2, and select Format Cells.
7. On the Number tab, select Time from the Category list box, choose 13:30 from the Type list box, and click OK.
8. In C2, enter the following formula:

=IF(B2<A2,B2+1,B2)-A2

If you enter 11:00 PM as the Time In and enter 7:00 AM as the Time Out, Excel will display 8, the correct number of hours worked.


Prevent Excel users from scrolling outside data area

In Excel, you can create a macro to prevent users from moving outside the used range on a worksheet. Here's how.

Do you find that users frequently scroll outside the data range and onto blank areas of their worksheet? You can create a macro that will automatically prevent them from moving outside the used range on a worksheet. Follow these steps:

1. Activate the worksheet.
2. Right-click the sheet name tab on which you want to limit scrolling and select View Code.
3. Enter the following code:

Private Sub Worksheet_Activate()
Me.ScrollArea = Range(Me.UsedRange, Me.UsedRange(2, 2)).Address
End Sub

4. Press [Alt][Q] and save the workbook.

Each time you activate the worksheet, this macro will run automatically to prevent scrolling outside the used range.

When you need to enter data outside the used range, you will need to reset the scroll area. You can create a shortcut key that activates a macro to do just that. Follow these steps:

1. Press [Alt] [F11]
2. Select Insert | Module and enter the following code at the prompt:

Sub ResetScrollArea()
ActiveSheet.ScrollArea =''
End Sub

3. Press [ALT][Q]
4. Press [ALT][F8] and Select ResetScrollArea.
5. Click Options and assign a shortcut key.


Sort four or more columns of Excel data at once

If you want to use Microsoft Excel's sort command to sort four or more fields at once, you need to run the sort function twice in reverse order. This Excel tip walks you through the process.

Microsoft Excel's sort command lets you sort three fields at once. But what if you need to sort four or more fields at one time? For example, suppose you want to sort your customers in this order (the customers’ first names, last names, addresses, and cities are in columns A, B, C, and D, respectively):

To do this, you will need to run the sort function twice in reverse order, as shown in the following steps:


Enter Excel data in multiple worksheets simultaneously

If you need to enter the same data in multiple Microsoft Excel worksheets, rather than copying and pasting the data from worksheet to worksheet, you can use Excel's Grouping function to update all worksheets at once. Find out how in this quick tip.

When you have a Microsoft Excel workbook that contains the same data on two or more worksheets, you could type the data first on one sheet, and then copy it on to the others. But a better method would be to use Excel's Grouping function. For example, suppose you are setting up your grade and attendance worksheets for the new semester. You will need to list each student's name on each worksheet. Follow these steps:

  1. Press [Ctrl] while clicking the sheet name tabs of the Attendance and Grades worksheets.
  2. Type the name of each student on the Attendance worksheet. As the data is typed, it will automatically be entered on the Grades worksheet.
  3. Right-click on any Sheet Name tab and select Ungroup Sheets.

Be sure to ungroup your worksheets before entering data that is not common to both worksheets.

Working With Multiple Worksheets - The rest of the story

Many times I have common headings (such as region names) and row labels (product names) in multiple worksheets of a workbook. It's easy to enter all the column and row heading text in Sheet1 and then copy it to Sheet2, but when there are multiple sheets involved (say, when I have a separate sheet for each week of the year), that can be tedious.

A better way is to make use of Excel's grouping feature.

Suppose we've renamed Sheet1 through Sheet4 as Week1 through Week4. Let's use this simple example to illustrate the time-saving nature of grouping. Here's how to enter row and column headings in all four workbooks at once:

1. Press [Ctrl] while clicking the tabs for Week1, Week2, Week3, and Week4, or click on Week1's tab, hold down the Shift key, and click on Week4's tab.

2.Enter the cell values (region names) in Week1's worksheet columns: use cells B1 through E1, for example, to enter North, South, East, and West. As you enter the data, it is automatically entered in the same cells in the other three worksheets.

3. When you're done entering region names, enter the product names down column A, beginning in cell A2. To keep this example short and understandable, we'll use three product names: enter Apple, Banana, and Cherry in cells A2 through A4 respectively.

4. Right-click on any of the four sheet tabs and select Ungroup Sheets. Alternatively, click on any of the other sheet tabs to move to that sheet. (If you want to stay on Week1's sheet, click on any other sheet tab, then click on Week1's tab. For example, I click on Week2's tab, then back on Week1's tab.)

Now the region names and product names are entered on all four worksheets.

Copy And Paste

What if you want to copy data that already exists in one worksheet to several others?

Suppose we have Week1 already set up and want to quickly copy the region names and product names to the other three worksheets in one step? (We are assuming there is no numeric sales data in the worksheet yet -- only row and column headings.)

As before, let's suppose that the region names are in cells B1 through E1 in Week1's worksheet, and that product names are in cells A2 through A4.

1. Click on Week1's worksheet tab.

2. Select cells A1:E4 (that's the full range of data -- row and column headings plus all the cells that will eventually contain sales data) and use the Copy command (Edit/Copy or Ctrl + C).

3. Click on Week2's worksheet tab, then Shift and click on Week4's tab to group those three worksheets.

4. Move to cell A1 and use the Edit/Paste command (or Ctrl + V).

5. Right-click on the tab for any individual worksheet within this selected group and choose the Ungroup Sheets option.  (Alternatively, click on any unselected worksheet tab -- such as Week1's -- to ungroup the sheets.)

Here's a slightly different approach:

1. Click on Week1's worksheet tab.

2. Select cells A1:E4 and use the Copy command (Edit/Copy or Ctrl + C).

3. Right-click on the Week1 worksheet tab. From the popup menu, choose Select All Sheets.

4. Use the Edit/Fill command, then choose Across Worksheets.

5. In the Fill Across Worksheets dialog box, pick one of the options (All, Contents, or Formats -- we typically choose All) and click OK.

6. To ungroup the sheets, click on any sheet's tab, or right-click on the tab and choose Ungroup Sheets. (Alternatively, click on any worksheet tab except Week1's tab.)

Summing Across Worksheets:

To total the data for the month, select (or insert) a fifth worksheet tab and name it Monthly Total. Since all four weeks' worth of worksheets are arranged exactly the same, pick a cell in the Monthly Total sheet that will contain numbers. For example, cell C2 will contain Apple sales in the south, since it's the intersection of the Apple row and the South column.

           =SUM(Week1:Week4!C2)

Now the value from cell C2 on the four worksheets (Week1 through Week4) will be totaled and displayed in the Monthly Total sheet's cell C2. Go ahead and enter data for Southern Apple sales in worksheets for Week1 through Week4 and then check the Monthly Total tab and you'll see that the total now appears.

Of course, so far all you have are Southern Apples. What about the rest of the data? The best answer is that you should copy the formula to all relevant cells (that is, all cells in the range B2:E4 in the Monthly Total tab).


Adjust text to fit within an Excel cell

Adjust text to fit within an Excel cellMicrosoft Excel's AutoFit feature is great when you need to resize a cell to display more text than the cell currently allows. But when you can't change the size of a cell to fit the text, this tip shows how you can resize the text to fit within the cell.

When text is too long to display in a label cell, you can use Microsoft Excel's AutoFit feature to enlarge the cell enough to fit the contents. But this can result in too much white space in the rest of the row or column.

Rather than fitting the cell to the label size, Excel also lets you resize the contents to fit within the cell. Follow these steps:

  1. Select the cell with text that's too long to fully display, and press [Ctrl]1.
  2. In the Format Cells dialog box, select the Shrink To Fit check box on the Alignment tab, and click OK.

However, sometimes this method shrinks text to the point of sacrificing legibility. An alternate method is to wrap the label text within the selected cell by selecting the Wrap Text check box on the Alignment tab of the Format Cells dialog box. Keep in mind that this method will increase the height of the cell.


Add A Line Break To Your Cell (Excel 2002/2003) 

In most word applications, pressing Enter moves the cursor to the next line. This is not the case in Excel, which at times seems to have a mind of its own. When you press Enter in Excel, it does not add a line break within a cell. Instead, it places the cursor in the cell below.

If you’re rather new to Excel, this can become a bit of an annoyance as you try to enter in data. The trick to this is that if you want to add a line break within a cell, press Alt + Enter, instead of just Enter.


Sorting Text And Numbers
Works with Excel 2000 and above

Reader Tim writes: How do I get Excel to sort/arrange a series of numbers that include letters at the end of the part number?  For example:

   100013B
  
100015
  
100015A
  
100016
  
100016A
  
etc.

--------------------------------------------------------------------------------------------------

The trick is to understand that when you enter "100015", Excel considers the entry a number.  When you enter "100015A", Excel sees the letter "A" and categorizes the entire entry as text, not a number.

There are other differences.  To see what we mean, copy the list  above to the clipboard, when open a new Excel workbook and in cell A1 use the Edit/Paste command; Excel pastes the part numbers into cells A1 through A5.

Notice that for parts that are strictly numbers, Excel right justifies the values; it left justifies any entries that contain text.  When you sort the list, Excel makes a distinction between text and numbers; this is the order of the sorted list:

100015
            100016
100013B
100015A
100016A

Note how numbers are still right justified and text is left justified.  This gives us a visual clue as to which cells are numbers and which are text. 

To get the sort order correct, you want to convert the entries that are numbers to text.

Your first thought may be to change the formatting of the numbers-only cells.  For example, if you select the two numbered cells (containing 100015 in cell A2 and 100016 in cell A4 in the original list), use the Format/Cells command, choose the Number tab, and choose Text from the category, you'll find that now all entries in the list are left justified.  That looks good -- or at least consistent.  However, when you re-sort the list, the sort order doesn't change.  The "numbered" cells -- though they now look like text -- are still numbers.  That is, internally Excel considers them numbers that are formatted to look like text -- but they still behave as numbers.

There are other solutions we thought might work but didn't.  For example, we copied the values from cells A1 through A5, then used the Edit/Paste Special command to paste only the values into a new cell range.  We sorted the new range, but it didn't work -- numbers remained numbers. 

We tried creating a second column using the formula

   =TEXT(A1,"#######")

in cell B1, and copied the formula down through cell B5.  Again, no luck  However, when we used the same TEXT function for B1:B5, then copied cells B1:B5 and pasted the VALUES (using the Edit/Paste Special command and then selecting the Values option) back over the original cells (A1:A5), the list in A1:A5 sorted correctly.  (We then deleted B1:B5.)

There are other solutions to the problem.  If you have a short list, move to any cell containing a number and place an apostrophe ( ' ) in front of the number.  That converts the cell to text (not just text format) and the entry is now left justified.  When all numeric cells are so changed, the sorted list will be in the order the reader wished.

If you haven't entered values into the cells, another approach is to format the empty cells as text; then, whenever a numeric value is entered into one of these cells, it is automatically converted to text.

Notes About Sorting

Remember, when you sort numbers that are really text, be sure that all "numbers" have the same number of digits.

For example, if you have three text entries in a column:

1000
2000
12345

Excel sorts from left to right.  Since 2000 follows 12345 (because the first digit in 12345 is larger then the first digit of 2000), the sorted list ends up as:

1000
12345
2000

To correct the problem, you must enter 1000 as 01000, and enter 2000 as 02000.

Dual-Axis Charts

One of the simplest Excel charts plots values (employee count, for example) over time (a year, perhaps). Each bar (column) in the chart indicates the total number of staff at the end of each month. This simple 12-column chart gives you a good idea of how your company's personnel count is moving. Is it stable, growing slowly, or rapidly falling? With just one series of numbers to plot, it's easy to read and see how things are going.

An Excel chart can be a powerful tool for comparing trends, too. For instance, how is head count changing compared to sales? If sales are rising but head count is falling, that tells you one thing. If sales are falling and head count is still rising, you get a completely different picture of your enterprise.

The challenge of plotting two different elements on a single chart is that the items being graphed usually have completely different scales. For example, your staff count may rise from 50 to 60 over the course of a year, while sales are measured in the tens of thousands of dollars. If you used one Y axis, say from 0 to 60, your sales would be "off the chart" -- they'd all top out at 60. If you used an axis that went from 0 to 200,000, the bars representing head count would appear to be zero.

Excel takes care of this scaling inequity by allowing you to plot two different series with independent Y axes. (Note: the Y axis on the left side of the chart for head count and the Y axis on the right side for sales.)

Here's how it works.

Suppose you have the following data in cells A1 through C7.

Staff Sales
Jan  50    140,000
Feb  52   150,000
Mar  49   170,000
Apr  53  190,000
May  55 205,000
Jun  56 195,000

To create a chart with distinct Y axes:

1. Select the cell range A1:C7.
2. Click on the Chart Wizard button in the toolbar, or use the Insert/Chart command from the main menu.
3. In Step 1 of the Wizard, select the Line chart type. Click the Finish button.
4. Excel creates a chart that shows column C's values (the sales figures). It appears that column B's data (staff count) has been ignored. Click on any bar in the Sales series, right click, and choose Format Data Series.
5. Choose the Axes tab and select Secondary axis.
6. Select OK.

Adding Axes Titles

Excel now displays two lines. The defaults from the Chart Wizard help you distinguish which line is plotted on each axis, but you can add titles to the two Y axes:

1. Click on the chart, then click on the Chart Wizard button again.
2. Click on Next until you reach Step 3 of the Wizard. Choose the Titles tab and enter values for the Chart Title and Category (X) Axis if you wish.
3. Enter "Staff" in the "Value (Y) axis" field.
4. Enter "Sales" in the "Second value (Y) axis" field.
5. Click on the Finish button.

Enhancing The Chart

Sometimes it's easier to use two different types of chart types -- one for each series of data. For example, having the staff shown as columns and the sales figures shown as a line distinguishes the two series on your chart.

To change the staff data to a column, right-click along the line that plots the staff values, choose Chart Type, and choose a column chart design.

Problems With Dual-Axis Charts

Dual-axis charts are fine if you have line charts, but big problems arise when you try to create column charts.

Prevent Excel from Opening a Blank Workbook

If you have a shortcut to Excel on your desktop to open Excel, Excel will open a blank workbook. If you do not wish this to happen, do the following:

  1. Delete the current Excel shortcut on your desktop
  2. Navigate to the location where your Office Program is located. By default it is:
    C:\Program Files\Microsoft Office\Office\Excel.exe

    Note: If you are using Office 10 the location will be:
    C:\Program Files\Microsoft Office\Office10\Excel.exe
  3. You may also find the shortcut in:
    Start | All Program Files | Microsoft Office | Office10
    or
    Start | All Program Files | Microsoft Office | Excel
  4. Once you have deleted the shortcuts, go to the Windows Explorer folder which contains the Excel.exe program file and create a new shortcut.
  5. Once created, right click on the shortcut and choose properties and then append the following in the Target section:

    /e"
    to the end of the target path (be sure to include a space before the switch). 
  6. Click OK. 
  7. Excel will not longer open to a blank workbook when you launch it using the application shortcut on the desktop.

Make subtotal values stand out in Excel

When Excel's Data | Subtotals menu is used to calculate subtotals and grand totals in a list, Excel displays the resultant grand total and subtotal headings in bold; however, their associated values are not reformatted.

If there are a number of columns between the subtotal labels and their values, you may need to reformat the values manually to make the worksheet easier to read. Or, you can set conditional formatting that will automatically reformat the values for you.

For example, suppose you want to subtotal employee payroll worksheet data by employee and gross pay per month. You may prefer for the subtotals to appear bold and underlined, while the grand total is bold and double underlined.

Before running Data | Subtotals on the worksheet, follow these steps:

1. Open the worksheet and select the raw data.
2. Select Format | Conditional Formatting.
3. Under Condition 1, select Formula Is.
4. Press [TAB] and enter the following code:

=$A1="Grand Total"

5. Click the Format button.
6. On the Font tab, select Double in the Underline drop-down menu and select Bold in the Font Style drop-down menu.
7. Click OK and then click the Add button.
8. Under Condition 2, select Formula Is.
9. Press [TAB} and enter the following code:

=Right($A1,5)="Total"

10. Click the Format button.
11. On the Font tab, select Single in the Underline drop-down menu and select Bold in the Font Style drop-down menu.
12. Click OK twice to exit both dialog boxes.

When you run Data | Subtotals to obtain the gross pay subtotals and grand totals, both the labels and the total values for each employee and grand total will now be formatted.


Retrieving the Last Value in a Column
See Also:

You may wonder if there is a way to return the last (not largest) value in a column. For instance, if there are values in A1 through A5, then you may want the value in A5 returned. Later, if values were added in A6 through A8, then the value in A8 should be returned.

There are a couple of ways that a solution can be approached. The first is to use a formula such as the following:

=INDEX(A:A,COUNT(A:A))

This formula returns the last numeric value in a column, providing that the values begin at (in this case) A1. This approach only works if all the values in the column are numeric. If the values are non-numeric, or if there are blank cells intermixed with the values, then a different approach is necessary. One way is to copy the following formula into column B, just to the right of the cells that may contain values:

=IF(ISNUMBER(A2),IF(A2<>0,ROW(A2),""),"")

In this case, the formula returns the row number of any cell in A which contains a numeric value greater than zero. The following formula can then be used to retrieve the last value in column A:

=INDEX(A:A,MAX(B:B))

This formula works because it returns the largest row number from column B, and then uses that as an index to return the corresponding value from column A.

As you can tell, returning the last value in a column can get a bit tricky at times. A clean approach is to simply develop your own VBA function that returns the desired value. In this case you can program the function to return any value--not just numeric values.

Determining the last non-empty cell in a column or row

This tip presents two useful VBA functions that can be used in worksheet formulas. LASTINCOLUMN returns the contents of the last non-empty cell in a column; LASTINROW returns the contents of the last non-empty cell in a row. Each function accepts a range as its single argument. The range argument can be a complete column (for LASTINCOLUMN) or a complete row (for LASTINROW). If the supplied argument is not a complete column or row, the function uses the column or row of the upper left cell in the range. For example, the following formula returns the last value in column B:

=LASTINCOLUMN(B5)

The formula below returns the last value in row 7:

=LASTINROW(C7:D9)

You'll find that these functions are quite fast, since they only examine the cells in the intersection of the specified column (or row) and the worksheet's used range.

The LASTINCOLUMN function

Function LASTINCOLUMN(rngInput As Range)
    Dim WorkRange As Range
    Dim i As Integer, CellCount As Integer
    Application.Volatile
    Set WorkRange = rngInput.Columns(1).EntireColumn
    Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
    CellCount = WorkRange.Count
    For i = CellCount To 1 Step -1
        If Not IsEmpty(WorkRange(i)) Then
            LASTINCOLUMN = WorkRange(i).Value
            Exit Function
        End If
    Next i
End Function

The LASTINROW function

Function LASTINROW(rngInput As Range) As Variant
    Dim WorkRange As Range
    Dim i As Integer, CellCount As Integer
    Application.Volatile
    Set WorkRange = rngInput.Rows(1).EntireRow
    Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
    CellCount = WorkRange.Count
    For i = CellCount To 1 Step -1
        If Not IsEmpty(WorkRange(i)) Then
            LASTINROW = WorkRange(i).Value
            Exit Function
        End If
    Next i
End Function

Auto/Fill Shortcut

You've probably experimented with AutoFill: you enter a value in a cell, then drag a corner of that cell down (or across) and Excel fills in the rest of the values. For example, enter Monday and Excel fills in Tuesday, Wednesday, and so on.

Here's a clever way to fill a range of cells with AutoFill'ed entries without having to drag that cell corner to the cells you want filled.

Suppose in cells A2 through A6 you have the names of employees: Adams, Baker, Carlson, Dover, and Evers. You want to assign the month when it is each person's turn to clean out the refrigerator in the break room.

Move to cell B2 and enter March. You'll assign Adams clean-up duty in March. After you type March and press Enter, Excel may change the active cell. Click back in cell B2 (See Note:), and double-click on the small square block at the bottom-right corner of cell B2. Excel fills cells B3 through B6 with April, May, June, and July.

Note: Instead of just pressing enter, you can press Ctrl + Enter and it will enter your information without moving you down to the next cell. You will stay right where you are. I like the trick, but I always forget about using it!

This double-click trick isn't only good for AutoFill. It can also quickly copy formulas, a kind of "Edit/Paste" on steroids.

To see how it works, move to cell A10. In cells A10 through A15, enter some values, such as:

   100 
   110
   120
   130
   140
   150

Now move to cell B10 and enter the formula

   =A10+25

If by pressing Enter you've changed the active cell, move back to cell B10. Now double-click the small black square in the bottom- right corner of cell B10 and the formula is copied to all cells in the range, basing the range dimension on the cell range immediately to the left of the current cell (B10 is the current cell). Since there are six cells, Excel copies the formula through cell B15.

Want to copy down without using the mouse? That's easy. Instead of double-clicking on the black square in the border of cell B10, copy the cell (use the Edit/Copy command or the Ctrl + C shortcut), press and hold the Shift key down and press the down-arrow key to highlight cells B10 through B15. Release the shift key and press Ctrl + D (think: D is for Down).


Ampersands in Excel Headers 

Reader Peter Writes: I need to include an ampersand (as in "P S & G") in the header of an Excel sheet. But no matter what I do, the ampersand doesn't appear, just "P S G." Do you know how I can get it to show?

In Excel headers and footers, the ampersand is a control code. For example, &A inserts the name of the current worksheet tab. To insert a literal ampersand you must use two of them, as in "P S && G." Excel's own help system doesn't offer an easy way to learn the ampersand codes. To get a list, choose Visual Basic Editor from the Macro menu, click on Help within the VBA Editor's menu, and search on "formatting header footer" (no quotes). This should result in a single topic entitled "Formatting Codes for Headers and Footers," which lists all available ampersand codes.

Return to Page Index


Excel's Custom Formats

Lets say that your an accountant and wanted to format a cell in Excel so that if its value is positive, it is formatted with the Accounting format and looks like this: "$ 456 ". But in the Accounting format, a zero is displayed as "$ -", which was not suitable for our purposes. We wanted the cell to display a zero as the Currency format does, like this: "$0", but to look like the Accounting format: "$ 0".

Luckily, Excel's custom number formats are very flexible. An Excel number format can have up to four distinct sections separated by semicolons that define formatting for positive numbers, negative numbers, zero, and text items. Expressed as a custom format, the Accounting format with no decimals looks like this:

_($* #,##0_);_($* (#,##0);_($* "-"_);_(@_)

Each underscore creates a space the width of the character that follows it (the character itself is not displayed). In the Accounting format, negative values are enclosed in parentheses. The format for positive numbers ends with an underscore followed by a parenthesis, creating a space the width of the parenthesis. This keeps positive and negative numbers aligned with one another. An asterisk within a number format creates as many repetitions of the character that follows it as are needed to fill the available space. Here the asterisk followed by a space has the effect of "pushing" anything before the asterisk to the left edge of the cell.

To create your custom format, first format one cell with the Accounting format. Then select Custom. The current format will be copied into the custom format area. Modify the Accounting format to change the single dash to a zero, thus:

_($* #,##0_);_($* (#,##0);_($* "0"_);_(@_)

One more thing: If you wish, you can color-code any or all of the four portions. For example, it's not uncommon to show negative numbers in red:

_($* #,##0_);[Red]_($* (#,##0);_($* "0"_);_(@_)

Only eight colors are available: Black, Blue, Cyan, Green, Magenta, Red, White, and Yellow.

Return to Page Index


Advanced Ranking in Excel

Reader Bob Writes: I have columns of students, band instruments, and tryout scores. I want to rank them automatically with some sort of formula. I know I could get the highest and lowest using Max and Min, but I need to have them ranked 1, 2, 3, and so on, from the highest score down. I also need the ranking for each instrument separately. The solution should also give the same number to any ties (there aren't supposed to be any ties, but I want to be prepared).

This seemingly knotty problem can be solved with a single formula. We'll assume the student names, instruments, and scores are in columns A, B, and C respectively, with column headers in row 1 and data starting in row 2. Select the range containing the instruments, choose Insert | Name | Define from the menu, and name that range Inst. Select the scores and name the range Score.

Click in cell D2 and type this formula: 

=B2&" "&SUM(IF(B2=Inst,IF(C2 <=Score,1,0),0))

After typing the formula, press Ctrl+Shift+Enter to identify it as an array formula. If you accidentally press Enter alone, Excel will complain—just press F2 to edit the formula, and then carefully press Ctrl+Shift+Enter. Excel will surround the formula with curly brackets ({}) to flag its status as an array formula. Copy this formula down column D to the last row containing data. You'll see that column D now contains an instrument and ranking for each student, like "Flute 2" or "Clarinet 1." In the figure, you can see that the third and fourth students are both clarinet players with a score of 71, and both are assigned the rank Clarinet 5.

How does it work? The initial portion up to the second ampersand (&) simply prefixes the instrument name and a space to the calculated rank. It's the remainder of the formula that calculates the rank. The outer IF statement compares the single cell B2 with the entire range named Inst—in an array formula, Excel repeats the calculation for every member of that range.

If the instrument does not match the value in B2, the IF statement returns 0. If it does match, it checks whether the score in C2 is less than or equal to the corresponding element in the range called Score. If C2 is less than or equal to a given value, the inner IF returns 1; otherwise it returns 0. And the SUM function surrounding it all sums up the results. When you copy the formula down the column, the relative references B2 and C2 change to match the current row, but the named ranges Inst and Score don't change.

Let's look at a specific example. In the figure, the four flute players have scores of 95, 87, 77, and 75, respectively. The score of each will be compared only to those four scores. 95 is less than or equal only to itself in this list, so that player is ranked 1. 87 is less than or equal to two scores, itself and 95, so that player is ranked 2. Each player's rank is the number of players with an equal or better score.

This precise example will be of use only to a school band director, but the principles involved can be applied to many problems in Excel.

Return to Page Index


The INT Function

The INT function rounds a number down to the nearest integer. It is useful for extracting the various parts of a real number. Use =INT(A1) to return the integer portion of the number in cell A1 and =A1-INT(A1) to return the decimal fraction portion (the part after the decimal point). One common use for INT is with the RAND function, extracting the integer portion of a random number, like =INT(RAND()*25).

Return to Page Index


The PROPER Function

Use the PROPER function to adjust the capitalization of a cell entry. =PROPER(A1) displays the contents of cell A1 in proper case where only the first letter of each word is capitalized. The functions UPPER and LOWER are similar and display text in all uppercase or all lowercase, respectively.

Return to Page Index


Four-Year Error in Excel
Works for versions 97 and above

Reader Mitch Writes: I have an Excel 2002 workbook with records from the past ten years. When I copy a sheet from this workbook to a new workbook, the dates all back up four years. For example, 25-Sep-04 becomes 25-Sep-00. I have installed all available updates with no difference in the results. I started this worksheet in some antique version of Excel, converted it to Excel 97, and then converted to Excel 2002 after it was recovered from a crashed hard drive.

Excel stores any date as a whole number of days since an arbitrary starting date. The default first date, represented by the number 1, is January 1, 1900. However, Excel also supports an alternate date system in which the default first date is January 2, 1904. This 1904 date system is the default for the Macintosh version of Excel. Somewhere in the dim and distant past, you selected that date system for the workbook that's giving you trouble. When you copy a worksheet from it into another workbook that uses the default date system, Excel interprets the dates differently, and this produces the four-year problem you've noticed. (Actually, it's a four-year-and-a-day error—25-Sep-04 becomes 24-Sep-00).

The best way to correct this is to change the antique workbook so it uses the default date system. To do that, go to Tools | Options | Calculation and deselect 1904 date system. All the dates in the spreadsheet will change to the wrong date. In a blank cell, enter the number 1462. This is the number of days between 1/1/1900 and 1/2/1904. Copy this cell. Now select all the cells with the wrong dates and select Edit | Paste Special | Add. The numbers are now correct, but they're serial numbers instead of dates. That's easily fixed by going into Format | Cells and choosing the appropriate date format.

If you would rather not deal with converting serial numbers, instead of entering 1462 as an integer enter it as a date. You can do this by typing either 1/1/1904' or =DATE(1904,1,1). When you then use Paste Special, the dates will stay dates because Paste Special also pastes the format of the source cell.

Once your workbook is changed to the default date system, you won't run into any of those four-year time warps any more.

Return to Page Index


Excel Fractions

For a fraction between zero and one, simply preface the fractional value with a "0 " (that is, zero and space). You don't have to enter a formula, and it formats the data automatically

Readers have asked about fractions like 3/16 and 5/9. To enter those, you would simply type "0 3/16" or "0 5/9" (no quotes). That is indeed easier than entering them as formulas and applying formatting separately. Also, in some situations it might be a problem to use a formula to represent data that should be a simple value. Of course, if you're entering a mixed number like 67/8, Excel will automatically handle it in the same way. It's only the numbers between zero and one that are tricky.

Return to Page Index


Display Worksheet Filename in a Cell 

Reader Stephanie writes: I know how to add the filename and path to a header or footer in Microsoft Excel. But I would like to display the filename and path in a cell. How can I do this?

The formula =CELL("filename") will give you the filename of the current worksheet. However, it will be expressed the way you'd use it in a reference within a formula, for example "C:\Temp\ [getrid.xls]Sheet1". We'll use some of Excel's text-processing functions to clean it up.

We can't assume that the current sheet name is Sheet1, but we will assume that the square bracket characters ([]) don't actually occur in the filename or path. This formula uses the FIND function to locate the right-hand bracket and the LEFT function to return the filename up to but not including it:

=LEFT(CELL("filename"),FIND("]",CELL("filename"))-1)

That still leaves the errant left bracket ([), which we can remove using SUBSTITUTE:

=SUBSTITUTE(LEFT(CELL("filename"),FIND("]",CELL("filename"))-1),"[","")

This yields the desired path and filename. If you save the file under a new name, the change won't be reflected in the cell until you press F9 or perform some action that forces Excel to recalculate.

Return to Page Index


Computing With Cells That Match Your Criteria

When you have a list of data, it's easy to use the Data/Filter/AutoFilter command and let Excel display pull-down boxes that help you quickly select data. For example, suppose you have widget sales data in a worksheet -- the salesperson's name is in one column and the number of items sold is in another column.

With the AutoFilter feature you can find all rows containing sales made by Sally Baker or see only the rows with the 10 largest sales.

When you want to perform calculations on a limited amount of data (all sales in a particular month, for example), we suggest you use Excel's database functions. Now don't panic. Yes, databases can be scary, but they need not be, especially in Excel. Excel's database functions offer a simple yet effective means for performing calculations on data that meets your criteria.

Let's suppose that we have the following data in cells A1 through D12:

Agent      Sale Date    Region     Quantity
Adams    10/04/04     North        175
Baker      10/06/04     South        165
Baker      10/06/04     East         340
Carter     10/11/04     West         150
Adams    10/16/04     East         400
Baker    10/21/04     North        170
Adams    10/26/04     West         200
Baker      11/05/04     West         250
Adams    11/20/04     North        200
Carter      12/03/04     North        350
Baker       12/08/04     East         300

What is the average sale for the West region? To find out, we need to use one of the database functions: DAVERAGE. The syntax for DAVERAGE is:

DAVERAGE(database, field, criteria)

The database parameter contains the range of cells that make up the list. In our case, we must include (at the very least) the Region column (since our criteria is based on Region) and the Quantity field (since we want to know the average quantity sold).

The field parameter in the formula refers to the column name or column number (relative to the first column in the database) that defines which field we want to use when performing the math. (In our example, this parameter should refer to the Quantity field because that's the column containing the values we want to average.)

The criteria parameter refers to the range of cells containing the conditions (in our example, cells that specify we want region equal to West). We'll explain criteria shortly.

Syntax Notes: Column Headers

Though the database range can be either a cell range or named range, that range must include a header row that contains the names of the columns. Therefore, our range must include a cell from row 1.

The "criteria" parameter must include a row with column headers as well as a row with the filter conditions below that row. In our example, we need to specify a two-cell range. This range will contain a header row (containing Region) and a cell underneath this header row containing the actual criteria (West).

SETTING UP THE CRITERIA:

We know the database range (cells A1 through D12). Note that our database range includes a row (row 1) containing the column headers.

We also know the field parameter must refer to column D, which is the fourth column (relative to the first column -- column A -- of the database).

Now we must set up the criteria. Fortunately, it's very simple. Move to an area beyond the data list. We'll use cells F1 and F2.

1. In cell F1 enter Region. Remember that we want to filter based on region.
2. In cell F2, enter the value of the region. We want to calculate only the values from sales in the "West" region, so enter West in cell F2.

FINDING THE AVERAGE:

Now let's put everything together and perform the calculation. 
Move to cell A14 and enter

Average Quantity

Move to cell D14 and enter this formula:

=DAVERAGE(A1:D12, 4, F1:F2)

We're asking Excel to compute the average. The database range is from A1 through D12, and implied with that range is that row 1 contains column headings. We want to perform the average on values in the fourth column of the database. Our criteria can be found in the cell range F1 through F2.

Excel computes the value of the formula: 223.75

Move to F2 and enter South, and the calculated DAVERAGE is now 165 (there was only one sale for the Southern region.

TIP: CAPITALIZATION

Searches are case insensitive. That means capitalization doesn't matter. If you enter "west" for the region criteria (in cell G2), Excel matches "West" and "west" in the database.

Return to Page Index