I'm trying to find a formula that adds to the pricing in certain
situations:
If B2=1 and C212 then I want to add .10*A2 If not 0
If B2=2 and C210 then I want to add .10*A2 If not 0
If B2=3 and C28 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

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 C212 then I want to add .10*A2 If not 0
If B2=2 and C210 then I want to add .10*A2 If not 0
If B2=3 and C28 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

Sorry, that one is over my head. Here is what I have been able to figure
out:
=IF(AND(B2=1,C212),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: http://www.excelforum.com/member.php...o&userid=31623
View this thread: http://www.excelforum.com/showthread...hreadid=534656

Something like this is probably what your looking for

=IF(AND(B2=1,C212),(0.1*A2),IF(AND(B2=2,C210),(0 .1*A2),...

"pdgood" wrote:


I'm trying to find a formula that adds to the pricing in certain
situations:
If B2=1 and C212 then I want to add .10*A2 If not 0
If B2=2 and C210 then I want to add .10*A2 If not 0
If B2=3 and C28 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

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!C212),(Sheet1!A2*0.1), IF(AND(Sheet1!B2=2,Sheet1!C210),(Sheet1!A2*0.1),I F(AND(Sheet1!B2=3,Sheet1!C28),(Sheet1!A2*0.1),IF( AND(Sheet1!B2=4,Sheet1!C27),(Sheet1!A2*0.1),IF(AN D(Sheet1!B2=5,Sheet1!C26),(Sheet1!A2*0.1),IF(AND( Sheet1!B2=6,Sheet1!C25),(Sheet1!A2*0.1),IF(AND(Sh eet1!B2=7,Sheet1!C24),(Sheet1!A2*0.1),IF(AND(Shee t1!B2=8,Sheet1!C23),(Sheet1!A2*0.1),IF(AND(Sheet1 !B2=9,Sheet1!C23),(Sheet1!A2*0.2),IF(AND(Sheet1!B 2=10,Sheet1!C22),(Sheet1!A2*0.3),IF(AND(Sheet1!B2 =11,Sheet1!C22),(Sheet1!A2*0.4),IF(AND(Sheet1!B2= 12,Sheet1!C21),(Sheet1!A2*0.5),0))

but it only shows:
=IF(AND(Sheet1!B2=1,Sheet1!C212),(Sheet1!A2*0.1), IF(AND(Sheet1!B2=2,Sheet1!C210),(Sheet1!A2*0.1),I F(AND(Sheet1!B2=3,Sheet1!C28),(Sheet1!A2*0.1),IF( AND(Sheet1!B2=4,Sheet1!C27),(Sheet1!A2*0.1),IF(AN D(Sheet1!B2=5,Sheet1!C26),(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!C212),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=2,Sheet1!C210),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=3,Sheet1!C28),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=4,Sheet1!C27),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=5,Sheet1!C26),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=6,Sheet1!C25),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=7,Sheet1!C24),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=8,Sheet1!C23),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=9,Sheet1!C23),(Sheet1!A2*0.2),
IF(AND(Sheet1!B2=10,Sheet1!C22),(Sheet1!A2*0.3),
IF(AND(Sheet1!B2=11,Sheet1!C22),(Sheet1!A2*0.4),
IF(AND(Sheet1!B2=12,Sheet1!C21),(Sheet1!A2*0.5),0 ))

Is there a more elegant way to do this?


--
pdgood
------------------------------------------------------------------------
pdgood's Profile: http://www.excelforum.com/member.php...o&userid=31623
View this thread: http://www.excelforum.com/showthread...hreadid=534656

"pdgood" wrote in
message ...

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!C212),(Sheet1!A2*0.1), IF(AND(Sheet1!B2=2,Sheet1!C210),(Sheet1!A2*0.1),I F(AND(Sheet1!B2=3,Sheet1!C28),(Sheet1!A2*0.1),IF( AND(Sheet1!B2=4,Sheet1!C27),(Sheet1!A2*0.1),IF(AN D(Sheet1!B2=5,Sheet1!C26),(Sheet1!A2*0.1),IF(AND( Sheet1!B2=6,Sheet1!C25),(Sheet1!A2*0.1),IF(AND(Sh eet1!B2=7,Sheet1!C24),(Sheet1!A2*0.1),IF(AND(Shee t1!B2=8,Sheet1!C23),(Sheet1!A2*0.1),IF(AND(Sheet1 !B2=9,Sheet1!C23),(Sheet1!A2*0.2),IF(AND(Sheet1!B 2=10,Sheet1!C22),(Sheet1!A2*0.3),IF(AND(Sheet1!B2 =11,Sheet1!C22),(Sheet1!A2*0.4),IF(AND(Sheet1!B2= 12,Sheet1!C21),(Sheet1!A2*0.5),0))

but it only shows:
=IF(AND(Sheet1!B2=1,Sheet1!C212),(Sheet1!A2*0.1), IF(AND(Sheet1!B2=2,Sheet1!C210),(Sheet1!A2*0.1),I F(AND(Sheet1!B2=3,Sheet1!C28),(Sheet1!A2*0.1),IF( AND(Sheet1!B2=4,Sheet1!C27),(Sheet1!A2*0.1),IF(AN D(Sheet1!B2=5,Sheet1!C26),(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!C212),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=2,Sheet1!C210),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=3,Sheet1!C28),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=4,Sheet1!C27),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=5,Sheet1!C26),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=6,Sheet1!C25),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=7,Sheet1!C24),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=8,Sheet1!C23),(Sheet1!A2*0.1),
IF(AND(Sheet1!B2=9,Sheet1!C23),(Sheet1!A2*0.2),
IF(AND(Sheet1!B2=10,Sheet1!C22),(Sheet1!A2*0.3),
IF(AND(Sheet1!B2=11,Sheet1!C22),(Sheet1!A2*0.4),
IF(AND(Sheet1!B2=12,Sheet1!C21),(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!C2VLOOKUP(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.5


David Biddulph

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

=.10*A2*(IF(AND(B2=1,C212),1,0)+IF(AND(B2=2,C210 ),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

"pdgood" wrote in
message ...

I'm trying to find a formula that adds to the pricing in certain
situations:
If B2=1 and C212 then I want to add .10*A2 If not 0
If B2=2 and C210 then I want to add .10*A2 If not 0
If B2=3 and C28 then I want to add .10*A2 If not 0
etc. through 12 possibilities for B2

You could use
=IF(C2CHOOSE(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(C214-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