Combining Multiple IF Statements

Funkymonkey0073

New Member
Joined
Dec 29, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. Web
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
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,432
Office Version
  1. 365
Platform
  1. Windows
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
Excel Formula:
=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.
 
Solution

Funkymonkey0073

New Member
Joined
Dec 29, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. Web
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
Excel Formula:
=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

Well-known Member
Joined
Dec 30, 2008
Messages
12,432
Office Version
  1. 365
Platform
  1. Windows
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.
Excel Formula:
=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

New Member
Joined
Dec 29, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. Web
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.
Excel Formula:
=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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,333
Messages
5,624,066
Members
416,010
Latest member
NJT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top