# Using EXCEL in Problem Solving

Document Sample

```					WELCOME

EF 105
Fall 2006
EF 105
Computer Methods in Engineering
Problem Solving

Week 05: Algebra and Trig Review
Use of EXCEL
Introduction
Application
Identify major components
of the Excel window
Excel is a computerized spreadsheet, which is an
important business tool that helps you report and
analyze information.
Excel stores spreadsheets in documents called
workbooks.
Each workbook is made up of individual worksheets, or
sheets.
Because all sorts of calculations can be made in the
Excel spreadsheet, it is much more flexible than a paper
The Excel window has some basic components, such as
an Active cell, Column headings, a Formula bar, a Name
box, the mouse pointer, Row headings, Sheet tabs, a
Task Pane, Tab scrolling buttons and Toolbars.
A sample Excel worksheet
Excel worksheets and workbooks

When you set up calculations in a worksheet, if an
entry is changed in a cell, the spreadsheet will
automatically update any calculated values that
were based on that entry.
When you open Excel, by default it will open a
blank workbook with three blank worksheets.
When you save a workbook, you have a Save As
option that can save the spreadsheet to earlier
versions of Excel or to Quattro Pro, Lotus 123
formats, dBase formats, and even to a comma or
tab-delimited text file.
Identify Excel components
Descriptions of Excel components
Navigate within worksheets
To navigate within a workbook, you use the arrow
keys, PageUp, PageDown, or the Ctrl key in
combination with the arrow keys to make larger
movements.
The most direct means of navigation is with your
mouse.
Scroll bars are provided and work as they do in all
Windows applications.
To move to other Worksheets, you can:
 Click their tab with the mouse

 Use the Ctrl key with the Page Up and Page Down
keys to move sequentially up or down through the
worksheets
The Active Cell
Developing a Worksheet

Determine the worksheet’s purpose.
Enter the data and formulas.
Test the worksheet and make any
necessary edits / corrections.
Document the worksheet and improve
appearance.
Save and print the complete worksheet.
Entering Data into a Worksheet

To enter data, first make the cell in which you
want to enter the data active by clicking it.
Enter the data (text, formulas, dates, etc.) into
the active cell.
Use the Alt+Enter key combination to enter text
on multiple lines within the same cell.
Use TAB key, arrow keys, or ENTER key to
navigate among the cells.

Columns are designated by
alphabetical values such
as A, B, C ….
Rows are designated by
numerical values such as
1, 2, 3 ….
Individual cells are
designated by ordered
pairs containing the row
and column designation C2
such as A1,C2, B15 ….
Formulas

You can also enter
data using formulas.
The formula can
perform
mathematical
operations using data
from other cells.
Arithmetic Operators
Order of Precedence Rules
Identify cell ranges

A group of worksheet cells is known as a cell range, or
range.
Working with ranges in a worksheet makes working with
the data easier.
   An adjacent range is a single, rectangular block of cells
   Select an adjacent range by clicking on a cell and dragging to
an opposite corner of a rectangle of cells
   A nonadjacent range is comprised of two or more adjacent
ranges that are not contiguous to each other
   To select a nonadjacent range, begin by selecting an
adjacent range, then press and hold down the Ctrl key as you
select other adjacent ranges
Select and move worksheet cells
To select a large area of cells, select the first cell
in the range, press and hold the Shift key, and
then click the last cell in the range.
Once you have selected a range of cells, you
may move the cells within the worksheet by
clicking and dragging the selection from its
current location to its new one.
By pressing and holding the Ctrl key as you
drag, Excel will leave the original selection in its
place and paste a copy of the selection in the
new location.
To move between workbooks, use the Alt key
while dragging the selection.
Range selection techniques
Insert worksheet rows and columns

You can insert one or many additional rows or
columns within a worksheet with just a few
steps using the mouse or menu options.
You can insert individual cells within a row or
column and then choose how to displace the
existing cells.
You can click the Insert menu and then select
row or column, or right click on a row or column
heading or a selection of cells and then choose
Insert from the shortcut menu.
Delete worksheet rows and columns

To delete and clear cells, rows, or columns, you
can use the Edit menu, or right click on a
heading or a selection of cells and choose Delete
from the shortcut menu.
Clearing, as opposed to deleting, does not alter
the structure of the worksheet or shift uncleared
data cells.
you can use the Delete key to clear cells, but it
does not remove them from the worksheet as
you might expect.
Use the Undo and Redo features

Editing is an intrinsic task in any document, and
especially useful are the Undo and Redo actions.
The Undo feature allows you to sequentially
back up to a certain action, such as a delete, a
move, an entry, etc. and allows you to reverse
those actions.
Redo allows you to reapply actions one step at a
time that you have previously undone.
Insert, move, and rename worksheets

Worksheets are much like pages within a book; you
peruse through them like you flip the pages of a
book.
There are several ways to move, copy and work
with worksheets.
Right click on the sheet tab and choose Move or
Copy. Select a new position in the workbook for the
worksheet or click the Create a copy checkbox and
Excel will paste a copy of that worksheet in the
workbook.
The same shortcut menu for the sheet tab also
gives you the option to insert, delete or rename a
worksheet.
Print a workbook

To Print a worksheet, you can use:
   The Print button on the standard toolbar
   The Ctrl-P keystroke to initiate a printout of the
worksheet
Excel uses the same basic methods for
printing as other Windows and Microsoft
Office applications.
The Print dialog box
Use Excel’s functions

You can easily calculate the sum of a large number of
cells by using a function.
A function is a predefined, or built-in, formula for a
commonly used calculation.
Each Excel function has a name and syntax.
 The syntax specifies the order in which you must
enter the different parts of the function and the
location in which you must insert commas,
parentheses, and other punctuation
 Arguments are numbers, text, or cell references used
by the function to calculate a value
 Some arguments are optional
Work with the Insert Function button

Excel supplies more than 350 functions
organized into 10 categories:
   Database, Date and Time, Engineering, Financial,
Information, Logical, Lookup, Math, Text and Data,
and Statistical functions
You can use the Insert Function button on the
Formula bar to select from a list of functions.
A series of dialog boxes will assist you in filling
in the arguments of the function and this
process also enforces the use of proper syntax.
Math and Statistical functions
Define functions, and functions within functions

The SUM function is a very commonly used math
function in Excel.
A basic formula example to add up a small number of
cells is =A1+A2+A3+A4, but that method would be
cumbersome if there were 100 cells to add up.
Use Excel's SUM function to total the values in a range
of cells like this: SUM(A1:A100).
You can also use functions within functions. Consider the
expression =ROUND(AVERAGE(A1:A100),1).
   This expression would first compute the average of all the
values from cell A1 through A100 and then round that result
to 1 digit to the right of the decimal point
Copy and paste formulas and functions

Copying and pasting a cell or range of cells is a
simple, but highly effective means for quickly
filling out a large worksheet.
To copy and paste a cell or range:
   Select the cell or range to be copied and then click
the Copy button on the standard toolbar
   Select the cell or range into which you want to
copy the selection and then click the Paste button
on the standard toolbar
   Once you are finished pasting, press the Esc key to
deselect the selection
Copy and paste effects on cell references

Copied formulas or functions that have cell
references are adjusted for the target cell or
range of cells.
For example, if cell G5 contains the formula
=F5*B5/B7, and you copy and paste this
formula to cell G6, the formula in cell G6 will be
=F6*B6/B8.
This may or may not be correct for your
worksheet, depending upon what you are trying
to do.
You can control this automatic adjusting of cell
references through the use of relative and
absolute references.
Problems using copy and paste with formulas

When Excel does not have enough room to display an entire
value in a cell, it uses a string of these # symbols to
represent that value.
For example, the formula in cell J5 is =F5-(H5+I5) and this
was pasted into cell J6 by updating the cell references there
to =F6-(H6+I6).
Cell G5 has the formula =F5*B5/B7 and cell G6 contains
=F6*B6/B8. This is where things went wrong. Sometimes this
automatic update is very useful and other times it does not
give you the desired result for your worksheet.
In this case, cells B5 and B7 should be referenced in the
formula in column G in all 240 payment period rows, but in
column J, you want the cell references to be automatically
updated. You can control this result using relative and
absolute references.
Use relative references

A relative reference is a cell reference that shifts
when you copy it to a new location on a
worksheet.
A relative reference changes in relation to the
change of location.
If you copy a formula to a cell three rows down
and five columns to the right, a relative
reference to cell B5 in the source cell would
become G8 in the destination cell.
Use absolute references

An absolute reference is a cell reference
that does not change when you copy the
formula to a new location.
To create an absolute reference, you
preface the column and row designations
with a dollar sign (\$).
For example, the absolute reference for
B5 would be \$B\$5.
This cell reference would stay the same no
matter where you copied the formula.
Use mixed references

A mixed reference combines both relative and absolute
cell references.
You can effectively lock either the row or the column in
a mixed reference.
   For example, in the case of \$B5, the row reference would
shift, but the column reference would not
   In the case of B\$5, the column reference would shift, but the
row reference would not
You can switch between absolute, relative and mixed
references in the formula easily in the edit mode or on
the formula bar by selecting the cell reference in your
formula and then pressing the F4 key repeatedly to
toggle through the reference options.
Automatic
Update

Numerical data is
Therefore, changing
the value of one cell
propagates the
changes throughout
Notice the effect of
changing one cell on
Relative vs. Absolute Addressing

relative

D3 * \$B\$10
D4 * \$B\$10
D5 * \$B\$10
D6 * \$B\$10

absolute
Practice

1. In an Excel spreadsheet cell, what formula
would you enter to calculate the following
equation?
100 x 20 / (15 + 2)
2. Name an Excel built-in function.
3. How would you designate cell B10 as an
formula?
Next,
Do the Tutorial

AND
the practice problems in an excel
file. Save the file as netidDATE,
where netid is your UT netid and
DATE is the month and day (e.g.
djackson0920)

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 4 posted: 2/17/2013 language: Unknown pages: 39