Welcome To Bo's Excel tips & tricks page 5

BULLETS IN EXCEL
You can set up a style to make it easy to apply bullets in Excel spreadsheets. One note: when you type the character number for the bullet (Alt+0149), you need to use the numeric pad, on the right side of your keyboard. If you use the numbers directly above the letters, Excel will just beep at you. You can find an exhaustive list of common character numbers at http://www.bbsinc.com/symbol.html Note that if you add more spaces after the bullet, Excel "indents" by as many spaces as you type.

If you want to make the bullet style available in all of your worksheets.
Then click File | Save As, in the File Name box type book, in the Save as type box, pick Template (*.xlt), and click Save.

Two additional ways to apply bullets, once you've set them up in a cell: "Copy the cell that has the bullet style in it. Select the cell or cells in which you want to apply the style. Choose Edit | Paste Special, then select Formats. Voila. That's really not much faster but will give you whatever other formats you have in the cell as well as the bullets. Or, you can select a cell that has the bullet. Click the Format Painter toolbutton on the Standard toolbar. Select another cell or range of cells where you want to apply the bullet style, and there it will be. To use the format painter more than once, double click on the button and then wherever you click, the format you're "painting" will be applied. Doing the "more than once" thing requires that you turn the Format Painter off when you're done by simply clicking the button again."

"The click on 'Text' option step is unnecessary. The '@' symbol is defined as a custom number format, so you can just type it over the word 'General' and it will work."

To make your data easier to read, color-code it with the Conditional Formatting feature. Select the desired cells, then choose Conditional Formatting from the Format menu. In the first field of the dialog box, choose which value you want to base the condition on (for example, Cell or Formula). In the second field, select the terms (for example, Between or Greater Than Or Equal To). In the third and fourth fields, choose the desired numerical range. To specify the cell's appearance, select the Format menu, then experiment with font, border and pattern (which includes color). Click on OK to complete the first condition, then repeat the process to add more conditions.

Q. I have an Excel Worksheet with data in cells A1:B10 plotted on a chart. Each day I add another row of data. How can I make the chart range grow automatically, so that tomorrow it is charting A1:B11, the following day A1:B12, and so on?

A. Lets assume you are working in sheet one with column headings in ROW 1 and days in column A. Data relating to each date is in column B. Using Insert | Name | Define, type each name in the Names in workbook area, and then type the corresponding formula in the refers to area. Click Add and type in:

Days=OFFSET (Sheet1! $A$2,0,0,
COUNTA (Sheet1!$A:$A)-1)
PlotData=OFFSET (Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1)

Click on the series in your chart so that the =SERIES formula appears in the formula bar. Edit the second and third arguments in the formula, replacing the reference to the Column A range with the range name Days and the reference to the column B range with the range name PlottData. The result should be something like this:

Data printed on greenbar computer paper can be much easier to read than rows and rows of data printed on plain white paper. If you don't have access to special paper, you can reproduce the effect in your worksheets. The first step is to color the first row or rows. Then, select those rows plus an equal number of blank rows and click Format Painter (the button with a paintbrush on it). Next, select the remaining rows in your sheet area. Excel will copy the colored and blank rows to fill the rest of the sheet area. Generally, it's best if you apply this format to a sheet before adding data. Now, let's look at a quick example. In a blank sheet, select row 1, then select any color from the Fill Color palette on the Formatting toolbar. (Generally speaking, a lighter color is better.) Now, select rows 1 and 2 and click Format Painter on the Standard toolbar. Select rows 3 through 20, and Excel will alternate the color and blank rows accordingly.

In the previous issue of Excel Energizer I looked at some of your options for creating sums based on two conditions where the conditions were of the type "Sum Wages where Staff = Yes and Department = Accounts". This week I'll show you how you can make calculations which use OR conditions to make calculations of the type, for example, "Sum Wages where Staff = Yes or Department = Accounts".

A breif review: example, here's a sample worksheet fragment to enter so you can test the formulas:
Or see: MANAGING CONDITIONS IN EXCEL

        A     B       C
1 Staff Wage Department
2 Yes    350 Accounts
3 No     100 Sales
4 Yes    200 Sales
5 No     300 Accounts

One of the formulas from last week, which we used to add the Wages for employees who were both staff (Yes) and who worked in Accounts was this:

This formula works on the basis that each test will evaluate to TRUE or FASLE (1 or 0) and the results of both tests are multiplied together. Only if both tests evaluate to TRUE (1) will the result of the multiplication be 1, and the Wages amount for that row will be included in the total.

You can use this same process to make an OR calculation, but this time you should add the results, rather than multiplying them. Now, when the results of the tests are evaluated and added, the Wages amount in any row in which any one of the tests evaluates to true will be included in the result. If both tests evaluate to true, the amount will still be included. So, this formula will sum all the Wages for all employees who either work in Accounts or who are staff:

Remember that this is an array function, so you must press Control + Shift + Enter after you've typed it for it to work. If you don't, press F2 to edit the cell and then press Control + Shift + Enter. You'll know you've got it right when you check the cell formula and it's surrounded by curly brackets {}.

Similarly, you can use array formulas to count the number of employees who either work in Accounts or who are staff:

But the action doesn't stop here. You aren't limited to COUNT and SUM and you can use other functions like AVERAGE for example. Try these functions which average the Wages for both our previously stated AND and OR test criteria:

