Can I combine IF/AND/OR statements?

[CJOHNSO92]

Can I combine 2 IF/AND/OR statements? Below are the two formulas -
individually, they each calculate properly.

IF(AND(OR(Cust!B5="X",Cust!B5="Y"),Cust!D24>Cust!C24),Cust!C24,Cust!D24)

IF(AND(OR(Cust!B5="X",Cust!B5="Y"),Cust!D26>Cust!C26),Cust!C26,Cust!D26)

When combined, I get an error
IF((AND(OR(Cust!B5="X",Cust!B5="Y"),Cust!D24>Cust!C24),Cust!C24,Cust!D24),IF(AND(OR(Cust!B5="X",Cust!B5="Y"),Cust!D26>Cust!C26),Cust!C26,Cust!D26))

Any assistance appreciated! Thanks-

 

[T. Valko]

You have a logic problem in the AND functions:

Cust!D24>Cust!C24
Cust!D26>Cust!C26

Those will *never* be TRUE.

Also, you have a problem with the IF functions. If the AND function is TRUE
(which it will never be) you want to return the value of Cust!C24 and if the
AND function is FALSE you still want to return the value of Cust!C24.

Try explaining in words what you want to do.

 

[Shane Devenshire]

Hi,

You have four possible outcomes but you haven't explained how you want to
handle them.

D24>C24 and D26>C26 (both are true)
D24>C24 and D26 not >C26 (one is true)
D24 not > C24 and D26>C26 (the other is true)
D24 not > C24 and D26 not > C26 (both are false)

Give us the outcome of these.

 

[T. Valko]

Ooops!

I better get my eyes examined!

Cust!D24>Cust!C24
Cust!D26>Cust!C26
Those will *never* be TRUE.

I couldn't see that those are different cells. I thought they were D24>D24
and D26>D26.

OK, as is you can't combine them because each is a completely separate
formula. You already have defined a value if true argument and a value if
false argument for Cust!D24-Cust!C24 so that teminates the formula.

 

[Bob Bridges]

I don't know why T. Valko thinks D24 can never be greater than C24, nor D26
than C26; he may be seeing something I'm missing. And I think he's
misreading your return. The problem I see is in the way you've combined the
two IF formulae; you seem to have them one after the other instead of one
inside the other. I agree with his suggestion, though: Can you explain in
words what you're trying to accomplish by combining the two IFs? Either one
of them alone looks like to me.

And by the way, WHAT error do you get? It makes a difference.

IF(
(
AND(
OR(Cust!B5="X",Cust!B5="Y"),
Cust!D24>Cust!C24),
Cust!C24,Cust!D24),
IF(
AND(
OR(Cust!B5="X",Cust!B5="Y"),
Cust!D26>Cust!C26),
Cust!C26,Cust!D26))

 

[CJOHNSO92]

Hi all,
thanks for your responses. I have the following variables:
X and Y are 2 types of customers
Supplied (column C) and Requested (Column D) represent quantity

Both X and Y customer types can have values in supplied and requested.
Basically I'm calculating how many packages are provided free of charge. So
if X customer type and D24 (requested qty) > C24 (supplied qty), we only want
to provide C24.

When combined, I get "the formula you typed contains an error". Maybe
something with my parentheses?

Hopefully explanation makes more sense...thanks for your help!
CJ

 

[Rick Rothstein]

Does this do what you want (it assumes you only have X and Y types of
customers)?

=IF(Cust!B5="X",MIN(Cust!C24,Cust!D24),MIN(Cust!C26,Cust!D26))

If you have other types of customers besides X and Y, then you need to tell
us what you want to happen for them.

 

[CJOHNSO92]

Hi Rick,
I do have other customer types, but only one of them should be true in any
situation. I will try the MIN and run through testing.

Many thanks!
CJ

 

[Bob Bridges]

If you have multiple customer types, maybe you need a table, something like
this:

Col A Col B
Row 1 Type Row
Row 2 A 13
Row 3 M 21
Row 4 X 24
Row 5 Y 26

So back in your home sheet, somewhere in a helping column to the right (say
col G), you do a VLOOKUP:

=VLOOKUP(Cust!B5,CustType!A:B,2,0)

Now in that cell (say G4) you've transformed the customer type into a row
number, so instead of an IF statement you can do this:

=MIN(INDIRECT("Cust!C"&G4),INDIRECT("Cust!D"&G4))

This looks like more work, but it allows you more flexibility when you have
multiple customer types and especially when those types or the proper rows
might change from time to time.