Exercise 7 Horizontal Lookup

In this example, a nutritionist enters a client's height, frame type and weight, and the worksheet gives the person's optimal weight and a comment on his actual weight. To keep the exercise to a reasonable size, we limit ourselves to male clients. Our final product will resemble Figure 5.9. (a) On Sheet6 of CHAP5.XLS begin by entering the text shown in A1 A13. Then enter the table in D1 G 16. (b) Use Insert Name Define to name the following areas of the table D2 D16 to be named height, El G1 to...

Anatomy of a Chart objects

You know that the *-axis is the horizontal axis while thejy-axis is vertical. You are also familiar with the terms titles, legends and gridlines. However, the terms chart area and plot area may be new. Their meaning is demonstrated in Figure 6.3. Collectively, these items are called objects. The chart area is everything within the border. The plot area is everything other than the titles and legend. In this chart, the plot area has a pattern. If you do not need the pattern you must set pattern...

Figure 156

You may ask why the chart was not included in step (a). Unfortunately, even if it had been included in the selection, the chart would not have appeared on the web page. Putting a chart on the web page requires a little more work. (d) With the chart selected, open the File menu and click Save as Web Page. Select the Selection (or Republish) radio button and put a check mark in the Add interactivity box. Use the name (e) When you open the new file with Internet Explorer the chart and part of the...

Figure 828

Using Functions from Other Workbooks The user-defined functions we have created have been used in the workbook in which they were coded. Every function in a workbook is available from any sheet in it. There are a number of procedures which permit us to use a function from another workbook. 1. The least efficient method is to copy and paste the function to the new workbook. XLSTART is a subfolder of the folder EXCEL in which Excel was installed, generally this will be C Program FilesNMicrosoft...

Exercise 4 Mixed Numeric and Text Values

Some functions can tolerate arguments referring to cells containing a mixture of numeric and textual values. The functions SUM, AVERAGE and COUNT are amongst these. However, there are some anomalies one should note. During this exercise, Excel 2002 users will see a green triangle in the top left corner of some cells and, when such a cell is active, an error smart tag (an exclamation mark in a yellow diamond) is displayed near the cell. We explore this topic in the next exercise. (a) On Sheet 1...

Natural Language Formulas

The so-called natural formula feature is mentioned here in case the reader finds a reference to it elsewhere. Your author respectfully advises you not to use this feature. (b) The formulas in B3 and B4 are A3*2 and A3 2, respectively. These are copied down to row 4. It is most likely that C4 will initially display 0. Open the Format dialog and select the Fractions category to adjust this. One of the most hailed new features of Excel 97 was called natural language formulas. This allows the use...

Exercise 4 Going Modular

In this exercise we implement the Simpson Vh rule as a user-defined function in a module. To check our function more easily we find the value of the same integral as in Exercise 1. In addition, we experiment with making the strip successively smaller and observe how the percentage error changes. (a) Open the Visual Basic Editor with the fAit )+fF11 ) shortcut. On the module sheet for CHAP11 project, code the integrating function and the function to be integrated as in Figure 11.4. Do not type...

Least squares fit

In each of these examples we see that we need to find the slope of a linear graph. Remember that experimental errors will mean that the data does not exactly fit a straight line. Before computers we would plot the data and then, with a ruler, draw a line that went as close as possible to the points. It is interesting to see the spread of values for the slope obtained in this way by a group of people using the same set of data values. There are more precise ways of determining the line of best...

Exercise 2 Object Embedding

To get the 'flavour' of OLE carry out the following (a) In the CHAP15.XLS workbook click once on the chart. Click the Copy button. (b) Move to your word processor and start a new document. Click the Paste button to copy the chart. (c) Double click the chart. If you are new to OLE the result is unexpected. Although you are running a word processor application (Word, WordPerfect, etc.), the part of the screen containing the chart now looks like Excel. That is exactly what it is. The whole of your...

Exercise 13 Symbols and Such

S will jump to the first font beginning with this letter. The formula bar does not display the formatted value. When you select a cell having some or all the characters formatted in Symbol font, you will see Roman (regular) characters in the formula bar generally in Arial font. (c) If you are familiar with Word you will know that one can use Insert Symbol to get symbols and Greek characters into a document. Until the latest version, Excel did not support this feature. However, one could use the...

The IF and the Logical Functions

The functions introduced in this chapter are useful when making decisions. They include the IF function, the logical functions AND, OR and NOT which enable one to make compound tests, and functions such as VLOOKUP, INDEX and MATCH that look up values from tables in the worksheet. We shall also explore the use of SUMIF and COUNTIF. Some array formulas are explored. The IF function is used when you want a formula to return different results depending on the value of a condition. As a simple...

Exercise 10 Editing and Formatting

In this exercise we construct a table to display the pressure of a gas at various temperatures and volumes using the van der Waals equation Note We could, of course, use the equivalent formula (B4*B8) (A9- E4)- D4 (AtT2). We would like to be able to change the values of a and b so that our table may be used with different gases, so we will place these values in their own cells rather than in the formulas. We will also place the value of the gas constant R in a cell to provide documentation -...

Figure 153

The Paste Option in Word 2002 may cause a smart tag to be displayed. This may be used, for example, to change an item copied as a picture to convert to a linked object. If you need to copy the same Excel object many times, the Windows Clipboard can be inconvenient. Recall that an Excel object remains on the Windows Clipboard only while the object is selected - in the case of a range, only while the 'ant track' is present. The Office Clipboard may be used to hold up to 24 objects and they are...

Userdefined Functions

