IT Business Applications

1
IT Business Applications

• Week 6
• Using Functions

This Presentation is in Self Study Form Press F5
to begin
2
Objectives

• After working through the materials for week 6
  you will be able to
• Get Help from Excel on a range of issues,
  including how to use functions and other commands
• Understand an use a wide range of different
  functions in Excel, including arithmetic, text,
  statistical,utility and financial functions

3
Menu

• Getting Help from Excel
• Introduction to Functions
• Time Functions
• Utility Functions
• Look- Up Tables
• Logical Functions
• Other Functions

This presentation consists of 7 self-study
sections. Click on the section you wish to study.
At the end of some sections there is a short task.
4
Getting Help from Excel

• From this point on in the module you will need to
  consult with Excel help files on a regular basis.
• To get well-focussed help from Excel you need to
  turn off the office assistant.
• Click on Options and de-select the tick bock for
  Use the Office Assistant

5
The Excel Help Files

• Now press F1, and these help files appear.
• You have a full Excel manual
• This can be accessed via
• Contents
• Answer Wizard
• Index

6
Example of a Help File
This is an example of one of the help topics.
This can be found in Creating Formulas, then
Entering Formulas, then How formulas create
values
7
Simple Functions in Excel

• A function can be thought of as a tool for doing
  a specific task.
• For example, instead of typing in a string of
  additions
• A1A2A3A4,
• we can use the SUM function
• SUM(A1A4)

8
Types of Functions
9
Time Functions

• Two interesting functions are
• NOW()
• This yields the current date and time
• WEEKDAY (Date)
• This yields a number corresponding to the date
  input
• 1Sunday,
• 2Monday,
• 3Tuesday etc.

10
Time Functions

• We can use the function TEXT to extract the day
  of the week
• TEXT( NOW(), dddd)

11
Time Functions

• There are many more functions to investigate
• In the HELP menu, Try looking up
• DAY
• NETWORKDAYs
• YEARFRAC

12
Utility Functions 1

• These functions are useful in telling you
  information about the data in the spreadsheet
  cells
• COUNT
• this counts how many cells in the range contain
  numbers
• COUNTA
• this counts how many cells within a particular
  range contain some sort of information
• COUNTBLANK
• this counts how many cells within a particular
  range are blank

13
Counting Cells
Before you go on, work out the answers to the
three functions below

• COUNT(A1 D4) gives
• COUNTA(A1D4) gives
• COUNTBLANK(A1D4) gives

14
Counting Cells
ANSWERS

• COUNT(A1 D4) gives 10
• COUNTA(A1D4) gives 14
• COUNTBLANK(A1D4) gives 2

15
Utility Functions 2

• These functions are useful in telling you
  information about the data in specific
  spreadsheet cells.
• They return TRUE or FALSE
• ISBLANK
• tells you whether a specific cell is empty
• ISNUMBER
• tells you whether a specific cell contains a
  numerical entry
• ISTEXT
• Tells you whether a particular cell contains text

16
Whats in the cell?
Before you go on, work out the answers to the
three functions below

• ISBLANK(D4) gives
• ISNUMBER(B1) gives
• ISTEXT(A4) gives

17
Whats in the cell?
ANSWERS

• ISBLANK(D4) gives FALSE
• ISNUMBER(B1) gives TRUE
• ISTEXT(A4) gives TRUE

18
Look Up Tables

• Using a Look Up is one of the most useful
  features of Excel.
• This allows you to select from of a list of
  options using a particular value as a reference
• This is exactly like looking up a definition
  of a word in a dictionary, or a telephone number
  in a directory.

19
Look Up Tables

• The simplest of the functions to use is LOOKUP.
• This has the syntax
• LOOKUP(cell, range)
• For example,
• LOOKUP(A1,K2L5) looks up the value stored in A1
  in the table located in the block of cells K2
  L5

20
Look Up Tables

• For example
• A company sell 4 different sizes of roofing
  slate
• A, B,C, D, E
• Costs per slate are given in the look-up table
  opposite
• We read off the cost for D, by looking up the
  value in the table

• Size Cost
• A 0.45
• B 0.70
• C 0.95
• D 1.10
• E 1.25

21
Look Up Tables

• The contents of the look-up table is place in
  cells B3 to C7
• In cell B10 we have a type of slate for which we
  need to know the cost
• In cell C10 we put
• LOOKUP(B10, B3C7)

22
The LOOKUP function

• The other use of this function is to look up a
  cost in a table to find an amount
• For example if we are willing to spend 1.00 per
  slate, what is the best quality that we can
  afford?
• LOOKUP(B10,B3C7)

The answer is C because as Excel reads down the
table that is the best quality for the price.
23
The LOOKUP function

• The table must be in alphabetical or numerical
  order
• There are two columns of figures
• We look down column 1, find the and then read off
  the value in column 2

24
VLOOKUP

• VLOOKUP is an extension of the idea, allowing you
  to create blocks of cells which contain different
  lookup values for different circumstances

25
VLOOKUP

• The simplest format for VLOOKUP is
• VLOOKUP(cell, range, column number)
• This looks up the value required in the first
  column of table, and reads the value from the
  column specified

VLOOKUP (B10, B3D7, 3) will give the New Price,
by looking up D in the first column, and reading
across to column 3
26
HLOOKUP

