# 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.

( 2018-07-06 21:00:29 +0100 )edit
1

Is there somewhere I can learn proper syntax?

( 2018-07-06 21:23:51 +0100 )edit

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.

( 2018-07-06 21:49:37 +0100 )edit

( 2018-07-06 21:50:12 +0100 )edit
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.

( 2018-07-06 22:07:54 +0100 )edit

=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.

( 2018-07-09 21:09:02 +0100 )edit

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.

( 2018-07-10 12:38:42 +0100 )edit