Once you see how easy it is to create array formulas you can extend them to calculate multiple criteria, for example, this will calculate the Wages for all employees who work in Accounts and are Staff, or those who are not Staff and who work in Sales:

=SUM(IF(((A2:A5="Yes")*(C2:C5="accounts"))+((A2:A5="no")*(C2:C5="sales")),B2:B5))

When you need to do tedious calculations like this, array formulas allow you to reduce the workings to a single cell which is both neat and very easy to troubleshoot when things go awry.

You already probably know that you can use the SUMIF and COUNTIF functions to either sum a range of values or count the number of matching entries based on a single criteria. If you're unsure how these work, the formula let's look quickly at one.

The formula =SUMIF(A2:A5,"Yes",B2:B5) will add up all the values in B2:B5 for which the corresponding entry in A2:A5 is Yes and it will return 550 (350+200).

COUNTIF works similarly and counts how many matching entries there are. It would read =COUNTIF(A2:A5,"Yes") and would return 2. But how do you handle the situation where you want to test for more than one condition. For example, if you want to know how much you've paid to Staff in the Accounts department? SUMIF doesn't handle multiple criteria so you can't use it.

To do this you can use an array formula. An Array formula is a more complex formula to create but it works very well in situations like this one. The array formula which will solve our problem is:

These formulas only add the corresponding entry in column B if the corresponding tests on the data in columns A and C return true values. Excel calculates this by treating the values in columns A, B & C as one dimensional arrays and it performs the calculations using these arrays. It evaluates the tests on the data in row 2, then row 3, then row 4 and finally row 5 and then sums the results.

However, you can't just enter either of these formulas into a cell and expect it to work - it won't unless you press Control + Shift + Enter which indicates to Excel that you're using an Array formula. If you forget, you'll see #VALUE! appear in the cell and you should select the cell, press F2 as if to edit it and press Control + Shift + Enter to enter the formula in the correct manner.

