Nested IF(AND statement

[bchager]

Hello all...

I'm trying to write a nested IF(AND statement. Below is as far as I've gotten:

=IF(AND(Order_Qty>1,Order_Qty<1000),Fixed_Order_Cost_Less_Than_1k*Order_Qty,0)

When I try to add a second or third nest, I receive an error. Below is an example of a formula that fails.

=IF(AND(Order_Qty>1,Order_Qty<1000),Fixed_Order_Cost_Less_Than_1k*Order_Qty,
IF(AND(Order_Qty=1000,Order_Qty<1999,Fixed_Order_Cost_1k*Order_Qty,0))

Any ideas?

 

[dreid1011]

Hello all...

I'm trying to write a nested IF(AND statement. Below is as far as I've gotten:

=IF(AND(Order_Qty>1,Order_Qty<1000),Fixed_Order_Cost_Less_Than_1k*Order_Qty,0)

When I try to add a second or third nest, I receive an error. Below is an example of a formula that fails.

=IF(AND(Order_Qty>1,Order_Qty<1000),Fixed_Order_Cost_Less_Than_1k*Order_Qty,
IF(AND(Order_Qty=1000,Order_Qty<1999,Fixed_Order_Cost_1k*Order_Qty,0))

Any ideas?

It could be a number of things. Here is what I saw:

Missing ")" on the second AND() function. AND(Order_Qty=1000,Order_Qty<1999)

Should the operator be "=" here? AND(Order_Qty=1000,Order_Qty<1999 If it should be "=", then there is no need for the AND() and it will never be true if it is not 1000. Perhaps you meant ">".

 

[bchager]

It could be a number of things. Here is what I saw:

Missing ")" on the second AND() function. AND(Order_Qty=1000,Order_Qty<1999)

Should the operator be "=" here? AND(Order_Qty=1000,Order_Qty<1999 If it should be "=", then there is no need for the AND() and it will never be true if it is not 1000. Perhaps you meant ">".

Not sure about the end parenthesis. It doesn't seem to be fixing it. To your second point; I need to calculate the cost if the quantity is less than 1000, at least 1000, or at least 2000.

 

[dreid1011]

Did you try both together then?

=IF(AND(Order_Qty>1,Order_Qty<1000),Fixed_Order_Cost_Less_Than_1k*Order_Qty,
IF(AND(Order_Qty>=1000,Order_Qty<1999),Fixed_Order_Cost_1k*Order_Qty,0))

Try this one.

 

[bchager]

Did you try both together then?

=IF(AND(Order_Qty>1,Order_Qty<1000),Fixed_Order_Cost_Less_Than_1k*Order_Qty,
IF(AND(Order_Qty>=1000,Order_Qty<1999),Fixed_Order_Cost_1k*Order_Qty,0))

Try this one.

That worked. Thank you! So if I want to include the cost for a quantity of at least 3000, just add another nest and 3 end parentheses? I really appreciate your help here! FYI this is for a graduate level risk analytics course.

 

[dreid1011]

If you're going to go to at least 3000, would that not be two more AND()'s? One more to check for 2000-2999 and the next to check for 3000+ ? Otherwise, yes, one more AND() just like you did the first, and another end parentheses. Just make sure you close the parentheses on each additional AND() too.

 

[bchager]

Thank you so much!

 

[dreid1011]

Thank you so much!

You're welcome.