Formula how to say "cannot be equal to"

Discuss the spreadsheet application

Formula how to say "cannot be equal to"

Postby Neko purr2 » Sat Nov 16, 2019 8:31 am

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
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?
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"

Postby Zizi64 » Sat Nov 16, 2019 12:32 pm

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.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
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.
User avatar
Zizi64
Volunteer
 
Posts: 8521
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby Villeroy » Sat Nov 16, 2019 1:41 pm

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.x
User avatar
Villeroy
Volunteer
 
Posts: 27373
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby MrProgrammer » Sat Nov 16, 2019 4:24 pm

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).
User avatar
MrProgrammer
Moderator
 
Posts: 3894
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

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

Postby Villeroy » Sat Nov 16, 2019 4:35 pm

=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.x
User avatar
Villeroy
Volunteer
 
Posts: 27373
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby Neko purr2 » Sat Nov 16, 2019 9:55 pm

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
(19.05 KiB) Downloaded 7 times
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"

Postby RusselB » Sat Nov 16, 2019 11:06 pm

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.
User avatar
RusselB
Moderator
 
Posts: 5667
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

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

Postby Neko purr2 » Sun Nov 17, 2019 5:43 am

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"

Postby keme » Mon Nov 18, 2019 12:00 pm

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))
User avatar
keme
Volunteer
 
Posts: 3284
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

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

Postby Neko purr2 » Tue Nov 19, 2019 3:42 am

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"

Postby Zizi64 » Tue Nov 19, 2019 8:25 am

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 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
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.
User avatar
Zizi64
Volunteer
 
Posts: 8521
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby Neko purr2 » Sat Dec 07, 2019 9:49 pm

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
(21.16 KiB) Downloaded 5 times
OpenOffice 4.1.4 on Windows 10
Neko purr2
 
Posts: 5
Joined: Sat Nov 16, 2019 8:03 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 13 guests