# Operators - Excel

Document Sample

```					        A          B           C          D           E          F          G           H          I         J          K     L           M                         N                      O          P              Q            R

1   You need to know Order of Precedence rules too!                                                                              Order of Excel Operations in Formulas (from first to last)
2                                                                                                                                      Operator                 Description
3                                                                               What is 5 plus 3 times 2?                        : (colon)          reference operator                                      If a formula contains
operators at the same
4                                                                               Are you thinking the                               (single space)   reference operator                                      level, such as addition
5                                                                               answer is 16?                                    , (comma)          reference operator                                      and subtraction, Excel
6                                                                                                                                -                  negation                                                evaluates them from
left to right.
7 Using Excel, if you enter:          The answer is..                                                                            %                  percent
8             =5+3*2                          11                                                                                 ^                  exponentiation
9             (Rule: multiplication occurs before addition)                                                                      * and /            multiplication and division
10                                                                                                                                + and -            addition and subtraction
11 But if you enter                    The answer is..                                                                            &                  concatenation of text strings
12             =(5+3)*2                        16                                                                                 = < > <= >= <>     comparisons
13             (Rule: operations within parentheses take precedence over others)
14                                                                                                                                Note: Items within parentheses calculate first; here is a well-known memory aid.
15                                    There's more information in the range M1:R14!
16
17
18
19                                                                                                                                P=parentheses; E=exponentiation; M=multiplication; D=Division; A=addition; S=subtraction

5a5bd67f-22e1-4918-af1c-c90c1c6a55d6.xlsx                                                                        Precedence                                                                                                    11/26/2011
A             B         C       D         E           F          G           H           I      J         K     L            M               N            O           P             Q             R            S    T

1   I can tell you about calculation operators!                                                                                Examples of Using Operators in Formulas
2
3 Arithmetic Operators                                                 Comparison Operators                                    Arithmetic Example in Cell N6
4    + (plus sign)  Addition                                               =    Equal to                                       Wage per hour        \$ 7.50
5   - (minus sign) Subtraction                                             >    Greater than                                   # of hours worked           10
6     * (asterisk)  Multiplication                                         <    Less than                                      Gross pay            \$ 75.00                 formula is =N4*N5
7 / (forward slash) Division                                              >=    Greater than or equal to
8       ^ (caret)   Exponentiation                                        <=    Less than or equal to                          Comparison Example in Cell N11
9                                                                         <>    Not equal to                                   Actual Net Income     16200
10 Text Concatenation Operator                                                                                                  Target Net Income     15000
11 & (ampersand) Joins one or more               Reference Operators                                                            Status            Goal Met!                  formula is =IF(N9>=N10,"Goal Met!","Under Budget!")
12                   text entries to form           : (colon) Connects first and last cells in a range
13                   a single entry               , (comma) Combines multiple references into one                               Concatenation Example in Cell M14
14                                                                                                                              Mary Smith        Mary         Smith
15                                              Go to the range M1:T25 to view examples!                                                            formula is =N14&" "&O14
16
17                                                                                                                              Reference Examples in Cell N23
18                                                                                                                                                    Jan           Feb          Mar
19                                                                                                                              Cut/edge grass             60          45              60
20                                                                                                                              Wash windows               20          20              20
21                                                                                                                              Trim trees/shrubs          30                          30
22
23                                                                                                                              1st Qtr yard care            225             formula is =SUM(N19:P19,N21:P21)
24                                                                                                                                                  Note: Sum of cutting grass (N19:P19) and trimming trees/shrubs (N21:P21)

5a5bd67f-22e1-4918-af1c-c90c1c6a55d6.xlsx                                                                       Operators                                                                                                      11/26/2011
Puzzle Power
Condensed Income Statements - 2004
Jan         Feb        Mar     Apr      May     Jun     Jul
Sales                             10,000       8,000      9,000   7,000   10,500   8,800   8,800
less Cost of Goods Sold           6,000       4,600      5,500   4,400    6,200   5,100   4,900
Gross Profit
less Commissions
less Other Expenses                 990            780    950    1,925    1,050    825     970
Net Income (Loss)

Additional Analysis:
Gross Profit %

Note: Commissions are calculated as a constant percentage of Sales.
<--Enter Current Year's Commission Rate (%) in cell A16.

Date last revised: (current date here)
Revised by: (your name here)
Aug     Sep     Oct     Nov     Dec     Annual
10,500   9,700   8,300   6,500   7,900
6,700   5,950   4,575   4,240   4,890

1,015    880    1,175   1,710   1,870
Puzzle Power
Condensed Income Statements - 2004
Jan         Feb       Mar        Apr     May      Jun      Jul
Sales                              10,000        8,000    9,000      7,000   10,500    8,800   8,800
less Cost of Goods Sold             6,000       4,600    5,500      4,400    6,200    5,100   4,900
Gross Profit                        4,000       3,400     3,500      2,600    4,300   3,700    3,900
less Commissions
less Other Expenses                  990           780     950      1,925    1,050     825     970
Net Income (Loss)

Additional Analysis:
Gross Profit %

Note: Commissions are calculated as a constant percentage of Sales.
<--Enter Current Year's Commission Rate (%) in cell A16.

Date last revised: (current date here)
Revised by: (your name here)

Part II: Using Type-and-Point to
Enter Formulas
Aug      Sep      Oct     Nov     Dec      Annual
10,500    9,700   8,300   6,500    7,900
6,700    5,950   4,575   4,240    4,890
3,800   3,750    3,725   2,260   3,010

1,015     880    1,175   1,710   1,870
Puzzle Power
Condensed Income Statements - 2004
Jan                 Feb         Mar        Apr        May
Sales                                      10000                  8000        9000       7000       10500
less Cost of Goods Sold                   6000                   4600        5500       4400       6200
Gross Profit                               =B5 - B6               =C5 - C6    =D5 - D6   =E5 - E6   =F5 - F6
less Commissions
less Other Expenses                       990                    780         950        1925       1050
Net Income (Loss)

Additional Analysis:
Gross Profit %

Note: Commissions are calculated as a constant percentage of Sales.
<--Enter Current Year's Commission Rate (%) in cell A16.

Date last revised: (current date here)
Revised by: (your name here)

Part II A: Displaying
Formulas
Jun        Jul       Aug      Sep         Oct        Nov        Dec     Annual
8800       8800       10500 9700          8300       6500       7900
5100       4900       6700     5950       4575       4240       4890
=G5 - G6   =H5 - H6   =I5 - I6 =J5 - J6   =K5 - K6   =L5 - L6   =M5 - M6

825        970        1015    880         1175       1710       1870
A                     B          C              D             E              F             G
1   The When and How of Absolute Addressing -- Wrong
2
3   Scenario: Commissions are calculated as a constant percentage of Sales.
4                              10% <-- Enter Current Year's Commission Rate (%) in cell A4
5
6   Case 1: Copied the Commissions formula =B8*A4 from B9 to C9:E9
7                                      Qtr 1     Qtr 2      Qtr 3               Qtr 4
8   Sales                              27,000    26,300     29,000              22,700
9   Commissions on Sales                2,700 #VALUE!             0                   0
10
11   Formulas in row 9 after the copy   =B8*A4   =C8*B4        =D8*C4        =E8:D4                        ERRORS!
12
Problem: The A4 portion of the original formula copies relative to
13                                               position (B4, C4, D4 in the next 3 cells). To produce correct
14                                               results, the second cell reference must remain A4 -- that is the
15                                               reference to cell A4 must be made absolute.
16

5a5bd67f-22e1-4918-af1c-c90c1c6a55d6.xlsx                                    Abs1                                     11/26/2011
A                         B               C                D                E        F      G
1   The When and How of Absolute Addressing -- Right
2
3   Scenario: Commissions are calculated as a constant percentage of Sales.
4                               10% <-- Enter Current Year's Commission Rate (%) in cell A4
5
6   Case 2: Copied the Commissions formula =B8*\$A\$4 from B9 to C9:E9
7                                       Qtr 1       Qtr 2       Qtr 3                        Qtr 4
8   Sales                                27,000      26,300      29,000                       22,700
9   Commissions on Sales                  2,700        2,630      2,900                        2,270 CORRECT!
10
11   Formulas in row 9 after the copy   =B8*\$A\$4        =C8*\$A\$4         =D8*\$A\$4          =E8*\$A\$4
12
13
14
15                                      1st cell reference B8 changed relative to position (C8, D8, E8)
16                                      2nd cell reference \$A\$4 was unchanged in the copy

5a5bd67f-22e1-4918-af1c-c90c1c6a55d6.xlsx                                           Abs2                             11/26/2011
Puzzle Power
Condensed Income Statements - 2004
Jan       Feb      Mar       Apr      May      Jun         Jul
Sales                           10,000      8,000   9,000     7,000    10,500    8,800      8,800
less Cost of Goods Sold          6,000     4,600   5,500     4,400     6,200    5,100      4,900
Gross Profit                      4,000     3,400   3,500     2,600     4,300    3,700      3,900
less Commissions                1,000       800      900      700      1,050     880         880
less Other Expenses                990       780     950     1,925     1,050      825        970
Net Income (Loss)                2,010     1,820    1,650       (25)    2,200   1,995       2,050

Additional Analysis:
Gross Profit %                   40.0%     42.5%    38.9%     37.1%    41.0%        42.0%   44.3%

Note: Commissions are calculated as a constant percentage of Sales.
10% <--Enter Current Year's Commission Rate (%) in cell A16.

Date last revised: date
Revised by: Jon Q. Nerd

Lesson 4: Creating and Copying
Formulas with Relative or
Absolute Cell References
Aug      Sep      Oct     Nov      Dec      Annual
10,500    9,700   8,300   6,500     7,900   105,000
6,700    5,950   4,575   4,240     4,890    63,055
3,800    3,750   3,725   2,260     3,010    41,945
1,050     970      830     650      790     10,500
1,015      880   1,175   1,710     1,870    14,140
1,735   1,900    1,720    (100)     350     17,305

36.2%    38.7%    44.9%   34.8%    38.1%      39.9%

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 10 posted: 11/27/2011 language: English pages: 12