software

I'm trying to find a formula that adds to the pricing in certain
situations:
If B2=1 and C2gt;12 then I want to add .10*A2 If not 0
If B2=2 and C2gt;10 then I want to add .10*A2 If not 0
If B2=3 and C2gt;8 then I want to add .10*A2 If not 0
etc. through 12 possibilities for B2
Thanks.--
pdgood
------------------------------------------------------------------------
pdgood's Profile: www.excelforum.com/member.php...oamp;userid=31623
View this thread: www.excelforum.com/showthread...hreadid=534656See 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)),quot;Happy Birthdayquot;,quot; quot;)

The above formula check the date store in column D4 with the system date AND
IF DAY and MONTH is equal then display quot;Happy Birthdayquot; otherwise blankquot;

Let us know, if the information is useful to you.quot;pdgoodquot; wrote:

gt;
gt; I'm trying to find a formula that adds to the pricing in certain
gt; situations:
gt; If B2=1 and C2gt;12 then I want to add .10*A2 If not 0
gt; If B2=2 and C2gt;10 then I want to add .10*A2 If not 0
gt; If B2=3 and C2gt;8 then I want to add .10*A2 If not 0
gt; etc. through 12 possibilities for B2
gt; Thanks.
gt;
gt;
gt; --
gt; pdgood
gt; ------------------------------------------------------------------------
gt; pdgood's Profile: www.excelforum.com/member.php...oamp;userid=31623
gt; View this thread: www.excelforum.com/showthread...hreadid=534656
gt;
gt;


