Exercise 3 Entering a Function Directly

The procedures in Exercise 2 are useful when we are unsure of the function name or the number of arguments it takes. At other times it is simpler to type the formula. (a) In D5 of Sheet 1 ofCHAP4.XLS, type MAX(A1 A3) and press the check mark of the formula bar. Note that had we typed max(a1 a3), Excel would automatically change the function name and cell addresses to upper case when we completed the formula. (b) To see another way of entering cell references, delete the contents of D3. Type...

Figure

You cannot use a worksheet function when VBA provides the equivalent function even when the name is not the same. So none of the worksheet trigonometric functions SIN, COS or TAN may be used but ASIN and ACOS are permitted. The worksheet function SQRT cannot be used since VBA includes the equivalent SQR function. You may, however, use the worksheet function MOD because Mod in VBA is an operator not a function. Use the Help facility in the VB editor to see a list of which worksheet functions are...

Exercise 3 Temperature Profile Matrix Method

In this exercise we model the same system as in Exercise 2 but use a matrix method to compute the temperatures. Again, we use only four internal points to facilitate discussion. A more accurate model is obtained using more points. Equation 13.1 may be written in a more general form using variables a, b, c and d rather than numerical values. It may then be rearranged in the form t C2 + '3) 4 + (a + d) 4 h U + ',) 4 + (a + b) 4 'i Ci + M 4 + (c + d 4 (13.2) To be able to use a matrix method each...

Exercise 11 Solving Systems of Linear Equations

A system of linear equations may be represented in matrix form. Thus the system of two equations x-2y -I Performing the matrix multiplication in Equation M, we get When matrix A and matrix B are equal, the corresponding elements are equal. So it follows that x + 2y 14 and 2x -y 5 these are the equations with which we started, thereby justifying the statement that we may represent a system of linear equations in matrix form. In the following represents the matrix of the coefficients, Xthe matrix...

Syntax 1 for the DO statements

Do While Until condition statements Exit Do statements Loop Exercise 8 The DO LOOP Structures User-de ned Functions 157 From this syntax we see there are two variations. 1. DO WHILE condition.,.LOOP Looping continues while the condition is true. The condition is tested when the first line of the DO statement is executed. 2. DO UNTIL condition LOOP'. Looping continues until the condition is true. The condition is tested when the first line of the DO statement is executed. Note that the condition...

Exercise 5 Roots of a Cubic Equation with Solver

(e) Use the mouse to move to the By Changing Cells box. Either type 'D4' in this cell (it will change to D 4) or use the mouse to click on the cell D4. Equal To C Max C Min < Value of o Equal To C Max C Min < Value of o (f) Click on the Solve button. After a second or two, Solver will report whether or not it has found a solution see Figure 10.11. Click the 'OK' button. With a starting value of-20, your first solution should be - 12. Solver found a solution. All constraints and optimality...

Exercise 2 Population Model

An ecological niche contains two species one is the prey, the other the predator. A theoretical analysis of the problem has yielded equations for the successive population of the two species N t+1) (1.0 -B(N(t) - 100))7V( )) KN(t)P(t) P(t) QN(t)P(t) the population of prey in generation t the net birth-rate factor the kill-rate factor the population of predators in generation t efficiency in use of prey To observe how this model predicts the changing populations and to examine the sensitivity of...

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...

Objective

To find how the output angle lt gt depends upon the input angle 0 . Assumptions For the quadrilateral formed by the four links, the algebraic sum of the vertical component and the algebraic sum of the horizontal component must equate to zero. This gives the two equations a sin 6 6 sin 3 csin lt 0 acos6 Z gt cosp-ccos lt t i 0 Adding the squares of these gives the Freudenstein equation - 2cos lt t gt ,-cos e- lt 0 Rather than attempt the difficult task of solving this to find lt j in terms of0,...

Exercise 1 Anatomy of the Window

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...