I don't understand how you did it as I am still learning Excel.
My book, "Thompson New Perspectives, Excel 2003" in the appendix "A"
pg. A17- A18 doesn't explain how to create the formula.
The first thing to realize is: AND(), OR() and IF() are merely
functions that return values. You can use any function (of the
correct type) any place that you can use an expression or a constant
in an expression.
The IF() function takes 2 or 3 parameters of the form
if(expr1,expr2,expr3). "Expr1" is the condition used to select
"expr2" or "expr3". "Expr2" is the value used if "expr1" is true.
"Expr3" is the value used if "expr1" is false.
The AND() function takes 1 or more parameters of the form
and(expr1,expr2,...). Use AND() when you want to know if __all__ of
the parameters are true at the same time.
The OR() function takes 1 or more parameters of the form
or(expr1,expr2,...). Use OR() when you want to know if at least
__one__ of the parameters is true.
In your case, you wanted the result to be 5 when __one__ of the
following is true: (1) the status is "FT" __and__ years are 1 or
more; __or__ (2) the status is "PT" __and__ years are 2 or more.
The structure of the English description suggests on way to formulate
the nested test, namely: (1) and(status="FT",years>=1); __or__ (2)
and(status="PT",years>=2). From that, we get:
or(and(status="FT",years>=1), and(status="PT",years>=2)).
It is impossible to tell you how to formulate every possible problem.
You have to understand the basic concepts -- namely that functions can
be used anywhere an expression can be used -- and use those concepts
as building blocks.
PS: Although the book's intent is probably to demonstrate this
building-block concept, it is useful to also understand why you should
minimize the number of "building blocks" as much as possible. First,
nesting functions is often error-prone. You may be more likely to
create an obscure error in formulation, simply by misplacing a
parenthesis. Second, Excel 2003 and earlier version have a limit of 7
__nested__ functions. Learning to simplify logic as much as possible
may minimize the function depth.
HTH.