Sorry, that one is over my head. Here is what I have been able to figure
out:
=IF(AND(B2=1,C2gt;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.--
pdgood
------------------------------------------------------------------------
pdgood's Profile: www.excelforum.com/member.php...oamp;userid=31623
View this thread: www.excelforum.com/showthread...hreadid=534656Something like this is probably what your looking for

=IF(AND(B2=1,C2gt;12),(0.1*A2),IF(AND(B2=2,C2gt;10),(0 .1*A2),...

quot;pdgoodquot; wrote:

gt;
gt; I'm trying to find a formula that adds to the pricing in certain
gt; situations:
gt; If B2=1 and C2gt;12 then I want to add .10*A2 If not 0
gt; If B2=2 and C2gt;10 then I want to add .10*A2 If not 0
gt; If B2=3 and C2gt;8 then I want to add .10*A2 If not 0
gt; etc. through 12 possibilities for B2
gt; Thanks.
gt;
gt;
gt; --
gt; pdgood
gt; ------------------------------------------------------------------------
gt; pdgood's Profile: www.excelforum.com/member.php...oamp;userid=31623
gt; View this thread: www.excelforum.com/showthread...hreadid=534656
gt;
gt;


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!C2gt;12),(Sheet1!A2*0.1), IF(AND(Sheet1!B2=2,Sheet1!C2gt;10),(Sheet1!A2*0.1),I F(AND(Sheet1!B2=3,Sheet1!C2gt;8),(Sheet1!A2*0.1),IF( AND(Sheet1!B2=4,Sheet1!C2gt;7),(Sheet1!A2*0.1),IF(AN D(Sheet1!B2=5,Sheet1!C2gt;6),(Sheet1!A2*0.1),IF(AND( Sheet1!B2=6,Sheet1!C2gt;5),(Sheet1!A2*0.1),IF(AND(Sh eet1!B2=7,Sheet1!C2gt;4),(Sheet1!A2*0.1),IF(AND(Shee t1!B2=8,Sheet1!C2gt;3),(Sheet1!A2*0.1),IF(AND(Sheet1 !B2=9,Sheet1!C2gt;3),(Sheet1!A2*0.2),IF(AND(Sheet1!B 2=10,Sheet1!C2gt;2),(Sheet1!A2*0.3),IF(AND(Sheet1!B2 =11,Sheet1!C2gt;2),(Sheet1!A2*0.4),IF(AND(Sheet1!B2= 12,Sheet1!C2gt;1),(Sheet1!A2*0.5),0))

but it only shows:
=IF(AND(Sheet1!B2=1,Sheet1!C2gt;12),(Sheet1!A2*0.1), IF(AND(Sheet1!B2=2,Sheet1!C2gt;10),(Sheet1!A2*0.1),I F(AND(Sheet1!B2=3,Sheet1!C2gt;8),(Sheet1!A2*0.1),IF( AND(Sheet1!B2=4,Sheet1!C2gt;7),(Sheet1!A2*0.1),IF(AN D(Sheet1!B2=5,Sheet1!C2gt;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!C2gt;12),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=2,Sheet1!C2gt;10),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=3,Sheet1!C2gt;8),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=4,Sheet1!C2gt;7),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=5,Sheet1!C2gt;6),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=6,Sheet1!C2gt;5),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=7,Sheet1!C2gt;4),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=8,Sheet1!C2gt;3),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=9,Sheet1!C2gt;3),(Sheet1!A2*0.2),
IF(AND(Sheet1!B2=10,Sheet1!C2gt;2),(Sheet1!A2*0.3),
IF(AND(Sheet1!B2=11,Sheet1!C2gt;2),(Sheet1!A2*0.4),
IF(AND(Sheet1!B2=12,Sheet1!C2gt;1),(Sheet1!A2*0.5),0 ))

Is there a more elegant way to do this?--
pdgood
------------------------------------------------------------------------
pdgood's Profile: www.excelforum.com/member.php...oamp;userid=31623
View this thread: www.excelforum.com/showthread...hreadid=534656Since you are always adding .10*A2, you can use the following
construct:

=.10*A2*(IF(AND(B2=1,C2gt;12),1,0) IF(AND(B2=2,C2gt;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 Vezeridesquot;pdgoodquot; gt; wrote in
message news
gt;
gt; I'm trying to find a formula that adds to the pricing in certain
gt; situations:
gt; If B2=1 and C2gt;12 then I want to add .10*A2 If not 0
gt; If B2=2 and C2gt;10 then I want to add .10*A2 If not 0
gt; If B2=3 and C2gt;8 then I want to add .10*A2 If not 0
gt; etc. through 12 possibilities for B2

You could use
=IF(C2gt;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(C2gt;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
quot;pdgoodquot; gt; wrote in
message news
gt;
gt; Thanks, that works except....when I paste it into the formula window I
gt; am exceeding some sort of limit.
gt;
gt; I am pasting:
gt; =IF(AND(Sheet1!B2=1,Sheet1!C2gt;12),(Sheet1!A2*0.1), IF(AND(Sheet1!B2=2,Sheet1!C2gt;10),(Sheet1!A2*0.1),I F(AND(Sheet1!B2=3,Sheet1!C2gt;8),(Sheet1!A2*0.1),IF( AND(Sheet1!B2=4,Sheet1!C2gt;7),(Sheet1!A2*0.1),IF(AN D(Sheet1!B2=5,Sheet1!C2gt;6),(Sheet1!A2*0.1),IF(AND( Sheet1!B2=6,Sheet1!C2gt;5),(Sheet1!A2*0.1),IF(AND(Sh eet1!B2=7,Sheet1!C2gt;4),(Sheet1!A2*0.1),IF(AND(Shee t1!B2=8,Sheet1!C2gt;3),(Sheet1!A2*0.1),IF(AND(Sheet1 !B2=9,Sheet1!C2gt;3),(Sheet1!A2*0.2),IF(AND(Sheet1!B 2=10,Sheet1!C2gt;2),(Sheet1!A2*0.3),IF(AND(Sheet1!B2 =11,Sheet1!C2gt;2),(Sheet1!A2*0.4),IF(AND(Sheet1!B2= 12,Sheet1!C2gt;1),(Sheet1!A2*0.5),0))
gt;
gt; but it only shows:
gt; =IF(AND(Sheet1!B2=1,Sheet1!C2gt;12),(Sheet1!A2*0.1), IF(AND(Sheet1!B2=2,Sheet1!C2gt;10),(Sheet1!A2*0.1),I F(AND(Sheet1!B2=3,Sheet1!C2gt;8),(Sheet1!A2*0.1),IF( AND(Sheet1!B2=4,Sheet1!C2gt;7),(Sheet1!A2*0.1),IF(AN D(Sheet1!B2=5,Sheet1!C2gt;6),(Sheet1!A2*0.1),IF(AND( Sheet
gt;
gt; With line breaks so that it is easier to read and understand, what I'm
gt; trying to accomplish is:
gt; =IF(AND(Sheet1!B2=1,Sheet1!C2gt;12),(Sheet1!A2*0.1),
gt; IF(AND(Sheet1!B2=2,Sheet1!C2gt;10),(Sheet1!A2*0.1),
gt; IF(AND(Sheet1!B2=3,Sheet1!C2gt;8),(Sheet1!A2*0.1),
gt; IF(AND(Sheet1!B2=4,Sheet1!C2gt;7),(Sheet1!A2*0.1),
gt; IF(AND(Sheet1!B2=5,Sheet1!C2gt;6),(Sheet1!A2*0.1),
gt; IF(AND(Sheet1!B2=6,Sheet1!C2gt;5),(Sheet1!A2*0.1),
gt; IF(AND(Sheet1!B2=7,Sheet1!C2gt;4),(Sheet1!A2*0.1),
gt; IF(AND(Sheet1!B2=8,Sheet1!C2gt;3),(Sheet1!A2*0.1),
gt; IF(AND(Sheet1!B2=9,Sheet1!C2gt;3),(Sheet1!A2*0.2),
gt; IF(AND(Sheet1!B2=10,Sheet1!C2gt;2),(Sheet1!A2*0.3),
gt; IF(AND(Sheet1!B2=11,Sheet1!C2gt;2),(Sheet1!A2*0.4),
gt; IF(AND(Sheet1!B2=12,Sheet1!C2gt;1),(Sheet1!A2*0.5),0 ))
gt;
gt; 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, amp; do a VLOOKUP in those lists:

=IF(Sheet1!C2gt;VLOOKUP(Sheet1!B2,Sheet3!A1:C12,2),V LOOKUP(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.5David Biddulph