Moving To a New Site

I have just decided that it's time to move on and have my own domain. All the posts in this blog will be moved to:

Personal posts will be transferred to:

Monday, October 11, 2004

Excel Inputs

Here’s how to add spinners, check boxes, and other useful controls to your worksheets.

Even if you’ve used Excel for years, you may not know about its form controls, which let you enter worksheet values using elements like sliders, spinners, list boxes, and check boxes. We’ll show you how these controls work, give you handy tips for customizing them, and look at some applications.

The Spinner Control

The spinner lets you alter the value in a cell by clicking an up or down arrow on the control. The techniques for customizing spinners also apply to scroll-bar controls, so you’re learning two controls in one, effectively.

Create a simple savings worksheet by entering the data shown in the table below. Note that row 6 is blank.

Cell Value

A1 Savings calculator

A2 Interest rate p.a.

B2 6%

A3 Number of years

B3 4

A4 Monthly deposit

B4 -200

A5 Initial balance

B5 -100

A7 Amount saved

B7 =FV(B2/12, B3*12, B4, B5, 0)

This worksheet calculates the amount saved at the end of four years if you start with $100 and save $200 a month at an interest rate of 6 percent compounded monthly. Monies paid out are expressed as negative values, so your starting deposit and the monthly payments are negative. This simple example offers plenty of options for using spinners.

Begin by creating a spinner to adjust the number of years shown in cell B3. Choose View Toolbars Forms to display the Forms toolbar and locate the Spinner control. Click the control and place it by using your mouse to draw a rectangle in cell C3. Right-click the control, choose Format Control..., and select the Control tab. Set the Current value to 4, the Maximum value to 20, and the Cell link to B3, then click OK.

Deselect the spinner by clicking away from it in the worksheet; test the spinner by clicking its up and down arrows. As you click, the value in the linked cell (B3) should increase or decrease within the specified range.

Overcoming Limitations

Spinners are limited to returning integers between 0 and 30,000, but you can get a range of real numbers (including negative numbers) by performing some simple arithmetic on the value returned by the spinner. To show how this is done, we’ll add a second spinner to adjust the interest rate in quarter-point increments.

Place the spinner in cell C2 and right-click on it. Choose Format Control..., select the Control tab, and set the Current value to 24, the Maximum value to 40, and the Cell link to E2, then click OK. Now format cell B2 to show percentage with two decimal places, and alter the cell’s contents to read: =E2/400. When you click the new spinner, you’ll see the value in cell B2 change in increments of .25 percent, from 0 to 10 percent. The new formula in B2 takes the value the spinner returns in cell E2 (a number from 0 to 40) and divides it by 400 to produce the displayed value.

You can also create a spinner to give you the negative value that represents your monthly deposit. Add a third spinner, this time in cell C4. Set the Current value to 200, the Maximum value to 30000, the Incremental change to 10, and the Cell link to E4, then click OK. In cell B4, type -E4 and test the spinner. The E4 value will change in increments of 10 within the range 0 to 30000, giving B4 values ranging from 0 to -30000. You can create a range of values by using a different formula or by modifying parameters like the maximum value.

Check Boxes

Check boxes are controls that return either true or false, depending on whether they are selected or cleared. They are useful for managing options that have only two possible settings, such as on/off, true/false, or 1/0. In our example, the last argument in the formula in cell B7 is currently set to 0, which indicates that the payment (the amount saved each month) is due at the end of every month. Changing this value to 1 alters the calculation to show the result when payments are made at the beginning of each month. This is a good use for a check box.

Click the Check Box control and add a check box into cell B6. Right-click this control and choose Format Control... Control tab. From the Value options choose Unchecked. Set the Cell link to E6 and click OK. Alter the formula in cell B7 to read: =FV(B2/12, B3*12, B4, B5, E6)

Change the check box’s text by right-clicking it and choosing Edit Text. In place of the current text, type: Payments made at beginning of period, then adjust the size of the control so the text can be clearly seen. Test the box by clicking in it; the value in cell B7 should change according to whether the check box is selected or not.

