Multiple Ifs

P

pdgood

I'm trying to find a formula that adds to the pricing in certai
situations:
If B2=1 and C2>12 then I want to add .10*A2 If not 0
If B2=2 and C2>10 then I want to add .10*A2 If not 0
If B2=3 and C2>8 then I want to add .10*A2 If not 0
etc. through 12 possibilities for B2
Thanks
 
R

rajkohli

See the following formula, where I have used multiple IFs. May this formula
give you a little hint about how to use multiple IFs with AND command.

=IF(AND(DAY(TODAY())=DAY(D4),MONTH(TODAY())=MONTH(D4)),"Happy Birthday"," ")

The above formula check the date store in column D4 with the system date AND
IF DAY and MONTH is equal then display "Happy Birthday" otherwise blank"

Let us know, if the information is useful to you.
 
P

pdgood

Sorry, that one is over my head. Here is what I have been able to figure
out:
=IF(AND(B2=1,C2>12),0.1*A2,0)
The problem is how do you string several of these together. I'm
guessing that there is an IF(OR command but I haven't been able to find
the right location for it.
 
K

Krizhek

Something like this is probably what your looking for

=IF(AND(B2=1,C2>12),(0.1*A2),IF(AND(B2=2,C2>10),(0.1*A2),...
 
P

pdgood

Thanks, that works except....when I paste it into the formula window I
am exceeding some sort of limit.

I am pasting:
=IF(AND(Sheet1!B2=1,Sheet1!C2>12),(Sheet1!A2*0.1),IF(AND(Sheet1!B2=2,Sheet1!C2>10),(Sheet1!A2*0.1),IF(AND(Sheet1!B2=3,Sheet1!C2>8),(Sheet1!A2*0.1),IF(AND(Sheet1!B2=4,Sheet1!C2>7),(Sheet1!A2*0.1),IF(AND(Sheet1!B2=5,Sheet1!C2>6),(Sheet1!A2*0.1),IF(AND(Sheet1!B2=6,Sheet1!C2>5),(Sheet1!A2*0.1),IF(AND(Sheet1!B2=7,Sheet1!C2>4),(Sheet1!A2*0.1),IF(AND(Sheet1!B2=8,Sheet1!C2>3),(Sheet1!A2*0.1),IF(AND(Sheet1!B2=9,Sheet1!C2>3),(Sheet1!A2*0.2),IF(AND(Sheet1!B2=10,Sheet1!C2>2),(Sheet1!A2*0.3),IF(AND(Sheet1!B2=11,Sheet1!C2>2),(Sheet1!A2*0.4),IF(AND(Sheet1!B2=12,Sheet1!C2>1),(Sheet1!A2*0.5),0))

but it only shows:
=IF(AND(Sheet1!B2=1,Sheet1!C2>12),(Sheet1!A2*0.1),IF(AND(Sheet1!B2=2,Sheet1!C2>10),(Sheet1!A2*0.1),IF(AND(Sheet1!B2=3,Sheet1!C2>8),(Sheet1!A2*0.1),IF(AND(Sheet1!B2=4,Sheet1!C2>7),(Sheet1!A2*0.1),IF(AND(Sheet1!B2=5,Sheet1!C2>6),(Sheet1!A2*0.1),IF(AND(Sheet

With line breaks so that it is easier to read and understand, what I'm
trying to accomplish is:
=IF(AND(Sheet1!B2=1,Sheet1!C2>12),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=2,Sheet1!C2>10),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=3,Sheet1!C2>8),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=4,Sheet1!C2>7),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=5,Sheet1!C2>6),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=6,Sheet1!C2>5),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=7,Sheet1!C2>4),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=8,Sheet1!C2>3),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=9,Sheet1!C2>3),(Sheet1!A2*0.2),
IF(AND(Sheet1!B2=10,Sheet1!C2>2),(Sheet1!A2*0.3),
IF(AND(Sheet1!B2=11,Sheet1!C2>2),(Sheet1!A2*0.4),
IF(AND(Sheet1!B2=12,Sheet1!C2>1),(Sheet1!A2*0.5),0))

Is there a more elegant way to do this?
 
V

vezerid

Since you are always adding .10*A2, you can use the following
construct:

=.10*A2*(IF(AND(B2=1,C2>12),1,0)+IF(AND(B2=2,C2>10),1,0)+...+...)

Also, if the pattern of your example continues (i.e. B2 starting from 1
and increasing by 1, C2 starting from 12 and decreasing by 2), you
could possible compact it all in a single formula:

=IF(C2=12-2*(B1-1),.10*A2,0)

HTH
Kostis Vezerides
 
D

David Biddulph

pdgood said:
Thanks, that works except....when I paste it into the formula window I
am exceeding some sort of limit.

I am pasting:
=IF(AND(Sheet1!B2=1,Sheet1!C2>12),(Sheet1!A2*0.1),IF(AND(Sheet1!B2=2,Sheet1!C2>10),(Sheet1!A2*0.1),IF(AND(Sheet1!B2=3,Sheet1!C2>8),(Sheet1!A2*0.1),IF(AND(Sheet1!B2=4,Sheet1!C2>7),(Sheet1!A2*0.1),IF(AND(Sheet1!B2=5,Sheet1!C2>6),(Sheet1!A2*0.1),IF(AND(Sheet1!B2=6,Sheet1!C2>5),(Sheet1!A2*0.1),IF(AND(Sheet1!B2=7,Sheet1!C2>4),(Sheet1!A2*0.1),IF(AND(Sheet1!B2=8,Sheet1!C2>3),(Sheet1!A2*0.1),IF(AND(Sheet1!B2=9,Sheet1!C2>3),(Sheet1!A2*0.2),IF(AND(Sheet1!B2=10,Sheet1!C2>2),(Sheet1!A2*0.3),IF(AND(Sheet1!B2=11,Sheet1!C2>2),(Sheet1!A2*0.4),IF(AND(Sheet1!B2=12,Sheet1!C2>1),(Sheet1!A2*0.5),0))

but it only shows:
=IF(AND(Sheet1!B2=1,Sheet1!C2>12),(Sheet1!A2*0.1),IF(AND(Sheet1!B2=2,Sheet1!C2>10),(Sheet1!A2*0.1),IF(AND(Sheet1!B2=3,Sheet1!C2>8),(Sheet1!A2*0.1),IF(AND(Sheet1!B2=4,Sheet1!C2>7),(Sheet1!A2*0.1),IF(AND(Sheet1!B2=5,Sheet1!C2>6),(Sheet1!A2*0.1),IF(AND(Sheet

With line breaks so that it is easier to read and understand, what I'm
trying to accomplish is:
=IF(AND(Sheet1!B2=1,Sheet1!C2>12),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=2,Sheet1!C2>10),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=3,Sheet1!C2>8),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=4,Sheet1!C2>7),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=5,Sheet1!C2>6),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=6,Sheet1!C2>5),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=7,Sheet1!C2>4),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=8,Sheet1!C2>3),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=9,Sheet1!C2>3),(Sheet1!A2*0.2),
IF(AND(Sheet1!B2=10,Sheet1!C2>2),(Sheet1!A2*0.3),
IF(AND(Sheet1!B2=11,Sheet1!C2>2),(Sheet1!A2*0.4),
IF(AND(Sheet1!B2=12,Sheet1!C2>1),(Sheet1!A2*0.5),0))

Is there a more elegant way to do this?
Click to expand...

If B2 is constrained to being one of the values listed (perhaps by Data
Validation?), then you could use a separate list of the B2 values, C2
comparison values, and A2 multiplier values, & do a VLOOKUP in those lists:

=IF(Sheet1!C2>VLOOKUP(Sheet1!B2,Sheet3!A1:C12,2),VLOOKUP(Sheet1!B2,Sheet3!A1:C12,3)*Sheet1!A2,0)

The array Sheet3!A1:C12 would look like this:
1 12 0.1

2 10 0.1

3 8 0.1

4 7 0.1

5 6 0.1

6 5 0.1

7 4 0.1

8 3 0.1

9 3 0.2

10 2 0.3

11 2 0.4

12 1 0.5


David Biddulph
 
Top