CODEWITHSUNDEEP
excel
Somk
Somk

Reputation: 12057

What does this formula do?

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

Answers (6)

matzone
matzone

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

Dick Kusleika
Dick Kusleika

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

fvu
fvu

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

Ducain
Ducain

Reputation: 1591

The AND allows you to check a boolean condition on more than one statement.

http://support.microsoft.com/kb/267982

Upvotes: 0

IanPudney
IanPudney

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

Jack
Jack

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

Related Questions