Docstoc

if_examples

Document Sample
if_examples Powered By Docstoc
					   A series of examples on the IF function and
   logical functions. In the simplest version we
   compute income taxes with a single tax rate.
   Any income over the cutoff value is taxed at
   this rate. This sheet also illustrates the use of
   named cells.




          Tax Rate                       25%
          Cutoff                       ####

Income Tax

$30,000                       $0
$50,000                   $2,500
$70,000                   $7,500
$90,000                  $12,500
#####                    $17,500
  More nested If Functions--
  -win lose or tie? Here is
  an example with
  conditional formatting.


Game        My Score Opponent's ScoreResult
        1         10               8 Won
        2          5               3 Won
        3          8               1 Won
        4          7               9 Lost
        5          2               1 Won
        6          7               1 Won
        7          7               5 Won
        8          1               1 Tied
        9          1               6 Lost
       10          0               6 Lost
       11          7               9 Lost
       12          6               6 Tied
An example in which there are two
different rates, as well as a no-tax cutoff.
To solve this we nest the use of the IF
function. Observe that this produces
dreadfully long formulas that are very
difficult to read and debug. A way around
this dilemma is illustrated on the next
sheet.




                       Table of Tax Rates
                       Low Income             15%
                       High Income            25%
                       Cutoff             $120,000
                       No-tax cutoff       $40,000

          Income      Tax
              $20,000               $0
              $40,000               $0
              $60,000           $3,000
              $80,000           $6,000
             $100,000           $9,000
             $120,000          $12,000
             $140,000          $17,000
             $160,000          $22,000
             $180,000          $27,000
             $200,000          $32,000
             $220,000          $37,000
             $240,000          $42,000
             $260,000          $47,000
The tax example redone. Sometimes it is
easier to break one complicated formulas
up into several simpler ones. In
particular, it is much easier to track down
errors this way, since you can see the
intermediate steps in the process.


                                 Table of Tax Rates
                                 Low Income          15%
                                 High Income         25%
                                 Cutoff          $120,000
                                 No-tax cutoff    $40,000

                    Income      Tax
                        $20,000          $0
                        $40,000          $0
                        $60,000      $3,000
                        $80,000      $6,000
                       $100,000      $9,000
                       $120,000     $12,000
                       $140,000     $17,000
                       $160,000     $22,000
                       $180,000     $27,000
                       $200,000     $32,000
                       $220,000     $37,000
                       $240,000     $42,000
                       $260,000     $47,000
Low Tax             High Tax
          -$3,000              -13000
               $0               -8000
           $3,000               -3000
           $6,000                2000
           $9,000                7000
          $12,000               12000
          $15,000               17000
          $18,000               22000
          $21,000               27000
          $24,000               32000
          $27,000               37000
          $30,000               42000
          $33,000               47000
    Problem 11-10 from the textbook: An
    employee is eligible for retirement if he or
    she meets at least one of the following
    criteria: At least 65 years of age, at least 40
    years on the job, or at least 60 years of age
    with at least 30 years on the job. This
    illustrates the AND and OR functions.
Employee Years on Job Age                  Eiligibility

Ted                      41           63   Eligible
Fred                     42           70   Eligible
Jed                      26           58   Ineligible
Ed                       28           62   Ineligible
Ned                      31           49   Ineligible
Amy                      31           67   Eligible
Mamie                    38           63   Eligible
Jamie                    29           69   Eligible
               The previous example redone:
               Sometimes it is easier to write a
               lot of simple formulas to
               compute intermediate results
               than to write one large complex
               formula. It's easier to read, and
               easier to detect mistakes. The
               columns containing the
               intermediate results can be
               hidden to make a cleaner
               presentation, although here
               everything is displayed.


Employee Years on JobAge            C1      C2     C3      Eligibility

Ted               41           63   FALSE   TRUE   TRUE    Eligible
Fred              42           70   TRUE    TRUE   TRUE    Eligible
Jed               26           58   FALSE   ###    FALSE   Ineligible
Ed                28           62   FALSE   ###    FALSE   Ineligible
Ned               31           49   FALSE   ###    FALSE   Ineligible
Amy               31           67   TRUE    ###    TRUE    Eligible
Mamie             38           63   FALSE   ###    TRUE    Eligible
Jamie             29           69   TRUE    ###    FALSE   Eligible
           Not to beat the problem to death, but you
           could do it all with Nested-Ifs, dispensing with
           ANDs and ORs and approaching the problem
           in a sort of decision-tree fashion.


                   Age
Employee Years on Job              Age >= 60? Age >= 65?Years>=30?

Ted               41          63      TRUE          FALSE     TRUE
Fred              42          70      TRUE          TRUE      TRUE
Jed               26          58      FALSE         FALSE     FALSE
Ed                28          62      TRUE          FALSE     FALSE
Ned               31          49      FALSE         FALSE     TRUE
Amy               31          67      TRUE          TRUE      TRUE
Mamie             38          63      TRUE          FALSE     TRUE
Jamie             29          69      TRUE          TRUE      FALSE
Years>=40?

  TRUE    Eligible
  TRUE    Eligible
  FALSE   Ineligible
  FALSE   Ineligible
  FALSE   Ineligible
  FALSE   Eligible
  FALSE   Eligible
  FALSE   Eligible

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:5
posted:3/30/2012
language:
pages:9