## Formula how to say "cannot be equal to"

Discuss the spreadsheet application

### Formula how to say "cannot be equal to"

So I have a spreadsheet that generates a random area of interest and three traits for a fictional character. The traits are chosen from a list based on what their area of interest is. There are 12 possible areas of interest. The problem is that all three traits are chosen from the same list, so sometimes I get duplicates, which I don't want. Is there any way to say "choose a word from this list that is NOT equal to (previous selection)"?

Formula:
Code: Select all   Expand viewCollapse view

I have already tried doing this (simplified formula):

IF(((IF(E\$12="Politics";INDEX(Traits.\$D\$2:\$D\$103;RANDBETWEEN(2;COUNTA(Traits.\$D\$2:\$D\$103)))))<>(E14));((IF(E\$12="Politics";INDEX(Traits.\$D\$2:\$D\$103;RANDBETWEEN(2;COUNTA(Traits.\$D\$2:\$D\$103)))))))

(aka: if/then). It worked on the simplified formula, but the full formula seems to have overmaxed on characters, and when I hit enter a large portion of the equation was cut off. Is there a way to make this work?
OpenOffice 4.1.4 on Windows 10
Neko purr2

Posts: 5
Joined: Sat Nov 16, 2019 8:03 am

### Re: formula how to say "cannot be equal to"

Depended on the data structure, maybe it is better to use a VLOOKUP() and/or a HLOOKUP() function instead of the nested IF-s.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.3;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.

Zizi64
Volunteer

Posts: 9180
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

### Re: formula how to say "cannot be equal to"

http://forum.openoffice.org/en/forum/vi ... 280#p65280 includes an example file with 2D lookups.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 28153
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: formula how to say "cannot be equal to"

Hi, and welcome to the forum.

Neko purr2 wrote:I have a spreadsheet that generates a random area of interest and three traits [and] sometimes I get duplicates, which I don't want.
[Tutorial] Randomization in Calc section C: Creating uniformly-distributed unique random integers between specified minimum and maximum values

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).

MrProgrammer
Moderator

Posts: 3964
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

### Re: formula how to say "cannot be equal to"

=INDEX(\$Traits.\$A\$2:\$Traits.\$L\$146;RANDBETWEEN(1;ROWS(\$Traits.\$A\$2:\$Traits.\$L\$146));MATCH(D12;\$Traits.\$A\$1:\$Traits.\$L\$1;0))
does not solve the uniqueness problem, but it eliminates all the IFs with range A1:L1 being:
Adventure Cooking/Baking Crafts-work Politics RecordUpkeep Science Business Medicine Other Combat Art Volunteer
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 28153
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: formula how to say "cannot be equal to"

Villeroy, the end of your formula is cut off. My best guess it that it was this:
Code: Select all   Expand viewCollapse view
=INDEX((\$Traits.\$A\$2:\$Traits.\$L\$159;RANDBETWEEN(1;ROWS(Traits.\$A\$2:\$Traits.\$L\$159)));MATCH(D\$12;Traits.\$A\$1:\$L\$1;0))

But when I typed this in, it changed the semicolon before randbetween to this: ~ and gave me an error message.
Attachments
Traits Gen. sample.ods
OpenOffice 4.1.4 on Windows 10
Neko purr2

Posts: 5
Joined: Sat Nov 16, 2019 8:03 am

### Re: formula how to say "cannot be equal to"

Look at your keyboard for a key marked Alt-Gr.
If you have one, that change is an indication that that key is stuck down.
If you don't have one, then (according to a Google search) it's the one on the right hand side of the keyboard.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 5973
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

### Re: Formula how to say "cannot be equal to"

RusselB wrote: that change is an indication that that key is stuck down.

I don't think it's a problem with the key being stuck down. When I typed a different randbetween equation it was fine, and all my other equations are fine, it's just that one that always changes the semicolon. Maybe calc just doesn't like that equation?
OpenOffice 4.1.4 on Windows 10
Neko purr2

Posts: 5
Joined: Sat Nov 16, 2019 8:03 am

### Re: Formula how to say "cannot be equal to"

Too many parentheses. Remove one level (take away what is marked in red):
=INDEX((\$Traits.\$A\$2:\$Traits.\$L\$159;RANDBETWEEN(1;ROWS(Traits.\$A\$2:\$Traits.\$L\$159)));MATCH(D\$12;Traits.\$A\$1:\$L\$1;0))

keme
Volunteer

Posts: 3333
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

### Re: Formula how to say "cannot be equal to"

So the formula does generate results now, but some of the values it generates are blanks. I tried an hlookup formula too, with the exact same results. Is there a way to make it only select boxes with defined values? Or a formula I could put in the blank boxes that prevents them from being randomly selected?

Hlookup formula is this:
Code: Select all   Expand viewCollapse view
=HLOOKUP(D\$12;Traits.\$A\$1:\$L\$146;RANDBETWEEN(2;146);0)
OpenOffice 4.1.4 on Windows 10
Neko purr2

Posts: 5
Joined: Sat Nov 16, 2019 8:03 am

### Re: Formula how to say "cannot be equal to"

Use helper cells to count the available string items (use the COUNTA() function)
Then get the relevant helper cell in your formula by xLOOKUP() (x = "H" or "V"), and use the value of the actual helper cell as a dynamic variable in the part RANDBETWEEN() part of your formula. Then the RANDBETWEEN() will generate a random number inside the existing data range.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.3;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.

Zizi64
Volunteer

Posts: 9180
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

### Re: Formula how to say "cannot be equal to"

I tried using your helper cell idea in two different ways. I set up a table in the traits sheet where the top column is the areas of interest, the second column is the data range for that area, the third is the first part of that range, and the fourth cell has the last value of the range.
Solution one: in a helper cell, use hlookup to find the data range corresponding to the generated area of interest. (Say the helper cell is A14; the value is Traits.A2:A113). The equation in the main cell is =index(A14;randbetween(2;counta(A14))). This gave me an error, though.
Solution two: In two helper cells, use hlookup to find in the first the top value of the corresponding data range (say A2 in cell B14), and to put in the second the last value in the corresponding data range (A113 in cell B15). The equation in the main cell is =index(Traits.B14:B15;randbetween(2;counta(Traits.B14:B15))). This always generates the same result.
Neither solution stops repeats, but they are way shorter than my nested if statements equation. Is there a way to make one of these work? Updated version of the sample sheet below.
Attachments
Traits Gen. sample.ods