Reputation: 12057
I am trying to understand this Excel formula, and understand IF statments, but not the structure of this one. What does the AND do?
=IF(AND(C8>59,C8<65),5%,
IF(AND(C8>64,C8<70),5.5%,
IF(AND(C8>69,C8<75),6%,
IF(AND(C8>74,C8<80),6.5%,
IF(AND(C8>79,C8<85),7%,
IF(AND(C8>84,C8<90),7.5%,
IF(AND(C8>89,C8<250),8%)
)
)
)
)
)
)*D8
Upvotes: 2
Views: 193
Reputation: 5719
Easy reading is ..
IF C8>59 and C8<65 The result is D8 * 5%
IF C8>64 and C8<70 The result is D8 * 5.5%
IF C8>69 and C8<75 The result is D8 * 6%
IF C8>74 and C8<80 The result is D8 * 6.5%
IF C8>79 and C8<85 The result is D8 * 7%
IF C8>84 and C8<90 The result is D8 * 7.5%
IF C8>89 and C8<250 The result is D8 * 8%
So if your C8 is 66 and D8 is 5 then the result is 5 * 0.55 = 2.75
Upvotes: 0
Reputation: 33185
It's (almost) equivalent to:
60 64 0.05
65 69 0.055
70 74 0.06
75 79 0.065
80 84 0.07
85 89 0.075
90 0.08
=VLOOKUP(C8,$A$1:$C$7,3,TRUE)
Upvotes: 2
Reputation: 32983
Excel's AND takes 2 or more arguments that all must evaluate to true for the end result to be true. In this case cell C8 is compared with 2 values to create a "between" like construction.
Rewritten in some kind of pseudocode this is what it could look like:
if ((C8>59) and (C8<65)) then
// meaning, for all values between 60 and 64, return 5%
returnvalue = 5%
else
if ((C8>64) and (C8<70)) then
// meaning, for all values between 65 and 70, return 5.5%
returnvalue = 5.5%
and so on
Upvotes: 0
Reputation: 1591
The AND allows you to check a boolean condition on more than one statement.
http://support.microsoft.com/kb/267982
Upvotes: 0
Reputation: 6041
The AND()
function means that "all parameters must be true" for the function to evaluate to true. Thus, your first line, =IF(AND(C8>59,C8<65),5%,...
means "if C8
is between 69 and 65, return 5%. otherwise, go on to next line." Because excel IF
statements have the structure IF(conditional, true result, false result)
, if the statement on each line in your equation is not true, the the evaluation continues to the next line. Otherwise, it stops and returns the percentage on that line.
Upvotes: 0
Reputation: 2830
The AND
function takes two boolean inputs and returns TRUE
if they are both true.
The entire formula looks at the value of cell C8
and returns the following:
5% if C8
is between 59 and 65,
5.5% if C8
is between 64 and 70,
6% if C8
is between 69 and 75... et cetera.
Oh, and that is multiplied by whatever is found in cell D8
Upvotes: 2