Spreadsheets

The development and adoption of computers has provided a very useful problemsolving method. This is the spreadsheet. A spreadsheet is a very powerful data

A

B

C

D

E

F

G

H

I

J

1

X

2

3

4

5

6

XX

7

8

10

FIGURE 1.2. Columns and rows af a spreadsheet.

FIGURE 1.2. Columns and rows af a spreadsheet.

management tool that provides a means to enter, manipulate, and plot data and information. There is some variation in the features of different spreadsheets, but they all include several common features. Some of the common features and uses of spreadsheets will be presented in the following sections.

To be able to use data in a spreadsheet the computer must know where it is. This is accomplished by setting up a grid or array using columns and rows. It is common for the columns to be identified by letters and the rows to be identified by numbers. The junction of each row and column is called a cell. The labels used for the column and rows gives each cell a unique address or location, see Figure 1.2.

In the example spreadsheet shown in Figure 1.2, the cell that has the "XX" would be cell E6 located in column E and row 6. The number of columns and rows that are included varies with the producer of the spreadsheet. A popular one has columns that go through the alphabet several times and stops at I5. The rows are numbered to over 10,000. A grid with 256 columns and 10,000 rows would have 2,560,000 individual cells for information. The data are entered into the spreadsheet by typing the desired data into the appropriate cell.

Spreadsheets have many other features for recording and manipulating data. One feature is the ability to link information in different sheets. For example, one type of spreadsheet uses the term "workbook" to describe a spreadsheet file. Each

Sheet #1

A

B

c

D

E

Sheet #2

1

2

B

C

D

E

XXX

4

S

6

XXX

5

6

FIGURE 1.3. Linking sheets in a workbook.

FIGURE 1.3. Linking sheets in a workbook.

FIGURE 1.4. Adding data in a spreadsheet.

A

B

C

D

E

1

2

3

4

3

4

5

6

workbook can have several pages and the data in one page can be linked to another page. If cell B3 in sheet #1 contained the equation "= Sheet2!C4", that cell would display the contents of cell C4 in sheet #2, Figure 1.3.

Most spreadsheets also have the capability of importing the data from other sources. Experience with using spreadsheets teaches that the user should give some thought to how much data will be used and the best way to organize it before starting data entry. This will make the spreadsheet easier to use and the user will make fewer mistakes.

Although spreadsheets are primary for process data and calculate results, text can also be included in the cells and it can be used to process the data.

1.3.6.2. Data Processing and Calculation

Data in a spreadsheet can be processed with almost any mathematical operation or function. The math functions are used by entering the equation with the functions in the cell where the user wants the answer.

For example, if the desired operation is to add the two numbers in the example spreadsheet in Figure 1.4 and have the answer appear in cell D2, the equation to complete this operation would be inserted in cell D2, Figure 1.5. Note that the equation is started with the "=" sign. In most spreadsheets, starting with the "=" sign tells the computer that a mathematical operation or a text process will be performed on data from other cell(s) and the results displayed in the cell with the equal sign.

This is a simple example, but all mathematical operations are entered in the same way. To expedite the process, spreadsheets also include an option called function. Functions are preprogrammed mathematical operations, such as sum, square roots, and trigonometry functions. These functions are usually accessed through a separate menu option. Functions may be categorized as financial, date and time, math and trig, statistical, lookup and reference, database, text, logical, or information. Not all of these functions will be explained in this text. Two examples will be included to show how functions work. The addition example in Figure 1.5 can be completed using the sum function from the math and trig category.

FIGURE 1.5. Example of amathemati-cal operation in a spreadsheet.

FIGURE 1.5. Example of amathemati-cal operation in a spreadsheet.

FIGURE 1.6. Addition using the sum function.

In this example 7, the sum of 3 and 4 would appear in cell D2 (see Figure 1.5). In this example, there is no clear advantage for using the sum function instead of inserting the equation for adding two numbers. The advantage of the sum function is much more apparent when more numbers are added, Figure 1.7.

Using the sum function is much simpler than writing the equation to add several numbers. To write the equation for row 2 in Figure 14.7 using the "+" operator, each cell reference must be included with a "+" in between. The equation "= A2 + B2 + C2 + D2 + E2 + F2" would be entered into cell H2. This operation is easier using a function. The "sum" function is used by entering an = in the cell where the answer is wanted and then sum. After that enter the left parenthesis "("the beginning and ending cell to be added separated by a ":" and finish by entering the right parenthesis")", cell H2, Figure 1.7.

It cannot be demonstrated in this text, but the use of functions is enhanced because the cells can be entered in by highlighting the desired ones. The procedure is to enter "= SUM (then highlight the cells and end the equation with)" to complete the function. Also note that in Figure 1.7, cells can be summed both horizontally and vertically. All of the functions are used in a similar manner.

