Transcript Spreadsheet Formulae - East China Normal University

Spreadsheet Formulae
• Some Common Functions
=IF(condition, true, false)
• Returns one value if a condition you
specify evaluates to TRUE and
another value if it evaluates to FALSE.
• Use IF to conduct conditional tests
on values and formulas
IF function
• A useful function when there is a choice
between two alternatives
• Syntax
– If(Logical_test, Value_if_true, Value_if_false)
– Logical_test: any value or expression that can be
evaluated to TRUE or FALSE
– Value_if_true: Value returned if Logical_test is
TRUE
– Value_if_false:Value returned if Logical_test is
FALSE
• Allowable logical comparisons in IF
function:
– =, >, <, <=, >=, <> (not equal)
The IF FUNCTION (CONT.)
What is the result of these IF statements?
A
B
C
1
6
2
34
3
2
4
7
5
8
6
2
7
10
8
35
9
11
10
92
D
E
F
G
Example
of
Comparison:
Is
B5 > 5 ?
Is
B6 < B7 ?
11
=IF (B6 < B7, B7, B6)
=IF (B2 – B1 >20, “More”,”Less”)
10
More
IF Function Example
• In a regular driver’s income
spreadsheet
• A driver's income is either $1,100 or
$1,100+$600 depending on whether
there is a “No” or ”Yes” in the 'Race
Win' column.
Example: if-function
• Build such a table and calculate the
INCOME of each driver
• What is the formula in C5?
– =IF(B5="Yes", $C$1+$C$2, $C$1)
• What is another possible formula in
C5?
– =IF(B5="No", $C$1, $C$1+$C$2)
• If there are more than 100 drivers in the
list, can you calculate all the incomes for
them?
=VLOOKUP FUNCTION
Syntax : =VLOOKUP(valueX, TableRange, ColNum, NotExact)
Looks DOWN the left column of TableRange for the last
row with a value <= valueX, then returns the value in
ColNum columns across within TableRange.
If NotExact is FALSE VLOOKUP only finds exact matches.
If NotExact is TRUE or omitted and an exact match is not
found then the largest value less than or equal to valueX
is matched, as described above.
Spreadsheet Formulae
• Used to translate from one value to
another
• Searches for a value in the leftmost
column of a table
• returns a value in the same row from a
column you specify in the table
VLOOKUP function
• =VLOOKUP(LookupValue, TableRange,
ResultColumn, NotExact )
– LookupValue: the value to look up
– LookupRange: the range of cells containing the
table in which the value is to be looked up
– ResultColumn: Column number of the range that
contains the result you want
– NotExact: if false, only find exact matches
EXAMPLE 1
Example 1: VLOOKUP with numbers
To find
=VLOOKUP(17, A1:C4, 2)
=VLOOKUP searches down the first column of the
specified block (column A), looking for the largest
number less than or equal to 17. It stops at cell A2, then
moves across the specified number of columns (2). It
stops at cell B2 and returns the value 56.
Examples
Find the values of the following:
• E1=VLOOKUP(17, A1:C4, 2) =?
• E2=VLOOKUP(5, A1:C4, 1) =?
• E3=VLOOKUP(50, A1:C4, 3) =?
• E4=VLOOKUP(1, A1:C4, 2) =?
• E5=VLOOKUP(18, A1:C4, 9) =?
• E6=VLOOKUP("18", A1:C4, 3) =?
VlookupExample2
EXAMPLE 2 (CONT’D)
What would be the answer given by
Excel for
=VLOOKUP("Tuesday", A1:C7,2) =
=VLOOKUP("Thursday", A1:C7,2) =
=VLOOKUP("Saturday", A1:C7,2) =
145 (RIGHT)
56 (WRONG)
56 (WRONG)
EXAMPLE 2 (CONT’D)
Some of the answers are not what you would expect. WHY?
For VLOOKUPs to work properly with text fields, it is very
important to include FALSE in the NotExact field in the
VLOOKUP.
Eg. =VLOOKUP(“Tuesday”, A1:C7,2, FALSE)
which means that only an exact match will do, so
Excel will keep looking down the list until an exact
match is found.
What would be the right answer now?
=VLOOKUP("Tuesday", A1:C7,2,FALSE) =
=VLOOKUP("Thursday", A1:C7,2, FALSE)=
=VLOOKUP("Saturday", A1:C7,2,FALSE) =
145
62
77
EXAMPLE 3
What is the vlookup formula in cell C8?
=VLOOKUP(B8,$A$2:$B$5,2)
VlookupExample4
• The look up table is(the speed range of
different things)
VlookupExample4
• Write down the description list for each
in column A
VlookupExample5
• Taxation table
– Salary Range
Tax
0 - 99
0
100 - 199
25% of salary > 100
200 - 299 $25 + 35% of salary > 200
300 +
$60 + 50% of salary > 300
VlookupExample5
• Taxation.xls
– What should
be written in
C9?
• the cell should
maintain
maximum
flexibility (can
be copied
down or
across)
HLOOKUP
• Use VLOOKUP instead of HLOOKUP
when your comparison values are
located in a column to the left of the
data you want to find.
SPREADSHEET DESIGN
• Have an area (table) set aside to hold key
values
• All cells in the working area then refer to the
respective values in the Key Values Table
• By simply altering the key values different
scenarios can be tested.
• Excel even has a scenario feature where
different sets of key values can be stored and
easily loaded from a scenario toolbar.
Example of possible layouts
Key
Values
Table
Working Area
However addition of new column also
affects the layout in Key Values Table
Another possible layout
Key
Values
Table
Working Area
Can insert/delete
rows/cols at will
both here and in the
key values table
CASE STUDY – ORCHIDS SHADE HOUSE
A flower grower plants a bed of orchids in a shade house at a
total cost of $6000. The bed will not start producing during the
first year. In the second through eighth years it should
produce, respectively, 150, 500, 1900, 2000, 2200, 2000, and
1200 dozen orchid sprays. Fixed annual operating costs are
$1200 per bed, while variable expenses during producing years
are 25% of sales receipts. Water costs per year are provided in
the table in the orchid’s spreadsheet. If 2000 or more orchids
are produced in a year then an extra staff hand needs to be
employed, costing $18000 for that year. Due to the drought,
water costs fluctuate each year: $2000, $3000, $5000, $5000,
$5000, $6000, $6000 & $5000 respectively. Orchids currently
sell for $15 per dozen, which has currently been increasing at
3.5% per year.
CASE STUDY – ORCHIDS SHADE
HOUSE
• Construct a spreadsheet model that
shows a breakdown of anticipated
annual income and expenses over
the eight years.
CASE STUDY – ORCHIDS SHADE HOUSE