EXCEL

1
EXCEL
2
Spreadsheet

• A spreadsheet is the computer equivalent of a
  paper sheet.
• It consists of a grid made from columns and rows.
  
• It is an environment that can make number
  manipulation easy and somewhat painless.

                                                               
3
Spreadsheet

• The nice thing about using a spreadsheet is that
  you can experiment with numbers without having to
  RE-DO all the calculations.
• Lets change the interest rate and then the number
  of months.
• Let the COMPUTER do the calculations!
• Once we have the formulas set up, we can change
  the variables that are called from the formula
  and watch the changes.

4
Spreadsheet

• Spreadsheets are made up of
• columns
• rows
• and their intersections are called cells

• In each cell there may be the following types of
  data
• text (labels)
• number data (constants)
• formulas (mathematical equations that do all the
  work)

5
What is a COLUMN ?

• In a spreadsheet the COLUMN is defined as the
  vertical space that is going up and down the
  window.
• Letters are used to designate each COLUMN'S
  location.

6
What is a ROW ?

• In a spreadsheet the ROW is defined as the
  horizontal space that is going across the window.
  
• Numbers are used to designate each ROW'S location.

7
What is a CELL ?

• In a spreadsheet the CELL is defined as the space
  where a specified row and column intersect.
• Each CELL is assigned a name according to its
  COLUMN letter and ROW number.

B6
8
Types of Data
Data types Examples Descriptions
LABEL Name or Wage or Days anything that is just text
CONSTANT 5 or 3.75 or -7.4 any number
FORMULA 53 or 853 math equation
9
Labels in Excel

• labels
• computer ledger
• car loan
• interest
• of payments

• Labels are text entries.
• We typically use labels to identify what we are
  talking about.

10
Constants in Excel

• Constants are entries that have a specific fixed
  value.

• the constants are
• 12,000
• 9.6
• 60

11
Formulas in Excel

• Formulas are entries that have an equation that
  calculates the value to display.

PMT(C4/12,C5,-C3)
12
Formulas in Excel

• Formulas are mathematical equations.
• There is a list of the functions available within
  Excel under the menuINSERT Function.
• Formulas OR Functions MUST BEGIN with an equal
  sign ().
• We use formulas to CALCULATE a value to be
  displayed.

13
Basic Formulas in Excel

• Let's look at this equation in B4
• B1 B2
• 23 5.36
• DO YOU KNOW which is BEST and WHY?

14
Basic Math Functions
A B
1 5 3
2 7 4
3 8 6
Operation Symbol ConstantData ReferencedData Answer
Multiplication 5 6 A1 B3 30
Division / 8 / 4 A3 / B2 2
Addition 4 7 B2 A2 11
Subtraction - 8 - 3 A3 - B1 5
15
Methods of Selecting Cells
To Select Type In Click On
A1 A1 click on A1
A1, A2, A3 A1A3 click on A1 with button down and drag to A3
A1, B1 A1B1 click on A1 with button down and drag to B1
A1, B3 A1, B3 click on A1, type in comma, click on B3
A1, A2, B1, B2 A1B2 click on A1 with button down and drag to B2
A B
1 5 3
2 7 4
3 8 6
16
Sum Function

• Probably the most popular function in any
  spreadsheet is the SUM function.
• The Sum function takes all of the values in each
  of the specified cells and totals their values.
• The syntax is
• SUM(first value, second value, etc)

17
Sum Function
A
1 25
2 50
3 75
4 test
5
Example Cells to ADD Answer
sum(A1A3) A1, A2, A3 150
sum(A1A3, 100) A1, A2, A3 and 100 250
sum(A1A4) A1, A4 VALUE!
sum(A1A2, A5) A1, A2, A5 75
18
Average Function

• The syntax is as follows.
• Average (first value, second value, etc.)

A
1 25
2 50
3 75
4 100
Example Cells to average Answer
average (A1A4) A1, A2, A3, A4 62.5
19
Max Function

• This will return the largest (max) value in the
  selected range of cells.

A
1 10
2 20
3 30
4 25
5
Example of Max Cells to look at Ans. Max
max (A1A4) A1, A2, A3, A4 30
20
Min Function

• This will return the smallest (Min) value in the
  selected range of cells.

21
Count Function

• This will return the number of entries (actually
  counts each cell that contains number data) in
  the selected range of cells.
• Blank entries are not counted.
• Text entries are NOT counted.

A
1 10
2 20
3 30
4 test
5
Example of Count Cells to look at Answer
Count (A1A4) A1, A2, A3, A4 3
22
CountA Function

• This will return the number of entries (actually
  counts each cell that contains number data OR
  text data) in the selected range of cells.
• Blank entries are not Counted.
• Text entries ARE Counted.

23
IF Function

• The IF function will check the logical condition
  of a statement and return one value if true and a
  different value if false.
• The syntax is
• IF (condition, value-if-true,
  value-if-false)
• value returned may be either a number or text
• if value returned is text, it must be in quotes

24
IF Function
A B
1 Price Over a dollar?
2 .95 No
3 1.37 Yes
4 comparing returning
5 14000 0.08
6 8453 0.05
Example of IF typed into column B Answer
IF (A2gt1,"Yes","No") No
IF (A3gt1, "Yes", "No") Yes

IF (A5gt10000, .08, .05) .08
IF (A6gt10000, .08, .05) .05
25
PMT

• Calculates the payment for a loan based on
  constant payments and a constant interest rate.

26
PMT

• Syntax
• PMT(rate,nper,pv,fv,type)
• Rate   is the interest rate for the loan.
• Nper   is the total number of payments for the
  loan.
• Pv   is the present value, or the total amount
  that we are borrowing.
• Fv   is the future value, or a cash balance you
  want to attain after the last payment is made. If
  fv is omitted, it is assumed to be 0 (zero), that
  is, the future value of a loan is 0.
• Type   is the number 0 (zero) or 1 and indicates
  when payments are due.

27
PMT
                               
c7 PMT(C4/12,C5,-C3)
28
COUNTIF

• Counts the number of cells within a range that
  meet the given criteria.
• Syntax
• COUNTIF(range,criteria)
• Examples
• COUNTIF(A2A5,"apples")
• COUNTIF(B2B5,"gt55")

29
SUMIF

• Adds the cells specified by a given criteria.
• Syntax
• SUMIF(range,criteria,sum_range)
• Examples
• SUMIF(A2A5,"gt160000",B2B5)

30
Array Formulas

• An array formula can perform multiple
  calculations and then return either a single
  result or multiple results.
• Array formulas act on two or more sets of values
  known as array arguments.
• Each array argument must have the same number of
  rows and columns.
• You create array formulas in the same way that
  you create other formulas, except you press
  CTRLSHIFTENTER to enter the formula.

31
Array Formulas
D5 gt SUM(B2D2B3D3)
SUM(B2B3, C2C3, D2D3)
32
Array Formulas
D8 gt SUM((B2B5Male)(C2C520)(D2
D5Ankara))