Excel CH 02 Working with Formulas Functions.ppt

Understanding functions and
prewritten formula that is built into Excel

1. FIRST COURSE
Chapter 2:
Working with Formulas and
Functions

2. •A function is a prewritten formula that is
built into Excel.
•Working with a range of cells is a lot easier
than working with each cell individually.
•Example, an individual can enter all 20 cells as
arguments in the AVERAGE function.
OR
•Type =AVERAGE(C5:C25) is decidedly quicker
(and probably more accurate).

3. Building Basic Formulas
Understanding Formula Basics:
All Excel formulas have the same general structure:
•an equals sign (=) followed by one or more
operands –which can be values, cell references,
ranges, range names, or function names
•separated by one or more operators –the
symbols that combine the operands in some
way, such as the plus sign (+) and the greater-
than sign (>).

4. Entering Formulas
1.Select the cell in which you want to enter the formula.
2.Type an equals sign (=) to tell Excel that you are
entering a formula.
3.Type the formula’s operands and operators.
4.Press Enter to confirm the formula.
Editing formula
•Press F2.
•Double-click the cell.
•Use the formula bar to click anywhere inside the
formula text.

5. Excel divides formulas into four groups:
arithmetic, comparison, text, andreference.
One of the reasons Excel is such a valuable tool is
that you can assign a formula to a cell and Excel
will calculatethe result.
To Enter a Formula Using the Keyboard
1.With cell F4 selected, type =d4*e4 in the cell
2.Press the RIGHT ARROW key twice to complete
the arithmetic operation

6. Arithmetic Operations
The formula, =d4*e4, is the same as the
formulas, =d4 * e4, =D4 * e4, or =D4 * E4.

7. UsingComparisonFormulas
A comparison formula is a statement that
compares two or more numbers, text strings, cell
contents, orfunction results.
If the statement is true, the result of the formula
is given the logical value TRUE (which is
equivalent to any nonzero value).
If the statement is false, the formula returns the
logical value FALSE (which is equivalent to 0).

9. Order of Operations

11. First Approach
=40+100+50+20

12. Second Approach
=b2+c2+d2+e2

13. Third Approach –Using Function
=sum(b2:e2)
OR

14. Determining Multiple Totals at the Same Time

15. Using name cell
The formula =Income–Expenses subtracts the value
in the cell named Expenses from the value in the cell
named Income.
Nested formula
This formula =(B2–B3)*B4 subtracts the value in cell
B3 from the value in cell B2 and then multiplies the
result by the value in cell B4.
The formula =((B2*C2)+(B3*C3)+(B4*C4))*B6 uses
nested parentheses

16. To Enter Formulas Using Point Mode
Point mode allows you to select cells for use
in a formula by using the mouse.

19. Figure 2–1: Consortium summary worksheet

20. Statistical Functions
Using the AVERAGE, MAX, and MIN Functions
Using Range Names
•Defining a Range Name
•Working with the Name Box
•Using the New Name Dialog Box
•Working with Range Names

21. Range names are labels applied to a
single cell or to a range of cells.
Range names also make formulas
intuitive and easy to read.
For example, assigning the name
AprilSales to a range such as E6:E10
immediately clarifies the purpose of a
formula such as=SUM(AprilSales).

22. Defining a Range Name
•The name can be a maximum of 255
characters.
•The name must begin with either a letteror
the underscorecharacter (_).
•Don’t use cell addresses (such as Q1) or
any of the operator symbols (such as +, –,
*, /,<, >, and &)
•Try to keep names as short as possible
while still retaining their meaning.
TotalProfit07

23. Defining a Range Name……
1.Select the range you want to name.
2.Click inside the Name box to display the
insertion point.
3.Type the name you want to use, and then
press Enter.

24. Using the New Name Dialog Box
1.Select the range you want to name.
2.Choose Formulas, Define Name.
(Alternatively, right-click the selection and then
click Name a Range.)
3.Enter the range name in the Name text box.
4.Use the Scope list to select where you want
the name to be available.
5.Use the Comment text box to enter a
description
6.If the range displayed in the Refers To box is
incorrect, you can correct it
7.Click OK to return to the worksheet.

25. For SUM(B5:D7), If the range B5:D7 is named TotalSales,
the following is equivalent: =SUM(TotalSales)

26. NOTE:
Defining name is used for CONSTANT values,
such as Tax Rate

27. Using Text Formulas
A text formula is a formula that returns text.
Text formulas use the ampersand (&)
operator to work with text cells, text strings
enclosed in quotation marks, and text
function results.
=“soft"&"ware" into a cell, Excel displays
software.

28. Understanding Cell Reference Format

29. Understanding Relative Reference Format
When you use a cell reference in a formula,
Excel looks at the cell address relative to the
location of the formula. For example, the
formula =A1*2.
Excel looks at the cell address relative to the
location of the formula
The relative reference formatis the default
format for Excel.

30. Understanding Absolute Reference Format
Using the absolute reference format, Excel uses the
physical address of the cell.
Absolute reference places dollar signs ($) before
the row and column of the cell address.
Example, =$A$1*2
No matter where you copy or move this formula,
the cell reference doesn’t change.

32. Understanding Mixed Reference Format
•$B4 and B$4 are examples of mixed cell
references.
•In the reference $B4, the column B is
absolute, and the row number is relative
•When you copy the formula, the column
letter, B, does not change, but the row
number will change.
•In the reference B$4, the column letter, B,
changes, but the row number, 4, does not
change.