Microsoft Excel includes a powerful programming language called Visual Basic for Applications (VBA) which enables you to write modules which may be subroutines or functions. A subroutine performs a process such as displaying a dialog box in which the user enters data. A function returns a value to a cell (or a range) in the same way as a built-in worksheet function. We shall explore only function coding. If you have experience with any programming language you will be familiar with many of the...

Exercise 2 Another Way to Print

This exercise uses the menu to print a worksheet. To demonstrate how this is more versatile than the Print button, we will print just a part of the worksheet. (a) Select A3 B10 on the fifth sheet in CHAP2.XLS. We know two ways to make this selection - with the mouse or with the combination of the keys o Shift , B, L, R and T. Here is yet another way click on A3, and while holding down o Shift click on BIO. (b) Use the menu command File Print (or the shortcut Ctrl)+P) to bring up the dialog box...

Exercise 5 Linest with Polynomial Data

(a) On Sheet3 of the CHAP7.XLS workbook, enter the text in A1 B3 and the values in C1 G2. (b) In C3, enter the formula LN(C2) and copy it D3 G3. (c) Enter the text shown in the lower half of the figure. (d) With B6 C8 selected, type the formula LINEST(C3 G3,C1 G1,TRUE,TRUE) and press(CtM)+(Sh + to complete the array formula. The ln(A') values are the known Y values and Time values are the known _X_values. We have used TRUE twice so that the intercept will be calculated and -squared will be...

Exercise 6 Logical Functions

At the start of this chapter, we saw some examples of IF formulas using NOT, AND and OR. These terms are the logical, or Boolean, functions. They return the values TRUE or FALSE. (a) Insert a new sheet, Sheet4, in CHAP5.XLS. Enter the values and formulas as shown in Figure 5.7 and note the values returned. Note To enter the value TRUE (or FALSE) you may type Use the last form when you want your Excel worksheet to be compatible with other spreadsheets. (b) The logical functions may be combined...

Exercise 6 Getting Help

In this exercise we briefly look at how to get help in Microsoft Excel. Suppose we wish to know how to change the width of a column. There is no need to type a complete question such as How do I change a column width You need enter only the key words so in the case we could use column width. (a) Click on either the Help item of the menu bar or on the Help tool, or press F1 . (b) Unless your copy of Excel has been set to do otherwise (see step (c) below), one of the Assistants will appear...

Exercise 3 Titration Model

(f) We can now experiment with the parameters. After observing the effect of changing one parameter, re-enter its original value before changing the next. (i) Change vV(0) to 60, 70, (ii) Change P(0) to 0.25, 0.3, (iii) Change B to 0.0055, 0.0006, 0.00065, (iv) Change K to 0.25, 0.3, 0.19 ,0.18, Which parameters may be changed slightly while maintaining a stable state Can you find one parameter for which a small change results in either a population explosion or an extinction A volume of a weak...

Exercise 3 Quadratic Equation Solver

(b) The formula in B4 is IF(B3> 0, 1 B3, ) which returns the reciprocal of the resistance if its value is greater than 0. Otherwise, the formula returns a space. Copy this across to G4. (c) In B5 enter 1 SUM(B4 G4). Check the result using pencil and paper. (OK, you can use your calculator ) (d) Now let us see if it works for four resistors. Enter 25 for the value of the first four resistors and either leave the last two empty or use the value 0. Do you get 6.25 ohms Save your workbook. It was...

Exercise 1 Eulers Method

Euler developed a method for finding the approximate solution to initial value problems. Let the differential equation to be solved have the form of Equation 12.5 and let the initial value of y be> > 0. Let the solution (i.e. the integral of Equation 12.5) have the form of Equation 12.6. Consider the two curves in Figure I2.l. From Equation 12.5, we may calculate any value y ,y'2, > y'n- We already know the value ofy0 - the initial value. Our task is to find values fory y2, ,y...

Exercise 7 Comparing Repeated Measurements

In the previous exercise each sample was measured once by each of two techniques. In this exercise the same sample is measured repeatedly by two techniques. Our task is the same, to determine if the mean of the two sets of measurements is the same. Once again, we have two statistical methods we could use the t and the p methods. For the former we compute a pooled standard deviation using the formula* J When the two data sets are of equal size, this reduces to from this we compute (experimental)...

Solving Equations

In this section we examine methods of finding roots of non-linear equations such as polynomial (3JC3 - lx2 - 22x + 40 0) and transcendental (exp(-x) - sin(x) 0). If the equation is written as J x) then a root of the equation is a value of x such thaty(x) 0. The value of x is sometimes called the zero of the function. Some equations may be solved analytically. The quadratic formula, for example, is used to find the roots of a quadratic equation. With other equations the analytical method may be...

Exercise 7 Rounding Function

In Exercise 4 of Chapter 3 we saw that formatting a cell changes the way a value is displayed but not the stored values. Excel provides a number of functions which either truncate or round a value to a required number of digits or to a multiple of some number. Constant values are used in the examples to facilitate the discussion. Clearly, the function would normally be used with cell addresses or an expression as the first argument. A few of the functions have a second argument. While this may...

Exercise 1 Anatomy of the Window

Excel Anatomy

Begin this exercise by starting Excel. Your screen will look similar to that in Figure l.l. There could be some minor differences because Excel allows the user to customize the menus and toolbars. Furthermore, starting with Excel 2000, the items displayed in toolbars and in the initial drop down menus change with usage. A tool that has been recently used will be displayed on the initial menu. We will look at customization in Exercise 5. It is convenient to divide the window into seven main...