Formula how to say "cannot be equal to"

[Neko purr2]

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

IF(D$12="Politics";INDEX(Traits.$D$2:$D$103;RANDBETWEEN(2;COUNTA(Traits.$D$2:$D$103)));IF(D$12="Adventure/Strategy";INDEX(Traits.$A$2:$A$113;RANDBETWEEN(2;COUNTA(Traits.$A$2:$A$113)));IF(D$12="Cooking/Baking";INDEX(Traits.$B$2:$B$101;RANDBETWEEN(2;COUNTA(Traits.$B$2:$B$101)));IF(D$12="Art";INDEX(Traits.$K$2:$K$111;RANDBETWEEN(2;COUNTA(Traits.$K$2:$K$111)));IF(D$12="Volunteer";INDEX(Traits.$L$2:$L$146;RANDBETWEEN(2;COUNTA(Traits.$L$2:$L$146)));IF(D$12="Combat";INDEX(Traits.$J$2:$J$114;RANDBETWEEN(2;COUNTA(Traits.$J$2:$J$114)));IF(D$12="Medicine";INDEX(Traits.$H$2:$H$92;RANDBETWEEN(2;COUNTA(Traits.$H$2:$H$92)));IF(D$12="Business";INDEX(Traits.$G$2:$G$100;RANDBETWEEN(2;COUNTA(Traits.$G$2:$G$100)));IF(D$12="Science";INDEX(Traits.$F$2:$F$95;RANDBETWEEN(2;COUNTA(Traits.$F$2:$F$95)));IF(D$12="RecordUpkeep";INDEX(Traits.$E$2:$E$85;RANDBETWEEN(2;COUNTA(Traits.$E$2:$E$85)));IF(D$12="Other";INDEX(Traits.$I$2:$I$146;RANDBETWEEN(2;COUNTA(Traits.$I$2:$I$146)));IF(D$12="Crafts-work";INDEX(Traits.$C$2:$C$99;RANDBETWEEN(2;COUNTA(Traits.$C$2:$C$99)))))))))))))))

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?

[Zizi64]

Please upoload your ODF type sample file here.

Depended on the data structure, maybe it is better to use a VLOOKUP() and/or a HLOOKUP() function instead of the nested IF-s.

[Villeroy]

http://forum.openoffice.org/en/forum/vi ... 280#p65280 includes an example file with 2D lookups.

[MrProgrammer]

Hi, and welcome to the forum.

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

[Villeroy]

=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

[Neko purr2]

Villeroy, the end of your formula is cut off. My best guess it that it was this:

Code: Select all

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

[RusselB]

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.

[Neko purr2]

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?

[keme]

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))

[Neko purr2]

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

=HLOOKUP(D$12;Traits.$A$1:$L$146;RANDBETWEEN(2;146);0)

[Zizi64]

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.

[Neko purr2]

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.