COMP 100 Excel Functions

1
COMP 100 (Excel Functions)

• Computer Applications
• By Gustavo Alatta

2
Functions

• A function in Excel is a procedure which makes
  its value depend systematically on other values
• What does this mean?
• The value a function returns depends on the
  mathematical operation and the values stored in
  referenced cells

3
Function Attributes

• Every function has two significant attributes.
• Name The name can be a mathematical symbol,
  such as , , -, etc
• Or it can be a word, such as SQRT, LEN, and POWER
• Arguments The values that the function operates
  on.
• Arguments can be text, dates, times, or numbers,
  depending on the function

4
Arguments

• An argument for a function can be a constant,
  such as 3, Sam, 1205, or a variable or
  reference such as C12 or B25
• Examples
• X y, x y, x y, SQRT(x, y), POWER(x,y)
• In each case x and y mark the position of the
  arguments
• When supplying arguments for a function, Order
  Matters!
• 2 / 3 ? 3 / 2
• POWER(2,3) ? POWER(3,2)

5
Entering a Formula

• There are two main ways to enter a formula for a
  cell
• Formula Editor Button
• Useful if you are uncertain as to what formula
  you are going to use or what arguments it
  requires
• Enter formula by hand
• functionName(argumentList) where argumentList is
  a list of arguments seperated by commas
• More convenient if you know the formula and
  arguments you are going to use

6
Literal Arguments

• Number literals type them as you normally
  would, with a dash - preceding all negative
  arguments
• Text literals Tom, Jack, Harry, (a
  space), (the null string)
• Date literals Type the date as you normally
  would
• 3/5/02, February 4, 1992, Feb 12, 1964, etc
• Boolean literals true, false (displayed as
  TRUE, FALSE)

7
Popular functions

• SUM
• AVERAGE
• IF
• HYPERLINK
• COUNT
• MAX
• PMT
• STDEV
• TODAY
• LOOKUP
• TRIM
• UPPER
• LOWER

8
Popular Functions cont

• SUM(Number1, number2) Add all the numbers in
  a range of cells.
• AVERAGE(number1, number,) Returns the average
  (the arithmetic mean) of its arguments, which can
  be numbers or names, arrays, or references that
  contain numbers.
• IF (Logical_test, value_if_true,
  value_if_falseReturns one value if a condition
  you specified evaluates to TRUE and another value
  if evaluates to FALSE.

9
Popular Functions (Cont..)

• HYPERLINK(Link Location. Friendly name) .
  Create a shortcut or Jump that open a document
  stored on your hard drive, a network server, or
  on the internet.
• COUNT(value1, value2.) Count the number of
  cell that contains numbers and numbers within the
  list of arguments.
• MAX(number1, number2.) Return the largest
  values in a set of values, ignore logical values
  and text.
  
  
  
  
  
  

10
Popular Functions (Cont..)

• PMT(rate, nper, pv, fv, type) .Are you
  considering a new car loan, taking an equity loan
  out on your home, or perhaps about to buy a new
  home? Find out what the payment amount will be
  using Excel's PMT Function.The payment function
  is used to calculate the monthly payment amount
  on a loan based on level payments and a fixed
  interest rate. The payment function takes five
  arguments explained below Rate is the annual
  interest rate as charged by the lender. It should
  be divided by the number of payments to be made
  in a year. For example suppose a 5 rate for a
  loan of 5,000 and the loan terms are 3 years
  paid monthly. The formula would be
  PMT(.05/12,36,5000). The rate must be divided by
  the number of payments in a year to convert it to
  a monthly interest rate.

11
Popular Functions (Cont..)

• nper is the total number of payments to be
  made on the loan. A 3 year loans with monthly
  payments would result in a nper of 36. PV is
  the present value of the loan (i.e., the
  principle of the loan). It represents the value
  today of a future stream of payments. If you want
  the payment amount to be returned as a positive
  value, you would enter the PV as a negative.
  FV represents the residual balance at the end of
  a payment stream. It is an optional argument that
  is set to zero by default. Type indicates
  when the payment is due. A type of Zero or
  omitted calculates the payment as due at the end
  of the period. A type of 1 calculates the payment
  as due at the beginning of the period.The
  proper syntax is PMT(rate, nper, pv, fv, type)

12
Popular Functions (Cont..)

• STDEV(number1, number2,) . Estimates Standard
  deviation based on a sample.
• TODAY( ). Returns a number that represent
  todays date (in excel format a date is a
  number).
• Lookup(.) See the sample in next page.

13
Popular Functions (Cont..)

• LOOKUP sample

14
Popular Functions (Cont..)

• TRIM(TEXT) remove all spaces from a text string
  ..except for the spaces between words.
• UPPER(TEXT) convert text string in Uppercase.
• LOWER(TEXT) convert text string in Lowercase.

15
Sorting in Excel

• In order to sort Data in Excel we have to select
  the Data that we want to Sort (Highlight with the
  Mouse).

16
Sorting in Excel.(cont)

• Then in the Excel Main menu bar select Data Then
  Choose Sort

17
Sorting in Excel(cont)

• Then when the Sort windows open Select Sort by
  (choose the column that you want to sort) then
  the the order that you are looking for. Finally
  check if you want to sort the header too.

18
Sorting in Excel(cont)

• Finally. See the result

19
QUESTIONS ?????