Multiple IF, And , OR formula with <

[Funkymonkey0073]

Hi thanks for looking.

I need help with a formula which I believe should be a multiple IF, AND, OR statement but I can't get it to work, I keep getting #Value! errors or false TRUE or FALSE results. I've written the statement out below as I clearly have no clue how this formula should be structured or even if I am going about it the right way. I tried using a lookup table but that failed too, any help would be appreciated.

If B50="Ivory Coast" and E5="Customer 1" or "Customer 2" and K50<56.00 then "Out of spec" else Blank
If B50="Ivory Coast" and E5="Customer 3" or "Customer 4" or "Customer 5" and K50<57.50 then "Out of spec" else Blank

Stuart

[Sam Capricci]

are these supposed to be two different formulas in two different spots or all merged together in one formula?

[Funkymonkey0073]

Sorry, good point, all one formula.

[Sam Capricci]

maybe this?...
=IF(AND(B50="ivory coast",OR(E5="customer 1",E5="customer 2"),K50<56),"out of spec",IF(AND(B50="ivory coast",OR(E5="customer 3",E5="customer 4",E5="customer 5"),K50<57.5),"out of spec",""))
but I think that can be shortened, see if that works for you while I look at shortening it.

[Sam Capricci]

this looks like a shorter version of the same formula...
=IF(AND(B50="ivory coast",OR(E5="customer 1",E5="customer 2"),K50<56),"out of spec",IF(AND(B50="ivory coast",OR(E5="customer 3",E5="customer 4",E5="customer 5"),K50<57.5),"out of spec",""))

[Funkymonkey0073]

I think you have pasted the same formula again but no problem as it worked first time.

Many thanks for the help and the speed of reply, very much appreciated.

Stuart

[Sam Capricci]

you're right, this is what I worked on that was slightly shorter...
=IF(OR(AND(B50="ivory coast",OR(E5="customer 1",E5="customer 2"),K50<56),OR(AND(B50="ivory coast",OR(E5="customer 3",E5="customer 4",E5="customer 5"),K50<57.5))),"out of spec","")

Oh, and thank you for the rep!

[Funkymonkey0073]

Even better, many thanks again, reps the least you deserve

Stuart

[Funkymonkey0073]

Sorry to bother you again but something I didn't mention was that B49 could be one of 2 different origins, Ivory Coast or Ghana, as I thought I could just duplicate the formula and string it together, with Ivory Coast replaced with Ghana and a different < range but it displays a blank cell or TRUE rather than 'out of spec' now, any suggestions on how to combine them?

=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

[hrlngrv]

The site is acting up, so I can't use less than symbols.

I'd only interject that it'd be simpler to use a formula like

Please Login or Register  to view this content.

where that middle part in red would consider E5 and K50. No efficiency lost having multiple IF calls each returning "" if their 1st arguments evaluated FALSE. No useful purpose served testing whether B50 equals "Ivory Coast" twice.

That said, if there were a lot of countries and a lot of customers in each country, you'd be better off using a table for K50 threshold values, with country in column 1, customer ID in column 2, and threshold values in column 3. Name that table CCTTBL, sort it in ascending order 1st on column 1 then on column 2. Formulas using this will be long, but they won't need to be edited when the table changes.

Please Login or Register  to view this content.

[Funkymonkey0073]

Figured it out, I just needed to remove the last "")) and = from the previous formula.

Stuart

[Funkymonkey0073]

Thanks hrlngrv, great suggestion and thanks for the advice on how to implement. I will go with your recommendation on the next version, this is the final piece of this particular puzzle and I'm just glad to have a working formula for now.