# if_examples

Document Sample

```					   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