Array formulas like these make multiple calculations and return a single result from them. While many calculations can only be made using array formulas, this is one calculation you could have made another way -- using Excel's Conditional Sum Wizard add-in. Test this by loading the Add-in -- choose Tools, Add-ins, enable the Conditional Sum Wizard and click Ok. You'll find it is now installed on the Tools menu - select Wizard then Conditional Sum. To create the formula, first select the entire data area as the list to work with, then specify the two tests which must be met (the Staff value must be equal to Yes and the Department value equal to Accounts, and finally indicate the cell to contain the answer. The resulting formula will be different to the two we've shown but works just as well.

PASTING UNFORMATTED TEXT IN EXCEL AND POWERPOINT
The Macro
TAMPERING WITH A RECORDED MACRO
Let me take advantage of this opportunity to show you a bit more about Office macros in general. To do that, I'd like you to crank up the Macro Recorder - the little gizmo that looks over your shoulder, and creates macros based on what you do. In theory, you can record your actions, have them stored away in a macro, and "play them back" by running the macro.

I almost never recommend that people use the Macro Recorder. Through no fault of its own, the Recorder isn't capable of recording most macros the way you want them recorded. Most of the time, if you record a macro with the Recorder, it won't "play back" the way you expect it to. Sometimes the problems are so obscure you really need to understand Visual Basic for Applications in order to make heads from tails out of the recorded macro. (A lot of it has to do with the cursor location, and how the Recorder has to guess what you are doing when you click or move the cursor. Details and examples are in the books listed in the preceding section.) Adding injury to insult, PowerPoint has a weird way of storing macros, so you even if you could get the macro to work, you could only make the unformatted paste work for individual presentations.

The Macro
In this case, though, recording a macro can kind of get you kick-started, taking care of several behind-the-scenes details that are a pain in the neck to do by hand. It'll also show you how PowerPoint's macro Recorder doesn't work worth squat. So I'll dispense with my usual Recording caveats and cut to the chase.

Here's how to create a PasteUnformatted macro in Word or Excel - and how you should be able to create one in PowerPoint:

> Start the application - Word, Excel or PowerPoint (the other Office apps don't have macro recorders).

> Copy some text. It doesn't matter where you get the text, or what it is. You just need to put something in the Windows clipboard.

> If you're using Word, make sure the line "Store Macro In" says "All documents (Normal.dot)" and click OK to start the Recorder.

> If you're using Excel, "Store Macro In" should say "Personal Macro Workbook" (and you'll save yourself a bit of time if you tell Excel to assign this macro to the Shortcut key Ctrl+V). Click OK to start the Recorder.

> If you're using PowerPoint, you are pretty much stuck with putting the macro in a specific presentation, which is a real pain. (Yes, I know there are alternatives, but none of them are much good.) Choose the presentation and click OK to start the Recorder.

> With the Recorder running (you can see it sitting in its own window), click Edit | Paste Special | Unformatted Text (in Word or PowerPoint) or Text (in Excel).

> While you're here, savor the consistency among Office apps. Word is quite different from Excel, and both are quite different from PowerPoint. You can't even record a macro in Access. In Outlook, you can only record a macro if you're using Word as your email editor - and then you're actually using the Word Recorder. But I digress.

> When Word, Excel, or PowerPoint is done with the Paste, click the Stop button on the Recorder.

In the next section I'll show you how to take a look at the macro that was recorded.

TAMPERING WITH A RECORDED MACRO
If you were following along in the preceding section, you now have a recorded macro called PasteUnformatted. What you do next depends on whether you're trying to get PasteUnformatted to work in Word, Excel or PowerPoint.

In Word, click Tools | Macro | Macros and pick PasteUnformatted. Click Edit. You'll see something like the macro I gave you in PASTE UNFORMATTED TEXT IN WORD

More on the Word Macro here

In Excel, you have to "Unhide" your Personal workbook before you can look at the macro. (Ah, consistency among the Office apps. Ain't it wunnerful.) To do so, click Window | Unhide, pick Personal.xls and click OK. At that point, you can see the macro you recorded by choosing Tools | Macro | Macros, clicking PasteUnformatted, then Edit. Here's the macro I recorded:

Sub PasteUnformatted()
'
' PasteUnformatted Macro
' Macro recorded 10/24/2001 by Woody Leonhard
'
' Keyboard Shortcut: Ctrl+v
'
      ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
          False
End Sub

The Recorder sticks in a lot of unnecessary junk. If you go
into the recorded macro and edit it so it says, simply:

Sub PasteUnformatted()
      ActiveSheet.PasteSpecial Format:="Text"
End Sub

It'll work.
PowerPoint is a basket case. In PowerPoint, click Tools | Macro | Macros, pick PasteUnformatted, and click Edit. Visual Basic for Application appears, and shows you that PowerPoint's Recorder has created a macro that looks something like this:

Sub PasteUnformatted()
'
' Macro recorded 10/24/2001 by Woody Leonhard
'

End Sub

If you've ever seen a macro before you might notice something's not quite right with this recorded macro. The PowerPoint Recorder neglected to record anything! That macro doesn't do a thing.

I played with it a bit, and discovered that you can paste the contents of the clipboard into the current slide by using the command ActiveWindow.View.Paste

But I'll be hanged if I can figure out how to paste text only, or do a Paste Special of any type. If one of you PowerPoint gurus can feed me a clue, please do so! |

Please click here

I'm a firm believer in the philosophy of not doing anything I can con, cajole or just plain bribe someone else to do for me. Well, Ok, I'm exaggerating a bit, but I really hate doing unnecessary work. That's why I love all the quick fill and list tools in Excel and why I'm always looking for a way to save a keystroke or three.

This week I have a range of quick solutions for entering data into Excel. They won't do all the work for you, but they can relieve you of some of the burden of repeated data entry.

The first offering is Excel's list options which will fill a range of cells with related data such as days of the week and months of the year. If you haven't used it before, go ahead and type January into a cell. Now select the cell and position your pointer over the bottom right hand corner of the cell so it changes to a small plus symbol, grab it with your mouse and start dragging. It doesn't much matter which direction you go but down or to the right is the most usual. If you hold your mouse button steady for a second or two, the later versions of Excel will show a tooltip at the mouse pointer telling you the month that will go into the last cell you have selected if you let go the mouse right now. Adjust your mouse position until you get December in the tooltip and let go. Voila! the months of the year are entered for you.

You can start this series at any month, so you could have begun at June, for example. You can also type three character abbreviations for the months and Excel will follow suit, e.g. Jun, Jul, Aug etc.. If you're using Excel 2000 or later and you have the first cell you enter already formatted, the formatting will be copied to the cells along with the month names.

This also works with the Days of the week using either their full names, e.g. Monday, Tuesday or their three letter abbreviation.

DIY LISTS
While showing others how to use these built in lists is a great party trick -- you can go one step further and create your own. So, for example, if you routinely enter the names of a list of people, or offices or states and provinces you can easily create these as a list so they'll perform just like the days of the week and the months do.

Laura Stewart covered the creation of lists in some detail in WOWMM volume 1, Issue 4 so I suggest you check that out. The shortened version is this; choose Tools, Options, Custom Lists and type the list items, in order, in the dialog with each item separated from the next by a comma. Click Add to create these entries as a list. Now to try it out, type one entry from your list and drag away to fill adjacent cells with your data.

QUICK FILL
If you're still not totally impressed, let's look at the Data Fill options which can make short work of tasks like entering days of the week, for example. In a cell in a workbook type the date for a Monday, select the cell and use your mouse to locate the fill pointer in the bottom right of the cell and this time drag it down or across the workbook using your right mouse button. When you let go you'll see a range of options, including ones to fill using Fill Days, Fill Weekdays, Fill Months, Fill Years etc.. Using days will fill each cell with the successive day of the month, using Weekdays will omit Saturdays and Sundays from the dates and Months and Years will increment the date you've entered by one month or one year at a time. It also accommodates shortened months like February and the 30 day months too.

Until now, you may have noticed that, when you type a number, for example, the number 1 or even the year 1999 and drag the fill handle you'll fill the selected cells with the same date. However if you use the right mouse button to drag with, you'll be able to choose Fill Series from the menu to enter the numbers 1, 2, 3 etc., or the years 1999, 2000, 2001 etc..

You can also enter data in a series you choose, for example, you may want to enter the dates for successive Mondays. Enter the starting Monday's date and use your right mouse button to drag the fill handle down or across a number of cells. From the shortcut menu, choose Series (not Fill Series) and set the Step value to 7 (the number of days between one Monday and the next). Set the Type to Day and the Date Unit to Day and click Ok and the dates will be entered for you. If you don't want the dates to go beyond a certain point, you can enter that 'stop' date in the Stop value box -- just ensure you enter the dates in the format Excel expects according to your country and date format. If you're unsure, using the format 11-Nov-2001 should work just fine.

QUICKLY CREATE DUPLICATE ENTRIES
When you have data entered on one worksheet that you want to copy to other worksheets, you can do it using a single step (and one which doesn't use the Copy button!). Begin by selecting both the sheet that is complete and the sheets you want to copy the entries to. To do this, click the sheet tabs to select them while holding down the Control key or click the first tab and hold the Shift key as you click the last in the series. Now select the actual cells which contain what you want to copy, and choose Edit, Fill, Across Worksheets. You can then choose to fill All, Contents or Formats as you require. To return to work on just one worksheet, click on a worksheet tab to select it or right click a worksheet tab and choose Ungroup Sheets.

If you know ahead of time that you'll be wanting to enter the same data on a number of worksheets you can select the worksheets before you begin and then enter the data into one of them. It will then be automatically entered in all the others you have selected.

Another quick and easy way to enter data is useful when you want to enter the same value into a number of cells. To do this, select the cells, type your data into one of them and press Control + Enter to enter it into them all. You can do this for cells which are adjacent and those which aren't (hold the Control key to select non adjacent cells).

This selection is merely the 'tip of the iceberg' of possible ways you can shortcut your day's work in Excel. Keep your eyes open and you're sure to find plenty more.

Autofill
All recent versions of Excel are able to fill in a sequence of cells based on the few you've typed.

For example, enter 1 into a cell then 2 into the one next to it. Select both then drag that selection (using the little box in the bottom right corner of the selection) across. Excel will fill the new cells you select with the numbers 3, 4 ,5 and so on.

It gets even cleverer - type in 2 in one cell and 4 in the next, extend the selection as you did before and the numbers are 6, 8, 10 etc. You can enter any two numbers and Excel will try to guess the series:
10, 20 will continue with 30, 40, 50 etc
5, 10 continues 15, 20 etc

It also works in reverse:
100, 95 continues 90, 85 and so on into negative numbers if you wish.

F Back to Autofil Index

Autofill For Text
This same idea extends to other non-number lists like Days of the week and months.

Type in Jan in one cell, Feb in the next and extend those cells out to see Mar, Apr, May appear. This also works for the full name of months, starting with January.

Custom Autofill
Going to Tools | Options | Custom Lists lets you see where Excel has the days of the week and months listed in long and short form.

You could alter those lists if you wanted to though I can't imagine why. Perhaps you have a really autocratic boss who has decreed that Wednesday is to be named after him instead of Woden ? <g>

This is also where you can add new lists to suit your particular needs. Click on New List type in your list entries on the right side, finish with Add to include it in the list of Autofill lists.

To edit an existing list, click on that list summary on the left side then the whole list will appear on the right for editing.

The common example used of custom lists is a list of staff or team members but that's probably not a good practical example because a list of staff can alter.

Custom lists are probably better for lists that are unlikely to change but you use regularly. Some possibilities are:

List of family members - Mum, Dad, Belle, Claudia etc.
Floors in a building: Basement, Car Park, Ground, Mezzanine, 1, 2 etc.
Decks on a ship (where the decks have names): Mozart, Puccini etc

In the spirit of the season you could have a list of apostles, books in the old or new testament or reindeer in the famous poem.

F Back to Autofil Index

Importing A Custom List
On the custom list dialog there's an option to import a list from a series of cells. This gives you an easier way to create and manage a list.

Type in your list into a column of cells, then go to Tools | Options | Custom Lists and then the import button. Select the range of cells then Import to bring the list into a new custom list.

F Back to Autofil Index

Reordering A List
There is no way to directly reorder a list, all you can do is cut and paste the list into the order you want.

Or you can autofill the entire list into a worksheet, edit or reorder the list using normal Excel features then import it into Custom Lists to replace the original list.

F Back to Autofil Index

Multi-Lingual Lists
You can also add lists in different languages. You may need to insert days of the week in French, German or some other language. Since Excel inserts only the lists applicable to your default language, you can add other 'parallel' lists in other languages.

F Back to Autofil Index

Limitations Of Autofill
Autofill is a nifty idea but, like too many other features it really doesn't go far enough.

Most obviously, the lists are not dynamic. They cannot be linked to a series of cells or other data source. Therefore you have to remember to change the custom list as circumstances change.

In addition once you have autofilled a list into a worksheet, that list doesn't change if the source custom list changes. The commonly used 'staff members' example is a good instance of this limitation. If a staff member is replaced, your existing custom lists in worksheets won't change automatically (which may, or may not, be what you want).

If you forget to change a custom list, inattentive staff might accidently use an outdated list which could have annoying or more dire consequences.

Tip: If you have a lot of custom lists, create a worksheet that has all the lists in a series of cells. This makes for easier reference, changing and updating the custom lists as necessary.

Question: Request for assistance in taking autocorrect autotext from MS Word 97 to a spread sheet in excel for a data base. Can you help? Thank you in advance for your consideration.

Not to sure what you are asking here. Word and Excel are a tad different in the way they handle similar or like data.

That being said, and I am taking a shot in the dark here, I am assuming that you want to lessen the hassle of constantly entering like data into cells? This is accomplished with the use of Fill Handles in Excel.

The small black square in the corner of the selection (See figure 1 below). When you point to the fill handle, the pointer changes to a black cross ( + ). To copy contents to adjacent cells or to fill in a series such as dates, drag the fill handle. To display a shortcut menu that contains fill options, hold down the right mouse button as you drag the fill handle. Types of series that Microsoft Excel can fill in for you

You can automatically fill in several types of series by selecting cells and dragging the fill handle or by using the Series command (point to Fill on the Edit menu, and then click Series). To select the type of series from a shortcut menu, select the starting values for the series; then hold down the right mouse button as you drag the fill handle.

What is auto fill, part due? Well, pictures are worth a thousand words. Here is an example of a string of dates in cell A, by grabbing the + on the lower right hand side of the auto fill and dragging it accross you can auto fill the rest of the days of the week. Please see figure 1 below. This also works if you drag vertically or to fill a series by holding the right mouse button. Experiment a little, you'll get it.

A time series can include increments of days, weeks, or months that you specify, or it can include repeating sequences such as weekdays, month names, or quarters. For example, the initial time selections in the following table result in the series shown.

The AutoFill feature extends several types of series as shown in the following table. The fourth example shows how Microsoft Excel can extend part of a selection (Product 1) and copy another part (On backorder). The last example is a best-fit trend.

When you create a linear series by dragging the fill handle, Microsoft Excel increases or decreases values by a constant value that is based on the selected starting values. When you create a growth series by selecting the Growth Trend command from the shortcut menu, Microsoft Excel multiplies values by a constant factor. For information about linear and growth series that you can create with the Series command, click .

If you have a series of cells all layed out in a workbook, you can import that data into the custom list.
Here is how: See figure below

The differences in the way each program, MsWord and MsExcel do things is the reason that they aren't compatable.

Ensure the data in the table is accurate, then open the Data menu and choose Filter. Click AutoFilter and a down arrow will appear on the top cell of the range. Click on the arrow and choose the minimum value desired. To display the entire table again, click the down arrow and select All. To completely disable AutoFilter, select AutoFilter from the Data menu again.

Using two Page Setup options can reduce and enlarge your worksheet data for printing purposes. If you use this method, you should be sure to check your page breaks when resetting these options. Chances are if you change print options, you'll need to adjust any manual page breaks you've set. To quickly reset all page breaks, switch to Page Break Preview by clicking Print Preview, then Page Break Preview. Right-click any cell on the sheet and choose Reset All Page Breaks from the context menu. This action removes manual page breaks and restores all automatic page breaks.

THREE WAYS TO DO THE SAME THING IN EXCEL - PART 1
+Cell References
THREE WAYS TO DO THE SAME THING IN EXCEL - PART II
+VLOOKUP
THREE WAYS TO DO THE SAME THING IN EXCEL - PART 3
+Writing a VBA script
+Visual Basic (Applications Edition) VBA
+Trhe Script Part 1
+The Script Part 2

By: Wayne A. Newton, Bytewise Systems

In this article, we will describe a simple problem and solve it using a conditional formula in an Excel cell (also known as a "nested IF").

These first three articles will show three different approaches to solving a single problem. There are always many ways to solve any problem and you will, no doubt, consider others when reading these articles. The important thing to remember is that you should consider alternate ways of solving any problem and then you can determine which the best is for you.

Let's start by defining the problem. We have been asked to calculate the number of Frequent-Flyer points we collect
when we fly ABC Airlines. Frequent-Flyer points are calculated according to the following table:

**Table 1**
	E	F	G
1	Points	Class-Discription	per mile
2	0	Discounted Economy	0.70
3	1	Economy Class	1.00
4	2	Business Class	1.25
5	3	First Class	1.5

According to the table, we will collect 1,000 points if we travel 1,000 miles in Economy Class and 1,500 points in First Class, etc.

A quick look at the problem shows us that there are two inputs: miles flown and class flown and depending on the class flown, we use a pre-determined number to multiply by
the number of miles flown and generate the result.

To begin, open a new Workbook in Excel and enter the data as shown below in Table 2. We will use this as test data to compare all three methods:

For the moment the class (Business, First etc) are represented by numbers. We've seen that done is real world worksheets by well meaning people and so use it here as a
cautionary tale <g>. We'll fix that later in this series.

To test the nested IF, enter one of the two following formulae into Cell C2:

=IF(B2=0,A2*0.7,IF(B2=1,A2*1,IF(B2=2,A2*1.25,IF(B2=3,A2*1.5,0))))
or
=A2*IF(B2=0,0.7,IF(B2=1,1,IF(B2=2,1.25,IF(B2=3,1.5,0))))

Both of these formulae are functionally identical. You choose which one you prefer, you say tomato and I say tomato.

Now, copy this formula down column C (Cells C3:C5). Use a calculator (or the calculator program supplied with Windows, or even the one on your desk!) to check that you have the correct results.

Table 2
	A	B	C
1	Miles Flwon	Class Flown	Points (By Formula)
2	200	0
3	25,460	1
4	15,950	2
5	33,650	3

The results should be correct, but the formula is fairly clumsy, difficult to debug and difficult to maintain. Additionally, a novice Excel user may not understand what it is supposed to do, even an expert will have to glare at it for a second or two.

Cell References
Instead of explicitly giving the values you could reference the list on the worksheet so the formula looks like this:

=IF(B2=0,A2*G2,IF(B2=1,A2*G3,IF(B2=2,A2*G4,IF(B2=3,A2*G5,0)))) or =A2*IF(B2=0,G2,IF(B2=1,G3,IF(B2=2,G4,IF(B2=3,G5,0))))

This gives you the chance to change the multipliers in one place (the G column) to affect the whole worksheet. For example, if the First Class rate changes to double then just change cell G5 to 2.0 and all the relevant points will be recalculated.

You could improve the readability a bit by assigning Range names to the class multipliers eg instead of 0.7 or G2 you could give it a name like DiscEconomyMultiplier .

Do that by clicking on cell G2, from the menu choose Insert | Name | Define, type in the name 'DiscEconomyMultiplier' then OK. Repeat for cells G3, G4 and G5 using different names.

=IF(B2=0,A2* DiscEconomyMultiplier,IF(B2=1,A2* EconomyMultiplier,IF(B2=2,A2* BusinessMultiplier,IF(B2=3,A2* FirstMultiplier,0))))

=A2*IF(B2=0, DiscEconomyMultiplier ,IF(B2=1, DiscEconomyMultiplier,IF(B2=2, BusinessMultiplier,IF(B2=3, FirstMultiplier,0))))

at least now you can see what the G2 reference is supposed to be just by reading it's name.

Whether you use numbers, cell references or named ranges, the nested IF statement is often seen in Excel worksheets despite the fact that it is messy to read, easy to get wrong and almost impossible to understand. A three part nested IF is one thing, but we've seen people doing it for 10 options or more!

We've set the groundwork we'll look at the better options in detail in a little while. Check back often, more to come.

THREE WAYS TO DO THE SAME THING IN EXCEL - PART II
By: Wayne A. Newton, Bytewise Systems

Welcome to the second in a series of articles where we will show how Excel can be used to solve simple day-to-day problems in different ways.

In the previous article (THREE WAYS TO DO THE SAME THING IN EXCEL - PART 1), we described a simple problem and solve it using a conditional formula in an Excel cell (also known as a "nested IF"). It's works but it's messy.

In this article, we will take the same problem and solve it using Excel's powerful VLOOKUP() Function. You may have seen this function and even tried to make it work! We'll take you step by step through it.

Let's start by re-defining the problem. We have been asked to calculate the number of Frequent-Flyer points we collect when we fly ABC Airlines. Frequent-Flyer points are calculated according to the following table:

Table 1
A	E	F	G
1	Class	Description	Points (per mile)
2	0	Discounted Economy	0.70
3	1	Economy Class	1.00
4	2	Business Class	1.25
5	3	First Class	1.50

+---+-----+------------------+----------+
|   | E +        F          +    G      |
+---+-----+------------------+----------+
|   |     |                   | Points |
| 1 |Class|   Description    |(per mile)|
|   +-----+------------------+----------+
| 2 | 0 |Discounted Economy|      0.70|
| 3 | 1 |Economy Class     |       1.00|
| 4 | 2 |Business Class    |       1.25|
| 5 | 3 |First Class       |       1.50|
+---+-----+------------------+----------+

According to the table, we will collect 1,000 points if we travel 1,000 miles in Economy Class and 3,000 points if we travel 2,000 miles in First Class, etc.

A quick look at the problem shows us that there are two inputs: miles flown and class flown and depending on the class flown, we use a pre-determined number to multiply by the number of miles flown and generate the result.

To begin, open a new Workbook (or you can use the Workbook from the last instruction and insert a new Worksheet) in Excel and enter the data as shown below in Table 2. We will use this as test data to compare all three methods:

Table 2
	A	B	C
1	Miles Flown	Class Flown	Points (by Lookup)
2	200	0
3	25,460	1
4	15,950	2
5	33,650	3

|   | Miles | Class |    Points      |
| 1 | Flown | Flown | (by Lookup)   |
|   +---------+-------+---------------+
| 2 |     200 |   0   |                |
| 3 | 25,460 |   1   |                |
| 4 | 15,950 |   2   |                |
| 5 | 33,650 |   3   |                |
+---+---------+-------+---------------+

To test the Look-up table, start by entering Table 1 exactly as shown beside your test data in the cells shown. Once the table has been set up, using your mouse, select all cells in the range E2 to G5. Now, locate the Name Box. You'll find it to the left of the Formula Bar (it should have "E2" in it). Using your mouse (cells E2 to G5 are still selected aren't they?), click inside the Name Box and type the word "LookupTable" (without the quotes). You could choose any name here; it really doesn't matter, but for the sake of this exercise, just stick with "LookupTable". You have now created a Named Range.

To verify that you have named the range correctly, choose Insert from Excel's menu, then Name and Define. You should click "LookupTable" in the ListBox on the Define Name Dialog Box, and then look at Refers to box at the bottom. With any luck, you should see "$E$2:$G$5" as part of the reference. Assuming all is well, click the Close Button.

Both of these formulae are functionally identical. You choose which one you prefer.

If entering the first formula, be sure to include the dollar signs because this is an absolute reference. To get around the difficult syntax, you could use the Named Range in the second formula.

Copy this formula down column C (Cells C3:C5). Use a calculator (or the calculator program supplied with Windows, or Excel, or even the spreadsheet you created from last week's article) to check that you have the correct results.

These Formulae should have tests to ensure that the flown class is 0, 1, 2 or 3 and I will leave it to you to add these later. The formulae are, arguably a little easier to understand, but novice Excel users may have difficulty understanding the VLOOKUP() Function.

1. The value contained in Cell B2 is converted to an Integer, using the INT() Function. This ensures that we are checking for whole numbers only.
2. LookupTable refers to cells $E$2 to $G$5; a three column block of cells, which represents the look-up table.
3. VLOOKUP() looks down the first column of the look-up table (column E in our case) for a matching value (INT(B2))
4. VLOOKUP() then takes the value contained in the third column (notice the ",3)" at the end of the VLOOKUP() Function call) and that is the value returned by this call to VLOOKUP().
5. The returned value from VLOOKUP() is then multiplied by the value contained in cell A2, giving us the result we require.

In this article we show the second of the three solutions we have chosen to demonstrate the power and flexibility of Microsoft Excel for solving day-to-day problems. Next week, we take the same problem and solve it by extending Excel in a way that's very neat and will solve more than just this simple problem.

THREE WAYS TO DO THE SAME THING IN EXCEL - PART 3
By: Wayne A. Newton, Bytewise Systems wayne@.com

Welcome to the third in a series of articles where we will show how Excel can be used to solve simple day-to-day problems.

In the last article, we described a simple problem and solved it using Excel's VLOOKUP() Function.

Writing a VBA script
In this article, we take the same problem and solve it by extending Excel. We will write a Function in Excel's powerful programming language, VBA. This may sound scary, but its often the best and easiest way to make a function to suit your particular needs. A VBA function is also easier to understand than a long and deeply nested cell formula.

As we did last week, let's start by re-defining the problem. We have been asked to calculate the number of Frequent-Flyer points we collect when we fly ABC Airlines. Frequent-Flyer points are calculated according to the following table:

	E	F	G
1	Class	Discription	Points (Per Mile)
2	0	Discounted Economy	0.70
3	1	Economy Class	1.00
4	2	Business Class	1.25
5	3	First Class	1.50

|   | E +        F          +    G      |
+---+-----+------------------+----------+
|   |     |                   | Points |
| 1 |Class|   Description    |(per mile)|
|   +-----+------------------+----------+
| 2 | 0 |Discounted Economy|      0.70|
| 3 | 1 |Economy Class     |       1.00|
| 4 | 2 |Business Class    |       1.25|
| 5 | 3 |First Class       |       1.50|
+---+-----+------------------+----------+

According to the table, we will collect 1,000 points if we
travel 1,000 miles in Economy Class and 3,000 points if we
travel 2,000 miles in First Class, etc.

A quick look at the problem shows us that there are two inputs: miles flown and class flown and depending on the class flown, we use a pre-determined number to multiply by the number of miles flown and generate the result.

To begin, open a new Workbook (or you can use the Workbook from last week and insert a new Worksheet) in Excel and enter the data as shown below in Table 2. We will use this as test data to compare all three methods:

|   |    A    |   B   |       C        |
+---+---------+-------+---------------+
|   | Miles | Class |    Points      |
| 1 | Flown | Flown | (by Function) |
|   +---------+-------+---------------+
| 2 |     200 |   0   |                |
| 3 | 25,460 |   1   |                |
| 4 | 15,950 |   2   |                |
| 5 | 33,650 |   3   |                |
+---+---------+-------+---------------+

Visual Basic (Applications Edition) or VBA is the macro programming language supplied with the Microsoft Office Suite. VBA does much more that automating keystrokes; it is a fully-blown, object-like, complete programming language that allows you full control over your Office application.

From Office '97, VBA programming is performed using the Integrated Development Environment or IDE. To get to the IDE, press Alt-F11 when in an Office Application. Pressing Alt-F11 again returns you to your application.

To test the VBA Function solution to our problem, from within Excel, press Alt-F11 to view Office's IDE.

You should be able to see three windows: The Code window (the largest one), the Project Explorer and the Properties window. If you cannot see all three windows, go to the View menu and select them. The standard layout for the IDE is to have the Code window occupying most of your screen real estate, with the Project Explorer and Properties windows docked at the top-left and bottom-left of the screen respectively.

For this exercise, we will add a new Code Module to the Project. Right-click your mouse inside the Project Explorer window and choose Insert, then choose Code Module from the context-sensitive menu. A new Code Module will appear in the Code window and the Project Explorer. In the Properties window, double-click "(Name)" and type "FlyerCode" (without the quotes) and press Enter. You have now named your new Code Module.

Trhe Script Part 1
In the Code window, carefully type the following code (or copy it )

Public Function FlyerPoints(FlightMiles As Range, _
                              FlightClass As Range) As Double
     Dim dblMultiplier As Double

     Select Case CInt(FlightClass.Value)
        Case 0
           dblMultiplier = 0.7
        Case 1
           dblMultiplier = 1
        Case 2
           dblMultiplier = 1.25
        Case 3
           dblMultiplier = 1.5
        Case Else
           dblMultiplier = 0
     End Select
     FlyerPoints = FlightMiles.Value * dblMultiplier
End Function

This Function is declared "Public" meaning that it can be seen anywhere within the Excel Application whenever this Workbook is loaded. It is called "FlyerPoints" and accepts two "arguments" - "FlightMiles" and "FlightClass". The two arguments are of type "Range", which means that they are references to ranges of cells within the spreadsheet. Finally, the Function will return a "Double" (double-precision, floating-point) number. Within the Function, the "Select" statement determines the multiplier, depending on the value contained within the cell called "FlightClass". The last executable statement in the Function does the multiplication and assigns the result to the Function's name. This is VBA's way of returning a value from a Function.

The next step is to call the new Function from the spreadsheet. To do this, press Alt-F11 to return to Excel.

=FlyerPoints(A2,B2)

Copy this formula down column C (Cells C3:C5). Use a calculator (or the calculator program supplied with Windows, or Excel, or even the spreadsheet you created from last week's article) to check that you have the correct results.

An advantage with the VBA method is that the formula is simple and easy to understand. It's also much easier to trap errors or out of range situations (for example the CASE ELSE trap in the above code).

A disadvantage is that the model loses a little of its "transparency". The code shown above is the minimum requirement to be "functional" (excuse the pun), but if your spreadsheet has to be audited or maintained, you should explain what the VBA code does.

Most auditors do not understand program logic and an explanation may be necessary to get your spreadsheet approved.

There's also the general fear of VBA due to virus worries. This stems from years of poor decisions and frankly 'hiding head in the sand' attitude from Microsoft. Your functions may not work if the user has security set to High (which disables all VBA functions with no warning).

The Script Part 2
In the next example, I have included comments. If any of your functions are implementing part of your business logic, you should add the following comments, so that auditors and others can understand what it does:

* What the function is called;
* What it does;
* What information it needs (arguments);
* What it returns;
* Version number and author initials are a good idea too.

Option Explicit

Private Const gcDiscEconomyClass As Double = 0.7
Private Const gcEconomyClass As Double = 1
Private Const gcBusinessClass As Double = 1.25
Private Const gcFirstClass As Double = 1.5
Private Const gcErrorClass As Double = 0
---------------------------------------------------------

Public Function FlyerPointsA(ByRef FlightMiles As Range, _
                               ByRef FlightClass As Range) As Double
'
' Excel Function:   FlyerPointsA (Version: 1.00.01 - WAN)
'
' Description:      Calculates the number of Frequent Flyer points,
'                     given the number of miles flown and the class
flown.
'
' FlightMiles:      Reference to a single Excel Cell containing
'                     the number of miles flown on this flight.
'
' FlightClass:      Reference to a single Excel Cell containing
'                     the class flown on this flight.
'
' Returns:          A double-precision, floating-point number,
'                     representing the calculated number of Frequent
'                     Flyer Points collected when flying FlightMiles,
'                     miles in FlightClass, class.
'
     Dim dblMultiplier As Double

     Const cDiscEconomy As Long = 0
     Const cEconomy As Long = 1
     Const cBusiness As Long = 2
     Const cFirst As Long = 3

     Select Case CLng(FlightClass.Value)
        Case cDiscEconomy
           dblMultiplier = gcDiscEconomyClass
        Case cEconomy
           dblMultiplier = gcEconomyClass
        Case cBusiness
           dblMultiplier = gcBusinessClass
        Case cFirst
           dblMultiplier = gcFirstClass
        Case Else
           dblMultiplier = gcErrorClass
     End Select
     FlyerPointsA = FlightMiles.Value * dblMultiplier
End Function
-------------------------------------------------------

As you look through this code, you'll notice the use of Constants that improve readability as well as the mandatory comments previously discussed. A little extra work using Constants and comments when writing your function helps everyone to understand your masterpiece, even you, when you re-visit it in six months time <g>

User-written Functions can add enormous power to an already powerful application. When seeking the solution to a problem, add VBA Functions to your toolkit - the possibilities are endless!

In this article we show the last of the three solutions we have chosen to demonstrate the power and flexibility of Microsoft Excel for solving day-to-day problems.

Okay, I think you get the idea. Modify the the =countif value to suit your needs.

Enumerating With Data
Countif is useful when you have a single criterion. For more complex comparisons, you may want to use dcount. This function requires that your data be set up as an Excel database (with field names in the first row), and that a separate criteria range specify the conditions.

FIGURE 1 shows an example of a simple database in A1:C13 and a criteria range in E1:G2. The formula in cell E4, which returns the number of sprockets sold in January, is =dcount(A1:C18,,E1:G2).

The formula will yield a different value whenever you change the values in the criteria range. Also, the criteria range can handle logical "or" operations when you use additional rows.

FIGURE 1: Complex counting requires complex formulas, such as dcount, which tallies records in a database.

Smarter Counting With Arrays

If none of the standard techniques fit the bill, construct an array formula, which lets you work with multiple cells in a range. To create an array formula, press Ctrl-Shift-Enter after typing the formula.

To tally the number of values in a range, enter =sum(if(isnumber(data),1,0)).

To count the number of cells that contain an error, try =sum(if(iserr(data),1,0)).

Pivot Tables
The ultimate in counting tools, a pivot table lets you summarize data in just about any way. Select Data, PivotTable to start the PivotTable Wizard.

Here is a simple chart to help you with counting:

FUNCTION	What it does

Count	Counts the number of cells in a range that contain numeric values.

Counta	Counts the number of nonempty cells in a range.

Countblank	Counts the number of empty cells in a range.

Countif	Counts the number of nonempty cells in a range that meet a specified criterion.

Subtotal	Performs various calculations (including counts) only on the visible cells in a range.

Dcount	Within a list or database, counts the number of cells that contain numeric values that meet specified criteria.

Other techniques

Array formula	Performs multiple calculations (useful when the other techniques won't work).

Pivot table	Summarizes large amounts of data in various ways.

How Many Days in the 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).

	A	B	C
1	Staff	Wage	Department
2	YES	350	Accounts
3	NO	100	Sales
4	YES	200	Sales
5	NO	300	Accounts

	A	B	C
1	Miles Flown	Class Flown	Points (By Fundction)
2	200	0
3	25,460	1
4	15,950	2
5	33,650	3