[Solved] Nested conditionals with OR

[sashcramp]

Hi,
I'm having great difficulty nesting these conditionals. The first conditional works on its own but when I try to add the second I get Err 504 or 509. I am a beginner, so no doubt I'm making some elementary error. Can anyone point it out out to me, please ?

=IF(OR(C214={"Boots";"pocklington's bakery";"ebay"});"Paypal";"SOURCE?";IF(OR(C214={"amazon";"tesco"});"1476";"SOURCE?")) gives Err 504

=IF(OR(C214={"Boots";"pocklington's bakery";"ebay"});"Paypal";"SOURCE?");IF(OR(C214={"amazon";"tesco"});"1476";"SOURCE?") gives Err 509

=IF(OR(C214={"Boots";"pocklington's bakery";"ebay"});"Paypal";"SOURCE?" works OK

[RusselB]

Welcome to the Forums.
I've never seen the OR function used in this manner before, but I think I understand the logic of how it should be working.
The problem you are running into is not with the OR, but with the IF.
The IF has 3 parameters. The condition that has to match, the response if it does match (aka TRUE), and the response if it does not (aka FALSE)
In the sample that works, the 1st parameter is fulfilled by the OR function. The 2nd parameter is the term "Paypal", and the 3rd parameter is the term "SOURCE?"
There is no option in Calc for a 4th parameter, which is where you are having your problem.
A rewrite (untested) could look like

Code: Select all

=if(or(c214={Boots";"pockington's bakery";"ebay"});"Paypal";if(or(c214={amazon";"tesco"});"1476";"SOURCE?");"SOURCE?")

[Zizi64]

sashcramp,
please upload your ODF type sample file here.

[cwolan]

I've never seen the OR function used in this manner before, [...]

This manner is also new to me. What does it mean?

Code: Select all

C214={"Boots";"pocklington's bakery";"ebay"}

Is this to check whether cell C214 contains a value from the set {...} ?
If so, it won't not give the expected result. Cf. the screenshot.

[Zizi64]

Please upload a real sample spreadsheet document here.

[cwolan]

Here you go, though it is a rudimentary test.
The OP should give his/her view and provide the file you've asked for.

[robleyd]

A read of the offline help on array formulas might be entertaining

[Zizi64]

Here is an another tip:

testFILE_Zizi64.ods

(9.57 KiB) Downloaded 124 times

[cwolan]

A read of the offline help on array formulas might be entertaining

And a read of the online help on array formulas might be entertaining too.

But joking aside, I just asked a question to be on the safe side.
I remember a post on some forum (long ago) — after analysis, it turned out that the OP "misused" {}.

[sashcramp]

Please upload a real sample spreadsheet document here.

Thanks. Here is the spreadsheet sample.

My original formula was just a number of nested conditionals that returned the appropriate result in col B for col A. Using this, I must add a whole new conditional each time a new term crops up in A. I was trying the array formula hoping to type in only the new term.

[sashcramp]

I've never seen the OR function used in this manner before, [...]

This manner is also new to me. What does it mean?

Code: Select all

C214={"Boots";"pocklington's bakery";"ebay"}

Is this to check whether cell C214 contains a value from the set {...} ?
If so, it won't not give the expected result. Cf. the screenshot.

Yes, I think you're right, there. Even though I thought it was working, I tested it further and it wasn't ! What I'm trying to achieve is "If the cell contains A, B or C then print "Paypal", if the cell contains X,Y or Z then print "1476" else print "SOURCE?".

When a term other than A B C X Y or Z crops up in the cell, I'd like to be able to add just that term to the formula, rather than type a whole nested conditional, as I was previously doing.

[robleyd]

Have you considered a lookup table?

[sashcramp]

Welcome to the Forums.
I've never seen the OR function used in this manner before, but I think I understand the logic of how it should be working.
The problem you are running into is not with the OR, but with the IF.
The IF has 3 parameters. The condition that has to match, the response if it does match (aka TRUE), and the response if it does not (aka FALSE)
In the sample that works, the 1st parameter is fulfilled by the OR function. The 2nd parameter is the term "Paypal", and the 3rd parameter is the term "SOURCE?"
There is no option in Calc for a 4th parameter, which is where you are having your problem.
A rewrite (untested) could look like

Code: Select all

=if(or(c214={Boots";"pockington's bakery";"ebay"});"Paypal";if(or(c214={amazon";"tesco"});"1476";"SOURCE?");"SOURCE?")

Thanks. I suspect I'm also making a mistake trying to use OR and an array formula to achieve what I want.

[sashcramp]

Have you considered a lookup table?

Thanks for the suggestion but unfortunately I can't sort the searchtable in my particular application.

[sashcramp]

A read of the offline help on array formulas might be entertaining

And a read of the online help on array formulas might be entertaining too.

But joking aside, I just asked a question to be on the safe side.
I remember a post on some forum (long ago) — after analysis, it turned out that the OP "misused" {}.

I'm beginning to empathise with that OP I'm probably barking up the wrong tree by trying to use OR with array formulas, anyway. Thanks for the sample sheet, by the way.

[keme]

I'm probably barking up the wrong tree by trying to use OR with array formulas, anyway.

Surprisingly (to me, at least), the use of OR() with array input changes the evaluation mode into array context just fine. It is not wrong. You just need to meticulously plan the evaluation.

The error lies in formula structure. I will elaborate on RussellB's answer, which is essentially correct, baring a small bug.

Structure of the IF function is:
=IF(<expression to be evaluated for zero (FALSE) or nonzero (TRUE)> ; <expression to be returned for TRUE> ; <expression to be returned for FALSE>)

Applying the color coding to your formula:
=IF(OR(C214={"Boots";"pocklington's bakery";"ebay"});"Paypal";"SOURCE?";IF(OR(C214={"amazon";"tesco"});"1476";"SOURCE?"))
The black part at the end constitutes the fourth parameter which RussellB mentions, and which is totally irrelevant to IF syntax and therefore unsupported, thus returning an error.

With your small data set it can be converted as per RussellB's strategy. Alas, he accidentally introduced another fourth parameter, or I may say "inherited" from your other formula suggestion. This slight rephrase should work:
=IF(OR(C214={"Boots";"pocklington's bakery";"ebay"});"Paypal";IF(OR(C214={"amazon";"tesco"});"1476";"SOURCE?"))
The inner IF() constitutes the third parameter to the outer IF().

That said, with larger data sets and occasional changes to payment, inline arrays easily become unmanageable. Also, inline arrays can only contain constant values, no references or calculations, which is often a significant limitation. Use a lookup function instead.

Thanks for the suggestion but unfortunately I can't sort the searchtable in my particular application.

You need exact matches for this, which requires the use of VLOOKUP(), HLOOKUP() or MATCH() with the "sorted" parameter set to false/0. Thus, your table does not have to be sorted.

[Villeroy]

With cells instead of inline arrays:
=IF(ISNUMBER(MATCH(C214 ; $X$1:$X$99 ; 0)) ; "Paypal" ; IF(ISNUMBER(MATCH(C214 ; $Y$1:$Y$99 ; 0)) ; "1476" ; "SOURCE?"))
where $X$1:$X$99 and $Y$1:$Y$99 are arbitrary columns with the criteria replacing the inline array. MATCH can deal with columns and rows as well.

[sashcramp]

I'm probably barking up the wrong tree by trying to use OR with array formulas, anyway.

Surprisingly (to me, at least), the use of OR() with array input changes the evaluation mode into array context just fine. It is not wrong. You just need to meticulously plan the evaluation.

The error lies in formula structure. I will elaborate on RussellB's answer, which is essentially correct, baring a small bug.

Structure of the IF function is:
=IF(<expression to be evaluated for zero (FALSE) or nonzero (TRUE)> ; <expression to be returned for TRUE> ; <expression to be returned for FALSE>)

Applying the color coding to your formula:
=IF(OR(C214={"Boots";"pocklington's bakery";"ebay"});"Paypal";"SOURCE?";IF(OR(C214={"amazon";"tesco"});"1476";"SOURCE?"))
The black part at the end constitutes the fourth parameter which RussellB mentions, and which is totally irrelevant to IF syntax and therefore unsupported, thus returning an error.



With your small data set it can be converted as per RussellB's strategy. Alas, he accidentally introduced another fourth parameter, or I may say "inherited" from your other formula suggestion. This slight rephrase should work:
=IF(OR(C214={"Boots";"pocklington's bakery";"ebay"});"Paypal";IF(OR(C214={"amazon";"tesco"});"1476";"SOURCE?"))
The inner IF() constitutes the third parameter to the outer IF().

That said, with larger data sets and occasional changes to payment, inline arrays easily become unmanageable. Also, inline arrays can only contain constant values, no references or calculations, which is often a significant limitation. Use a lookup function instead.

Thanks for the suggestion but unfortunately I can't sort the searchtable in my particular application.

You need exact matches for this, which requires the use of VLOOKUP(), HLOOKUP() or MATCH() with the "sorted" parameter set to false/0. Thus, your table does not have to be sorted.

Many thanks! I couldn't detect the bug but I see it now. This works well but, as you say, I'm using a small dataset and I can't add a third payment type due to the limit of 3 parameters. I'll read up on the lookup function. Thanks again.

[sashcramp]

With cells instead of inline arrays:
=IF(ISNUMBER(MATCH(C214 ; $X$1:$X$99 ; 0)) ; "Paypal" ; IF(ISNUMBER(MATCH(C214 ; $Y$1:$Y$99 ; 0)) ; "1476" ; "SOURCE?"))
where $X$1:$X$99 and $Y$1:$Y$99 are arbitrary columns with the criteria replacing the inline array. MATCH can deal with columns and rows as well.

That's interesting, thanks, it would save me altering the formula each time there's a new term in col C !

[Zizi64]

it would save me altering the formula each time there's a new term in col C !

Use a Named Range, and use the name in the formula.

[sashcramp]

it would save me altering the formula each time there's a new term in col C !

Use a Named Range, and use the name in the formula.

Thanks.

[keme]

...
I'll read up on the lookup function. Thanks again.

Please note the difference between "lookup functions" and "the lookup function":

• The LOOKUP() function does require a sorted search range. This is the original spreadsheet lookup function.
• The lookup functions VLOOKUP(), HLOOKUP() and MATCH() accept the "sorted" parameter which doubles as a signal for "require exact match", and will work with unsorted search range.

[sashcramp]

...
I'll read up on the lookup function. Thanks again.

Please note the difference between "lookup functions" and "the lookup function":

• The LOOKUP() function does require a sorted search range. This is the original spreadsheet lookup function.
• The lookup functions VLOOKUP(), HLOOKUP() and MATCH() accept the "sorted" parameter which doubles as a signal for "require exact match", and will work with unsorted search range.

OK, thank you.

[Villeroy]

Use a Named Range, and use the name in the formula.

This does not solve the probem unless you constantly maintain the named reference. It saves some time and effort because you can maintain the name in one place while using it in many places.

Instead of maintaining references, you expand all references (named and unnamed ones) by cell insertion.
Tools>Options>Calc>General> "Expand references = ON". WIth this option checked the trick works when you insert cells directly below the list.

The LOOKUP() function does require a sorted search range. This is the original spreadsheet lookup function.

LOOKUP is the wrong function anyway because it returns false positives.
=LOOKUP(3;{0;2;4;6}) returns 2 although the searched number 3 is not in the list. Things become totally confusing with text.