intro to excel - session 7.11 tutorial 7 - session 7.1 developing an excel application

Intro to Excel - Session 7.1 1

Tutorial 7 - Session 7.1

Developing an Excel Application


Session 7.1 Objectives

• Discuss worksheet Sections• Use the Excel data validation feature• Assign names to cells• Use cell names in formulas• Use the IF function when building formulas


Figure 7-1, page 7.03


Figure 7-2, page 7.03


Dividing a Worksheet in Sections

• Sections:– Input or Initial conditions and assumptions– Calculations and output

• Benefits to the users:– Knows where to enter data– Knows what factors affect the results– Doesn’t have to change formulas to reflect

new assumptions


Validating Data Entry

• Excel data validation features allows you to specify:– The type of data allowed in a cell– A range of acceptable values– An input message to display– An error alert message to display

(continued)


Validating Data Entry (continued)

• Select the cell• Select

Data Validation…

• Specify validationcriteria:– data type– acceptable values

(continued)



• Specify an inputmessage:– Shown when the

cell is selected– Enter a window title– Enter an input

message

(continued)



• Specify an erroralert message:– Shown when invalid

data is entered– Select error style– Enter a window title– Enter an input

message


Error Message Alert Styles


Using Range Name

• A descriptive name you assign to a cell or range of cells

• Advantages:– Easier formula entry– Improved documentation– Easier navigation with Go To command– Specification of a print range


Defining a Cell or Range Name

• Select a cell or range• Select Insert Name Define


Defining a Cell or Range Name

• Select a cell or range

• Double click in the Name box


Using Range Names in Formulas

• C4 can contain:= C2 - C3

or= Sales - Expenses


Using the IF Function

• Evaluate a specified condition, performing actions based on TRUE or FALSE

• Format:

IF(logical_test, value_if_true, value_if_false)

for example:

=IF(hours > 40, rate * 40 + rate * 1.5 * (hours - 40), rate * hours)


Comparison Operators

• Less than <• Greater than >• Less than or equal to <=

• Greater than or equal to >=• Equal to =• Not equal to <>

intro to excel - session 7.11 tutorial 7 - session 7.1 developing an excel application

Documents