Making Logical Decisions Logical Expressions (AND, OR and NOT) IF Function Nested IF Function Example 4.1 Assign a final letter grade to each of the students listed in the worksheet in Example 2.4. Base the letter grades on the following rules: Overall Score Grade

90 and above A 80 to 89.9 . . . B 70 to 79.9 . . . C 60 to 69.9 . . . D Below 60 F

The letter grade conversion can easily be carried out with the following formula, which utilizes several nested IF functions: =IF(E2 >= 90, "A", IF(E2 >= 80, "B", IF(E2 >= 70, "C", IF(E2 >= 60, "D", "F")))) This formula assumes that cell E2 contains the numerical score being converted. Example 4.2 Modify the previous example to include pluses and minuses (i.e., so that students may receive final grades of A, C+, etc.). We will adopt the rule that, within any 10-point interval, a score of 3 or below will be assigned a minus, whereas a score of 7 or above will receive a plus.

Thus, within the interval ranging from 80 to 89.9 . . . , a score of 83 or below will result in the grade B, a score of 87 or above will result in the grade B+, and a score between 83 and 87 will correspond to an ordinary B grade. The quadratic equation normally has two roots, though the roots depend on the values assigned to the coefficients a, b, and c. If , then the roots are given by the well-known quadratic formula

If, the roots are complex, given by where represents the imaginary number -1 . If, there is one repeated root, given by Finally, if , there is only one root, given by Create an Excel spreadsheet that will determine the roots of a quadratic equation in terms of the coefficients a, b, and c. Enter the following sets of coefficients in your worksheet, and

determine the roots corresponding to each set: (a) a = 2, b = 4, c = 1 (b) a = 4, b = 2, c = 3 (c) a = 2, b = 4, c = 2 (d) a = 0, b = 3, c = 2 Create an Excel worksheet that will convert inches to one of the following metric units: millimeters, centimeters, meters, or kilometers, depending on the magnitude of the given value. (Note: 1 inch = 2.54 centimeters.) Use the following rules to determine which metric unit will be shown:

(i) If the given value is less than 1 inch, convert to millimeters. (ii) If the given value is greater than or equal to 1 inch but less than 100 inches, convert to centimeters. (iii) If the given value is greater than or equal to 100 inches but less than 10,000 inches, convert to meters. (iv) If the given value is greater than or equal to 10,000 inches, convert to kilometers. Use the worksheet to carry out the following conversions: (a) 0.04 inch (b) 32 inches (c) 787 inches

(d) 15,500 inches X-Y GRAPHS An x-y graph is created by plotting a series of paired data points. Each paired data point consists of an x-value and a y-value, hence, the name x-y graph. A line or curve may be passed through the data points. Most engineering and scientific data are recorded as paired data points and displayed in the form of x-y graphs. Example 5.1 Creating an X-Y Graph

in Excel The voltage drop across a capacitor varies with time in accordance with the formula where V represents voltage drop, in volts, and t represents time, in seconds. Prepare an x-y graph of the voltage as the time varies from 0 to 10 seconds. Display the data to three-decimal precision using arithmetic coordinates. Label the graph so that it is legible and attractive. SEMI-LOG GRAPHS

A graph in which the y-axis (the ordinate) has a logarithmic scale and the x-axis (the abscissa) has an arithmetic scale is known as a semi-log graph. Semi-log graphs are commonly used in many diverse fields, including engineering, chemistry, physics, biology, and economics. SEMI-LOG GRAPHS Note the nonuniform spacing of the units along the logarithmic scale. Also, note that the location of 1 on the logarithmic scale is equivalent to the location of log 1 on the arithmetic scale (because log 1 = 0, as shown on the arithmetic scale). Similarly, the location of 2 on the logarithmic scale is equivalent to the location of log 2

on the arithmetic scale (because log 2 = 0.30), and so on. Thus, we see that plotting x (or y) on a logarithmic scale is equivalent to plotting log x (or log y) on an arithmetic scale. Note that you simply plot the value of x (or y) directly when plotting data on a logarithmic scale. You need not calculate the log of x (or the log of y) the logarithmic scale automatically does this for you. Example 5.4 Creating a Semi-Log Graph in Excel Convert the arithmetic x-y graph developed in Example 5.1 and shown in Fig. 5.13 into a semi-log graph. The voltage drop across a capacitor varies with time in accordance with

the formula where V represents voltage drop, in volts, and t represents time, in seconds. Prepare an x-y graph of the voltage as the time varies from 0 to 10 seconds. Display the data to three-decimal precision using arithmetic coordinates. Label the graph so that it is legible and attractive. LOG-LOG GRAPHS A log-log graph has logarithmic coordinates along both axes. Thus, it is equivalent to a plot of log y against log x using arithmetic coordinates.

Log-log graphs are useful for plotting scientific and technical data because they allow the data to span several orders of magnitude and because a power equation of the form will appear as a straight line. Example 5.5 Creating a Log-Log Graph in Excel Create a log-log graph of the area and volume of a sphere as a function of the radius within the interval 0 r 10, using the following two formulas.

and LINE GRAPHS Line graphs, unlike x-y graphs, are used to represent single-valued (categorical) data. Usually, the data points represent different values of the same entity, such as the average daily temperature for each of several consecutive days. Do not confuse x-y graphs (called XY Charts or Scatter Charts in Excel) with line graphs (called Line Charts in Excel). If the independent variable varies continuously (e.g., time or distance), you should

always use an x-y graph rather than a line graph to represent the data. Example 5.6 Creating a Line Graph in Excel BAR GRAPHS (EXCEL COLUMN CHARTS) A bar graph, like a line graph, is used to represent single-valued (categorical) data. Unlike a line graph, however, a bar graph utilizes a series of vertical rectangles (bars) to represent the data.

PIE CHARTS A pie chart shows the distribution of individual data items within a data set. Like line graphs and bar graphs, pie charts represent singlevalued data.