Another feature of spreadsheets is logic functions. Logic functions are expressions such as "IF", "AND", "FALSE", "NOT", "OR" and "TRUE". Logic functions are very useful because they can be used to compare or relate the values of different cells. The "if" function includes a logic statement so it can be used to compare cells and return a number or word. For example, a flour miller must constantly monitor the percent of flour milled from the wheat. A drop in yield rate would indicate a problem. The "IF" function can be used to trigger an error or warning message when the yield drops below the desired rate, see Figure 1.8.

A

B

C

D

E

F

G

H

1

J

1

2

2

4

8

21

45

234

=sum(A2:F2)

3

5

4

8

5

10

6

234

7

782

8

9

=sum(B2:B7)

10

1

FIGURE 1.7. Using sum function with multiple numbers.

FIGURE 1.7. Using sum function with multiple numbers.

A

B

C

D

E

F

G

H

1

Date

2

10/1 0/04

10/11/04

10/12/04

10/13/04

10/1 4/04

10/15/04

10/16/04

3

Pounds wheat

10562

11584

11698

10962

11532

11 846

10624

4

Pounds flour

8215

10634

10692

10854

10237

10687

9634

5

% Yield

77.78

91.80

91.40

99.01

88.77

90.22

90.68

6

Error

Error

7

FIGURE 1.8. Spreadsheet using the "IF" function.

FIGURE 1.8. Spreadsheet using the "IF" function.

In this example, an "IF" function was used to trigger the spreadsheet to insert the word "error" whenever the percent yield of flour was less than 90%. The function that was inserted into cell B6 written as: = IF(B5<90,"Error"," "). This function was then copied (filled) into the adjoining cells, C2 through H2. Spreadsheets are very powerful tools and allow a lot of flexibility in how they are set up. Through study and practice, the user can make them do very complex calculations and logic statements.

1.3.6.2.1. Fixed or Relational Reference

In the example in Figure 1.8, the reference cell "B5" was not fixed because the desired outcome was to use the same function to sum the columns B through H. This is an example of using a relational reference. Reference cells can also be fixed so they do not change when the function is copied. An example is if the miller in the flour example wanted the spreadsheet set up so that the yield percent could be changed without requiring the retyping of all of the equations. This could be accomplished by using a fixed reference cell for the yield percent, Figure 1.9.

In this example, the user can change the percent yield that will trigger the "Error" message by changing the value that is in cell B1. To accomplish this the function in cell B7 was changed and then copied (filled) to the adjoining cells. The function in cell B7 reads: = IF(B5 < $B$1,"Error"," "). In the spreadsheet used to develop these examples placing the "$" in front of the column and row label fixes these labels so they do not change when the function is copied into adjoining cells.

These examples are just a hint at the type of data manipulation that can be accomplished using spreadsheets. They are only limited by the amount of time and ingenuity of the user to figure out how to make them complete the desired tasks.

A J

B

C

D

E

F

G

H

1

Desired Yield

85

%

2

Date

3

10/10/04

10/11/04

10/12/04

10/13/04

1 0/1 4/04

1 0/15/04

10/16/04

4

Pounds Wheat

10562

11584

11698

10962

11532

11846

10624

5

Pounds Flour

8215

10634

10692

10854

10237

10687

9634

6

% Yeld

77.78

91.80

91.40

99.01

88.77

90,22

90.68

7

Error

8

FIGURE 1.9. Spreadsheet using fixed reference cell.

FIGURE 1.9. Spreadsheet using fixed reference cell.

1.3.6.3. Graphing

Another powerful and useful feature of spreadsheets is the ability to produce graphs of the data in the spreadsheet. Some spreadsheets use the term chart instead of graph. Many people are visual learners and seeing a chart of the data will convey information faster and easier than studying the same data in a table. The charting function of most spreadsheets is not as powerful as a dedicated graphing or presentation program, but they will usually provide enough options to satisfy the needs of the average user.

Producing a graph with a spreadsheet has the same requirements as drawing one by hand. The computer must know which set of data should be plotted along the "X" axis and which along the "Y" and the "Z" if three-dimensional charts are used. The chart function should provide an opportunity to type in labels for the axis, the chart title and data legends and values if desired. To demonstrate the charting function a common problem of surveying will be used. A common survey is called a profile. A profile survey collects the data necessary for defining the topography of the earth's surface along a route. This is usually done for a utility, sidewalk, road, or retaining wall. In this example, an underground drainpipe will be used.

Table 1.3 contains the data for a profile survey. More information will be discussed about profile surveys in a later chapter, but to help understand the use of a chart, the numbers in the STA column are the distances from the starting point for each station and the numbers in the ELEV column are the elevations for each station along the profile. This information is combained with the calculations for the drain elevations at each station, Table 1.4, before the profile and the drain can be shown in a chart.

