Multiple Ifs

**pdgood** · 04-20-2006, 12:21 PM

I'm trying to find a formula that adds to the pricing in certain 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.

rajkohli · 04-20-2006, 12:50 PM

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.

"pdgood" wrote:

>
> I'm trying to find a formula that adds to the pricing in certain
> 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.
>
>
> --
> pdgood
> ------------------------------------------------------------------------
> pdgood's Profile: http://www.excelforum.com/member.php...o&userid=31623
> View this thread: http://www.excelforum.com/showthread...hreadid=534656
>
>

**pdgood** · 04-20-2006, 01:21 PM

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.

Krizhek · 04-20-2006, 02:40 PM

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),...

"pdgood" wrote:

>
> I'm trying to find a formula that adds to the pricing in certain
> 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.
>
>
> --
> pdgood
> ------------------------------------------------------------------------
> pdgood's Profile: http://www.excelforum.com/member.php...o&userid=31623
> View this thread: http://www.excelforum.com/showthread...hreadid=534656
>
>

**pdgood** · 04-20-2006, 04:42 PM

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?

vezerid · 04-20-2006, 05:00 PM

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

David Biddulph · 04-20-2006, 05:10 PM

"pdgood" <[email protected]> wrote in
message news:[email protected]...
>
> I'm trying to find a formula that adds to the pricing in certain
> 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

You could use
=IF(C2>CHOOSE(B2,12,10,8,6,4,2,0,-2,-4,-6,-8,-10),0.1*A2,0)
with the 12,8,10, etc being the range of values with which to compare C2.

If the numbers do go on in the sequence shown, then you could merely use
=IF(C2>14-2*B2,0.1*A2,0)

You may wish to use Data Validation to ensure that your B2 inputs fall
within the 12 possibilities you are expecting.
--
David Biddulph

David Biddulph · 04-20-2006, 06:10 PM

"pdgood" <[email protected]> wrote in
message news:[email protected]...
>
> 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?

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