• HLOOKUP has the same kind of structure, except
  that the table is formatted horizontally instead
  of vertically

Format HLOOKUP(cell, table, row) HLOOKUP(B10,
C2G6, 4) will give the 2001 price for the
slate
27
Naming Cells and Ranges

• We have seen previously that it is possible to
  label cells with names.
• It is equally possible to label ranges of cells
  in the same way

Highlight the cells
Click on here and insert a name
28
Using Name Ranges in Formulae

• The highlighted area of the table has been called
  Slates
• In the cell we type
• VLOOKUP(B11, Slates, 2)
• This gives the old price

29
Costing of Fabric

• The Functions Example spreadsheet contains a
  worksheet called dresses. You should now create
  the lookup formulae for this page.

Action Point
30
Costing of Fabric
The table at cells B14F20 should be renamed
dresses, or some other suitable name.
31
Costing of Fabric
ANSWERS
VLOOKUP(C3, Dresses, 3)
VLOOKUP(C3, Dresses, 5)
G3G4
32
Costing of Fabric
ANSWERS
VLOOKUP(C3, Dresses, 2) C5
VLOOKUP(C3, Dresses, 4) C5
33
Logical Functions 1

• IF
• This is a very important function, as it allows
  us to make conditional statements.
• The Function has the structure
• IF(condition, statement 1, statement 2)
• If the condition is satisfied, statement 1 is
  executed, otherwise statement 2 is executed.
• For example, IF(A2gt40, Pass,Fail)

34
Logical Functions 2

• AND and OR
• These functions are important conditional test
  tools, which allow us to combine events
• To test whether A1gt0 and B1gt0, we would use the
  construction
• AND(A1gt0, B1gt0)
• if both were true the AND function would yield a
  TRUE result.
• OR(A1gt0,B1gt0) yields a true result if either
  A1gt0, B1gt0 or both A1gt0 and B1gt0.

35
Putting it together

• Look at the function
• IF(OR(A1lt0,B1lt0),One is negative,A1B1)
• This tests whether either A1 or B1 is negative,
  and if so, puts the a message in the cell to that
  effect. On the other hand, if both are positive,
  the two values are multiplied together and the
  answer put in the cell.

36
Sales Returns
The Functions Examples spreadsheet has a
worksheet called Sales Returns giving the
performance figures of five salespeople during
2000 and 2001 you are asked to create formulae
to test a variety of conditions.
NB Average of the individual sales person
Action Point
NB Average of the whole team
37
Sales ReturnsSolution 1
ANSWERS
AVERAGE(B5B9)
AVERAGE(C5C9)
38
Sales ReturnsSolution 2
ANSWERS
IF(AVERAGE(B5C5)gt70000,Yes,No
39
Sales ReturnsSolution 3
ANSWERS
if(AND(B5gtB11,C5gtC11),Yes,No)
if(OR(B5ltB11,C5ltC11),Yes,No)
Note the use of absolute cell referencing when
referring to B11 and C11, in order that when
copied, these cells remain fixed.
40
More Functions

• Excel contains literally hundreds of functions,
  some of which are quite similar to, and may of
  which are very different from the ones in this
  lecture.
• In particular, as you look through the Excel help
  files, you will notice that we have been using
  only one form of these functions each one has
  many variants.

41
Text Functions

• CONCATENATE
• This is a function used to glue together two or
  more pieces of text as output in a cell
• CONCATENATE(The answer is , C5)
• If the numerical value of cell C5 is 3.142, then
  the text created would say
• The answer is 3.142
• An alternative is to use the ampersand sign
• The answer is C5

42
Text Functions

• MID
• This function reads a piece of text and extracts
  part of it. The format is
• Mid( text, start, number of letters)
• For example, suppose the cell A5 contained the
  word Spreadsheet
• Mid(A5,3,5) would yield the word reads

43
Statistical Functions

• We have already used some of these functions
• MIN gives the minimum value from a range,
• MAX gives the maximum value from a range
• SUM sums a range of values
• STDEV returns the standard deviation
• AVERAGE returns the average of the values
• e.g. MAX(C2C40), SUM(A1D300) etc.

44
Financial Functions

• DB
• This function calculates the depreciation of an
  asset for the fixed-declining depreciation
  period.
• The format is
• DB(initial cost, salvage, life, period)
• For example
• DB( 5000, 1000, 10, 3) calculates the amount that
  needs to be written off in year 3 of a car bought
  for 5000, and expected to have a salvage value
  of 1000 at the end of 10 years

45
Financial Functions

• PMT
• This function calculates the payment for a loan
  based on constant payments at constant interest
  rate
• Format
• PMT(int. rate, no. of payments, pres. value)
• For example
• PMT(8/12, 12, 10000) calculates the monthly
  payments on a loan of 10000 that must be paid
  back in 12 months at a rate of 8 per annum.

46
More Functions

• There are literally hundreds more functions
  within Excel, some of which you will encounter in
  the Activity which follows
• However, if you think of a task you want to do,
  then use the Answer wizard.
• In the example that follows, the question posed
  was I want to sum a range of values when a
  particular condition is met

47
More Functions
This was the result. Note that a range of
possible functions is suggested. In fact the
SUMIF function would be the one to use in this
case.