Assume the outlet of the desired drain is 2 ft below the elevation of station "0.0" and the drain will have a 1% slope. Table 1.4 shows the starting elevation, the surface elevation, and the elevation of the drain at each station. The drain elevation

Table 1.3.

Data for profile survey.

STA

BS

HI

FS

IFS

ELEV

BM

3.56

103.56

100.00

0.0

103.56

4.89

98.67

27.3

103.56

4.67

98.89

35.6

103.56

5.10

98.46

41.2

103.56

5.89

97.67

56.9

103.56

4.68

98.88

63.4

103.56

3.61

99.95

75.9

103.56

4.01

99.95

80.7

103.56

4.65

98.91

93.5

4.04

103.92

3.68

99.88

BM

3.91

100.01

Sum

7.60

7.59

Difference

0.01

0.01

0.01

= 0.01

AE=

0.02

.01<.02

Table 1.4. Data and profile and drain.

A

B

C

1

Starting

2

Elevation

96.67 ft

3

% Slope

1%

4

5

Station

Profile

Drain elevation

6

0.0

98.67

96.67

7

27.3

98.89

96.94

8

35.6

98.46

97.03

9

41.2

97.67

97.08

10

56.9

98.88

97.24

11

63.4

99.95

97.30

12

75.9

99.55

97.43

13

80.7

98.91

97.48

14

93.5

99.88

97.61

is determined by inserting an equation in cell C7 and copying it down through C14. The equation is: = $C$6 + (C7 x $B$3/100).

A person with experience in design and construction would be able to study the table and answer important questions about this drain. Such as, "What is the maximum depth?", or "What is the minimum depth?", and numerous others. The importance of these questions and their answers is easier to see if the data are plotted in a chart. In a chart it is easy to see the difference in elevation between the surface and the drain, the depth of the drain, etc. The chart or plot of the profile and the drain are shown in Figure 1.10.

Figure 1.10 shows that the maximum depth occurs at 62 ft from the start and the difference between the surface elevation and the elevation of the drain is about

Profile with drain

100.00

Profile with drain

100.00

0.0 a 10.0 20.0 30.0 40.0 50.0 60.0 7Q.G 60.0 90.Q 100.0

Station

FIGURE 1.10. Chart of profile and drain.

0.0 a 10.0 20.0 30.0 40.0 50.0 60.0 7Q.G 60.0 90.Q 100.0

Station

FIGURE 1.10. Chart of profile and drain.

TABLE 1.5. Drain data at 0.5% slope.

A

B

C

1

Starting

2

Elevation

96.67 ft

3

% Slope

0.5%

4

5

Station

Profile

Drain elevation

6

0.0

98.67

96.67

7

27.3

98.89

96.81

8

35.6

98.46

96.85

9

41.2

97.67

96.88

10

56.9

98.88

96.95

11

63.4

99.95

96.99

12

75.9

99.55

97.05

13

80.7

98.91

97.07

14

93.5

99.88

97.14

100.0 - 97.4 or 2.6 ft. The minimum depth occurs at station 40 and it is 97.6 - 97.1 or 0.5 ft. This could be a problem because a drain only 6 inch depth is easily damaged and in cold climates could have problems with freezing.

The power and usefulness of spreadsheets can be shown using this example. In Table 1.4, the slope of the drain was in an individual cell and the equations used it with a fixed reference. This was done so it would be easy to do "What if?" scenarios. Such as, "What if the slope of the drain was changed to 0.5%?" The results are shown in Table 1.5 and in Figure 1.11.

Profile with drain

^S. 99.55 /

—i,"" J9Q.85

^98.91

- -0- -Drain Elev.

^7.67

Maximum depth

Minimum depth

1

» — -

' 96,67

96.S8 ybM:>

0.0 10.0 20.9 30.0 40.0 50.0 60.0 70.0 80.0 30.0 100.0

Station

0.0 10.0 20.9 30.0 40.0 50.0 60.0 70.0 80.0 30.0 100.0

Station

FIGURE 1.11. Drain with 0.5% slope.

Note that in this chart the values for each point were displayed. This is a common feature of spreadsheet charts. This shows that the maximum depth of the drain changes to 99.95 - 96.99 or 2.96 ft. The minimum depth changes to 97.67 -96.85 or 0.82 ft. This "What if?" was accomplished by changing one value in the spreadsheet.

Spreadsheets are capable of much more complicated calculations and charts. In addition, they usually have the capabilities of recording "macro's" to automate actions that are frequently used and require multiple keystrokes. Another feature of some spreadsheets is visual basic programming language. This feature provides the tools for the user to write programs to manipulate or use data in some special way. This discussion of spreadsheets does not describe all of the capabilities and features of spreadsheets, but it attempts to explain their basic operation and show some of their useful capabilities. It is up to the reader to explore their capabilities in greater depth.

0 0

Post a comment