# Trying to create a coin toss

Basic idea is two binaries. Column A high or low 0(1-4) or 1(6-9). Column B even or odd. Column C choices for random. Column D random number from choices in Column C. Trying to get Column C to read one of 4 choices 1,3 or 2,4 or 7,9 or 6,8. Trying to get Column D to pick a choice in Column C.

=IF(AND(0=A2,0=B2),"1,3","") Formula A will work returns 1,3 or nothing

=IF(AND(0=A2,0=B2),"1,3",=IF(AND(0=A2,1=B2),"2,4")) Formula B does not work returns Err:510 expected 1,3 or 2,4 or FALSE

=IF(AND(0=A2,0=B2),"1,3"),=IF(AND(0=A2,1=B2),"2,4"),=IF(AND(1=A2,0=B2),"7,9"),=IF(AND(1=A2,1=B2),"6,8") Formula C does not work returns Err:509 expected 1,3 or 2,4 or 7,9 or 6,8

Example

1 A B C D

2 0 1 Formula C =RAND(C2)

3 1 1 6,8 6

4 0 1 2,4 4

5 0 0 1,3 3

6 1 0 7,9 7

7 1 0 7,9 9

edit retag close merge delete

Sort by » oldest newest most voted You have some = too many in the formula expressions (only the leading = must be present, everywhere else =IF(...) would be an equal operator), use =IF(AND(0=A2,0=B2),"1,3",IF(AND(0=A2,1=B2),"2,4")) and the second expression also closes all nested IF too early thus concatenates expressions where no parameters are possible, that probably should be =IF(AND(0=A2,0=B2),"1,3",IF(AND(0=A2,1=B2),"2,4",IF(AND(1=A2,0=B2),"7,9",IF(AND(1=A2,1=B2),"6,8"))))

more

1

This works excellent for both my purposes, and for syntax. I would never have thought of the extra =. I might have caught the early ends. Thanks for the education.

1

Is there somewhere I can learn proper syntax?

When you start entering the formula in the formula bar, e.g. type =IF(, there'll be a little pop up indicating the correct syntax and a description of what the function does.

1

There's for example the Calc Guide with a chapter on Formulas and Functions, also the online Help has some introductory sections starting with Calculating With Formulas. Using the Function Wizard (Ctrl+F2) clicking through function names and existing expressions helps with visualising the parameters. =IF(AND(0=A2,0=B2),"1,3",=IF(AND(0=A2,1=B2),"2,4")) Formula B does not work returns Err:510 expected 1,3 or 2,4 or FALSE

Drop the = inside the formula, unless you are comparing two values. You only need one = at the start to enter a formula. Also, if you need a conditional with multiple tests, you should use IFS:

=IFS(AND(0=A2,0=B2),"1,3",AND(0=A2,1=B2),"2,4",TRUE,FALSE)


=IF(AND(0=A2,0=B2),"1,3"),=IF(AND(0=A2,1=B2),"2,4"),=IF(AND(1=A2,0=B2),"7,9"),=IF(AND(1=A2,1=B2),"6,8") Formula C does not work returns Err:509 expected 1,3 or 2,4 or 7,9 or 6,8

Yup, I see what you're trying to do, but you can't just put several expressions in one formula and separate them with a comma. Commas are only for separating arguments to function calls. The correct version would be:

=IFS(AND(0=A2,0=B2),"1,3",AND(0=A2,1=B2),"2,4",AND(1=A2,0=B2),"7,9",AND(1=A2,1=B2),"6,8")


(If this works for you, please click on the ✓ to accept the answer.)

more

I am using Libreoffice 5.0.2.2.

I get #NAME?.

I don't see IFS in the function wizard.

Someone else can check if this works in more modern versions.

Apparently it was added in 5.2. Didn't know it was that new. Might be better to stick to @erAck's solution then, at least if you need other people to open your spreadsheet.