Can I combine IF/AND/OR statements?
 

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!D24Cust!C 24),Cust!C24,Cust!D24)

IF(AND(OR(Cust!B5="X",Cust!B5="Y"),Cust!D26Cust!C 26),Cust!C26,Cust!D26)

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

Any assistance appreciated! Thanks-

Can I combine IF/AND/OR statements?
 

You have a logic problem in the AND functions:

Cust!D24Cust!C24
Cust!D26Cust!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.

--
Biff
Microsoft Excel MVP


"CJOHNSO92" wrote in message
...
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!D24Cust!C 24),Cust!C24,Cust!D24)

IF(AND(OR(Cust!B5="X",Cust!B5="Y"),Cust!D26Cust!C 26),Cust!C26,Cust!D26)

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

Any assistance appreciated! Thanks-

Can I combine IF/AND/OR statements?
 

Hi,

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

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

Give us the outcome of these.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"CJOHNSO92" wrote:

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!D24Cust!C 24),Cust!C24,Cust!D24)

IF(AND(OR(Cust!B5="X",Cust!B5="Y"),Cust!D26Cust!C 26),Cust!C26,Cust!D26)

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

Any assistance appreciated! Thanks-

Can I combine IF/AND/OR statements?
 

Ooops!

I better get my eyes examined!

Cust!D24Cust!C24
Cust!D26Cust!C26
Those will *never* be TRUE.

I couldn't see that those are different cells. I thought they were D24D24
and D26D26.

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.


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
You have a logic problem in the AND functions:

Cust!D24Cust!C24
Cust!D26Cust!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.

--
Biff
Microsoft Excel MVP


"CJOHNSO92" wrote in message
...
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!D24Cust!C 24),Cust!C24,Cust!D24)

IF(AND(OR(Cust!B5="X",Cust!B5="Y"),Cust!D26Cust!C 26),Cust!C26,Cust!D26)

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

Any assistance appreciated! Thanks-

Can I combine IF/AND/OR statements?
 

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!D24Cust!C24),
Cust!C24,Cust!D24),
IF(
AND(
OR(Cust!B5="X",Cust!B5="Y"),
Cust!D26Cust!C26),
Cust!C26,Cust!D26))

--- "CJOHNSO92" wrote:
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!D24Cust!C 24),Cust!C24,Cust!D24)

IF(AND(OR(Cust!B5="X",Cust!B5="Y"),Cust!D26Cust!C 26),Cust!C26,Cust!D26)

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

"CJOHNSO92" wrote:

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!D24Cust!C 24),Cust!C24,Cust!D24)

IF(AND(OR(Cust!B5="X",Cust!B5="Y"),Cust!D26Cust!C 26),Cust!C26,Cust!D26)

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

Can I combine IF/AND/OR statements?
 

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

"Bob Bridges" wrote:

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!D24Cust!C24),
Cust!C24,Cust!D24),
IF(
AND(
OR(Cust!B5="X",Cust!B5="Y"),
Cust!D26Cust!C26),
Cust!C26,Cust!D26))

--- "CJOHNSO92" wrote:
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!D24Cust!C 24),Cust!C24,Cust!D24)

IF(AND(OR(Cust!B5="X",Cust!B5="Y"),Cust!D26Cust!C 26),Cust!C26,Cust!D26)

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

"CJOHNSO92" wrote:

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!D24Cust!C 24),Cust!C24,Cust!D24)

IF(AND(OR(Cust!B5="X",Cust!B5="Y"),Cust!D26Cust!C 26),Cust!C26,Cust!D26)

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

Can I combine IF/AND/OR statements?
 

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!C2 6,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.

--
Rick (MVP - Excel)


"CJOHNSO92" wrote in message
...
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

"Bob Bridges" wrote:

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!D24Cust!C24),
Cust!C24,Cust!D24),
IF(
AND(
OR(Cust!B5="X",Cust!B5="Y"),
Cust!D26Cust!C26),
Cust!C26,Cust!D26))

--- "CJOHNSO92" wrote:
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!D24Cust!C 24),Cust!C24,Cust!D24)

IF(AND(OR(Cust!B5="X",Cust!B5="Y"),Cust!D26Cust!C 26),Cust!C26,Cust!D26)

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

"CJOHNSO92" wrote:

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!D24Cust!C 24),Cust!C24,Cust!D24)

IF(AND(OR(Cust!B5="X",Cust!B5="Y"),Cust!D26Cust!C 26),Cust!C26,Cust!D26)

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

Can I combine IF/AND/OR statements?
 

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

"Rick Rothstein" wrote:

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!C2 6,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.

--
Rick (MVP - Excel)


"CJOHNSO92" wrote in message
...
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

"Bob Bridges" wrote:

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!D24Cust!C24),
Cust!C24,Cust!D24),
IF(
AND(
OR(Cust!B5="X",Cust!B5="Y"),
Cust!D26Cust!C26),
Cust!C26,Cust!D26))

--- "CJOHNSO92" wrote:
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!D24Cust!C 24),Cust!C24,Cust!D24)

IF(AND(OR(Cust!B5="X",Cust!B5="Y"),Cust!D26Cust!C 26),Cust!C26,Cust!D26)

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

"CJOHNSO92" wrote:

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!D24Cust!C 24),Cust!C24,Cust!D24)

IF(AND(OR(Cust!B5="X",Cust!B5="Y"),Cust!D26Cust!C 26),Cust!C26,Cust!D26)

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

Can I combine IF/AND/OR statements?
 

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.

--- "CJOHNSO92" wrote:
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.

--- "Rick Rothstein" wrote:
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!C2 6,Cust!D26))