Combo Boxes

The final control we’ll look at is the combo box, which lets you choose an entry from a list. Combo boxes are handy when you have a fixed number of choices and can be used to return more than one piece of data from a table of data.

To understand more about the combo box control (and its close relative, the list box), type these values into a blank worksheet.

Cell Value

A2 J. Brown


C2 10%

A3 P. Smith


C3 12%

A4 J. Peters


C4 9%

A7 Sales

B7 Salesperson

C7 State

D7 Commission

A8 200000

C8 =INDEX(A2:C4,E2,2)

D8 =INDEX(A2:C4,E2,3)*A8

Ignore the errors that appear in cells C8 and D8. Click the Combo Box control and draw a combo box in cell B8. Right-click the control, choose Format Control... and the Control tab, set the Input Range to A2:A4, set the Cell link to cell E2, and click OK. You can now choose a salesperson from the combo box. When you do so, the person’s state will appear in cell C8 and the commission amount will appear in cell D8.

The combo box control returns the position of the selected item in the Input range list. The first item—J. Brown, in this example—is in position 1. In our sample worksheet, each INDEX function queries the array A2:C4 and returns the value in the row and column specified by the formula. The row number is the value returned in cell E2 by the combo box. The column number is supplied in the INDEX function itself.

Note that the data in column E is necessary but does not have to be visible. You can hide it by right-clicking the column and choosing Hide.

This covers the basics of using form controls on your worksheets. You’ll find other controls on the Forms toolbar, such as the Option Button, the List Box, and the Scroll Bar. Each of these works in a similar way to one of the controls we’ve looked at. Option buttons work like check boxes, but only one at a time can be selected. Scroll bars work like spinners, but also include sliders. List boxes work like combo boxes, but the full list can be visible. Some controls on the toolbar are grayed; these cannot be used on worksheets. See the sidebar for directions to Microsoft Knowledge Base articles that discuss these controls.

Saturday, October 02, 2004

The System Boot Process Explained

The typical computer system boots over and over again with no problems, starting the computer's operating system (OS) and identifying its hardware and software components that all work together to provide the user with the complete computing experience. But what happens between the time that the user powers up the computer and when the GUI icons appear on the desktop?

In order for a computer to successfully boot, its BIOS, operating system and hardware components must all be working properly; failure of any one of these three elements will likely result in a failed boot sequence.

When the computer's power is first turned on, the CPU initializes itself, which is triggered by a series of clock ticks generated by the system clock. Part of the CPU's initialization is to look to the system's ROM BIOS for its first instruction in the startup program. The ROM BIOS stores the first instruction, which is the instruction to run the power-on self test (POST), in a predetermined memory address.

POST begins by checking the BIOS chip and then tests CMOS RAM. If the POST does not detect a battery failure, it then continues to initialize the CPU, checking the inventoried hardware devices (such as the video card), secondary storage devices, such as hard drives and floppy drives, ports and other hardware devices, such as the keyboard and mouse, to ensure they are functioning properly.

Once the POST has determined that all components are functioning properly and the CPU has successfully initialized, the BIOS looks for an OS to load. The BIOS typically looks to the CMOS chip to tell it where to find the OS, and in most PCs, the OS loads from the C drive on the hard drive even though the BIOS has the capability to load the OS from a floppy disk, CD or ZIP drive. The order of drives that the CMOS looks to in order to locate the OS is called the boot sequence, which can be changed by altering the CMOS setup. Looking to the appropriate boot drive, the BIOS will first encounter the boot record, which tells it where to find the beginning of the OS and the subsequent program file that will initialize the OS.

Once the OS initializes, the BIOS copies its files into memory and the OS basically takes over control of the boot process. Now in control, the OS performs another inventory of the system's memory and memory availability (which the BIOS already checked) and loads the device drivers that it needs to control the peripheral devices, such as a printer, scanner, optical drive, mouse and keyboard. This is the final stage in the boot process, after which the user can access the system’s applications to perform tasks.