34. Naming Constants
If you don’t want to clutter a worksheet, you
can name constants without entering them in
the worksheet.
•Choose Formulas, Define Name to display
the New Name dialog box.
•Enter a name for the constant in the
Names text box, and enter an equals sign
(=) and the constant’s value in the Refers
Totext box.

35. What-If Analysis
•The cell references in a formula are called
assumptions.
•Assumptions are values in cells that you can
change to determine new values for
formulas.
•When you change values in cells in a
systematic manner and observe the effects
on specific formula cells, you are performing
a type of what-if analysis.

39. Entering Numbers with Format Symbols
The following step enters the numbers in the
What-If Assumptions table with format symbols.
1. Enter 1,000.00 in cell B19, 3.25% in cell B20,
61.00% in cell B21, 9.00% in cell B22, 5.75% in
cell B23, 50,000.00 in cell B24, and 17.00% in cell
B25 to display the entries using a format based on
the format symbols entered with the numbers.

40. What-If Analysis Worksheet

41. Making Decision -The IF Function

42. The IF function is used to analyse data, test whether or
not it meets certain conditions (logical test) and then act
upon its decision.
The formula can be entered either by typingit or by using
the Function Library on the formula's ribbon, the section
that deals with logical functions
The IF statement is accompanied by three arguments
enclosed in one set of parentheses; the condition to be met
(logical test); the action to be performed if that condition
is true (value if true); the action to be performed if false
(value if false).

43. 1.Click the drop down arrow next to the
LOGICAL button in the FUNCTION LIBARY
Group on the FORMULAS Ribbon
2.Click IF, a dialog box will appear
3.The three arguments can be seen within the box
Using the Function Library

44. For example, using the IF function, cell B9 can be
assigned the following IF function:

45. Logical Operators in IF Functions
IF functions can use logical operators, such as AND, OR,
and NOT. For example, the three IF functions:
=IF(AND(B3>C3, D3<C5), “OK”, “Not OK”) and
=IF(OR(C3>G5, D2<X3), “OK”, “Not OK”) and
=IF(NOT(A6<H7), “OK”, “Not OK”) use logical
operators.
Rather than create large and unwieldy formulae involving
multiple IF statements, the AND, OR and NOT functions
can be used to group logical tests or "conditions" together.

46. And
This function is a logical test to see if all conditions
are true.
If this is the case, the value "TRUE" is returned.
If any of the arguments in the AND statement are
found to be false, the whole statement produces the
value "FALSE".
The following example checks that two cells, B1 and
B2, are both greater than 100.

47. If either one of these two cells contains a value less
than 100, the result of the AND statement is "FALSE"
This can now be wrapped inside an IF function to
produce a more meaningful result.
You may want to add the two figures together if they
are over 100, or display a message indicating that
they are not high enough.
=IF(AND(B1>100,B2>100), B1+B2, “Number not
high”)

48. Another application of AND's is to check that a number is
between certain limits.
The following example checks that a number is between 50 and
100. If it is, the value is entered. If not, a message is displayed;
=IF(AND(B1>50,B1<100), B1, “Number is out of range”)

49. Or
This function is a logical test to see if oneor more
conditions are true.
If just one of the arguments in the OR statement is
found to be true, the whole statement produces the
value "TRUE".
Only when all arguments are false will the value
"FALSE" be returned.
=IF(OR(B1>100,B2>100), “one is OK”, “Figures not
high enough”)

50. In the above formula, only one of the numbers in cells
B1 and B2 has to be over 100 in order for them to be
added together.
The message only appears if neither figure is high
enough.

51. Not
NOT checks to see if the argument is false.
If so, the value "TRUE" is returned.
In the example, the cell contents of B1 are returned
unless the number 100 is encountered.
If B1 is found to contain 100, the message
"Unlucky" is displayed.
=IF(NOT(B1=100),B1, "Unlucky")

52. If sales exceed target, then 2% as bonus on sales
For Bonus calculation
=IF(B2>=C2, B2*2%, 0)
=IF(B2>1000, “GOOD”, “POOR”)

53. Nested Forms of the IF Function
A nested IF function is one in which the action to be
taken for the true or false case includes yet another IF
function.
The second IF function is considered to be nested, or
layered, within the first.
=IF(1st logic test, 1st true value, IF(2nd logic test, 2nd true value, false value)).

54. Nested IF statement
=IF(B2>1000, “GOOD”, IF(B2<600, “POOR”,
“AVERAGE”)

55. To make the above IF statement more flexible, the
logical tests could be amended to measure sales
against cell referencesinstead of figures.
For example, E2 holds the highest sales threshold,
and E3 holds the least sales thresholds.
=IF(B2>$E$2, “GOOD”, IF(B2<$E$3, “POOR”,
“AVERAGE”)

56. To accomplish this:
1.Enter an equal sign in a cell within the
dependent worksheet
2.Click the cell in the source worksheet that
contains the data you want to use in the other
workbook.
Working with Links in Formulas

57. Displaying a System Date
1.Click cell G15 and then click the Insert Function box
in the formula bar.
2.When Excel displays the Insert Function dialog box,
click the ‘Or select a category’ box arrow, and then
select Date & Time in the list.
3.Scroll down in the Select a function list and then click
NOW
4.Click the OK button.
Other way
In cell G15, type =now( ), press Enter.

58. END