Combining Multiple IF Statements

[Funkymonkey0073]

Hi thanks for looking

I need help combining 2 multiple IF statements, see below. I used them in separate cells previously but now I have the need to combine them and thought I could just string them together but I am getting TRUE and blank results when they shouldn't be. Any help would be appreciated.

=IF(AND(B49="ivory coast",OR(E5="Customer 1",E5="Customer 2"),K50<56),"out of spec",IF(AND(B49="ivory coast",OR(E5="Customer 3",E5="Customer 4",E5="Customer 5"),K50<57.5),"out of spec",""))
=IF(AND(B49="Ghana",OR(E5="Customer 1"),K50<58.5),"out of spec",IF(AND(B49="Ghana",OR(E5="Customer 2",E5="Customer 3",E5="Customer 4",E5="Customer 5"),K50<57.5),"out of spec",""))

Stuart

 

[jasonb75]

There are several ways of doing what you want, joining them together would mean replacing the final FALSE result of "" in the first formula with the entire second formula (apart from the = symbol at the start).

Or you could use a few different functions and try something like this much shorter formula instead

=IF(OR(AND(B49="Ivory Coast",K5<LOOKUP(--RIGHT(E5,1),{1,3},{56,57.5})),AND(B49="Ghana",K5<LOOKUP(--RIGHT(E5,1),{1,2},{58.5,57.5}))),"out of spec","")

If the formula is going to become more complex by adding more countries and values for K50 then a using a lookup table would be preferable to having all of the information hard coded into the formula.

 

[Funkymonkey0073]

There are several ways of doing what you want, joining them together would mean replacing the final FALSE result of "" in the first formula with the entire second formula (apart from the = symbol at the start).

Or you could use a few different functions and try something like this much shorter formula instead

=IF(OR(AND(B49="Ivory Coast",K5<LOOKUP(--RIGHT(E5,1),{1,3},{56,57.5})),AND(B49="Ghana",K5<LOOKUP(--RIGHT(E5,1),{1,2},{58.5,57.5}))),"out of spec","")

If the formula is going to become more complex by adding more countries and values for K50 then a using a lookup table would be preferable to having all of the information hard coded into the formula.

Thank you so much, a great answer. The first solution worked, I tried the second but getting a #Value! error (accounting for changing the cell ref to K50 from K5). I may have the need to add another country at some point but that's it, the customers may also go up by 2 or 3, so as you say it could get a little long but not unwieldy, but I note you point about the lookup table, I considered that and may do that on another version, this is the last piece of this particular puzzle so just glad to have a working formula.

Thanks again, very much appreciated.

Stuart

 

[jasonb75]

Looking at reasons why the second formula didn't work (other than the reference to K5 instead of K50), does your actual formula use "Customer 1", "Customer 2" etc, or is that just a fictional version used for the purpose of the question?

The second formula is taking the number at the end of "Customer 1", "Customer 2" and using it to calculate the result. A #VALUE! error suggests that there is no number in the actual data.

I had wondered if the second formula might be a little too assumptive but went with it any way on the off chance. If I'm correct in assuming that your actual formula contains names or account numbers rather than the examples provided then a lookup table would be the only practical way to simplify the formula.

You can shorten an IF formula a bit by changing the order of things to prevent the need for repeating the same test multiple times, although sometimes the logic can become confusing if you're not entirely familiar with the principles. I've added a formula below as an example but In this case I think that it is too complex to be practical.

=IF(OR(AND(B49="ivory coast",OR(AND(OR(E5={"Customer 1","Customer 2"}),K50<56),AND(OR(E5={"Customer 3","Customer 4","Customer 5"}),K50<57.5))),AND(B49="Ghana",OR(AND(E5="Customer 1",K50<58.5),AND(OR(E5={"Customer 2","Customer 3","Customer 4","Customer 5"}),K50<57.5)))),"out of spec","")

 

[Funkymonkey0073]

Looking at reasons why the second formula didn't work (other than the reference to K5 instead of K50), does your actual formula use "Customer 1", "Customer 2" etc, or is that just a fictional version used for the purpose of the question?

The second formula is taking the number at the end of "Customer 1", "Customer 2" and using it to calculate the result. A #VALUE! error suggests that there is no number in the actual data.

I had wondered if the second formula might be a little too assumptive but went with it any way on the off chance. If I'm correct in assuming that your actual formula contains names or account numbers rather than the examples provided then a lookup table would be the only practical way to simplify the formula.

You can shorten an IF formula a bit by changing the order of things to prevent the need for repeating the same test multiple times, although sometimes the logic can become confusing if you're not entirely familiar with the principles. I've added a formula below as an example but In this case I think that it is too complex to be practical.

=IF(OR(AND(B49="ivory coast",OR(AND(OR(E5={"Customer 1","Customer 2"}),K50<56),AND(OR(E5={"Customer 3","Customer 4","Customer 5"}),K50<57.5))),AND(B49="Ghana",OR(AND(E5="Customer 1",K50<58.5),AND(OR(E5={"Customer 2","Customer 3","Customer 4","Customer 5"}),K50<57.5)))),"out of spec","")[/CODEY
[/QUOTE]

You are correct, they are fictional names, they are actually just the name of the company (text).

I tried several ways of doing this myself and as you say the logic is confusing and no doubt where I went wrong.

I will go with a lookup table in version 2, but as mentioned it's the last piece of what's been a long arduous puzzle and need to move on to something else, I'm just happy it's working.

Many thanks for your help, and your explanations, it really is appreciated.

Stuart