[Solved] Trouble with a long if string

Discuss the spreadsheet application

[Solved] Trouble with a long if string

Postby blairapd » Fri Jul 17, 2020 7:10 am

EDIT: SOLVED

Hello.

I am using randbetween to generate a number that is meant to produce a text output.

randbetween produces the number, I reference it in the if string, and no matter the value in the randbetween cell, I come back with the output for the lowest numeriacal value.

the string is here:

Code: Select all   Expand viewCollapse view
=IF(C2>96,"AM",IF(97>C2>80,"INC",IF(81>C2>60,"REM",IF(61>C2>40,"EX",IF(41>C2>20,"GD",IF(21>C2>10,"TYP",IF(11>C2>5,"PR",IF(6>C2>1,"FE","WTF"))))))))


Without fail, I get "WTF". What am i doing wrong?

EDIT: SOLVED
Last edited by robleyd on Mon Jul 20, 2020 2:28 am, edited 3 times in total.
Reason: Tagged [Solved]
Open Office 6.0.7.3 on Linux Mint 19.3
blairapd
 
Posts: 4
Joined: Fri Jul 17, 2020 5:21 am

Re: Trouble with a long if string

Postby Zizi64 » Fri Jul 17, 2020 7:58 am

Please upload a real, ODF type sample file here.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;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.
User avatar
Zizi64
Volunteer
 
Posts: 9725
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Trouble with a long if string

Postby blairapd » Fri Jul 17, 2020 9:31 am

Will do. Look at sheet 3 for the strings. They are intended to reference the random numbers on sheet 1. file:///home/andrew/Documents/Boss%20Builder.ods
Attachments
Boss Builder.ods
(26.93 KiB) Downloaded 35 times
Open Office 6.0.7.3 on Linux Mint 19.3
blairapd
 
Posts: 4
Joined: Fri Jul 17, 2020 5:21 am

Re: Trouble with a long if string

Postby S0S » Fri Jul 17, 2020 9:54 am

I don't believe this is valid: 97>C2>80
Perhaps try: AND(97>C2;C2>80)
OpenOffice 4.17 on Windows 10
S0S
 
Posts: 2
Joined: Fri Jul 17, 2020 3:06 am

Re: Trouble with a long if string

Postby blairapd » Fri Jul 17, 2020 9:59 am

will do.
Open Office 6.0.7.3 on Linux Mint 19.3
blairapd
 
Posts: 4
Joined: Fri Jul 17, 2020 5:21 am

Re: Trouble with a long if string

Postby RoryOF » Fri Jul 17, 2020 10:14 am

consider: 97>c2 evaluates to True or False. Then True > 80 or False >80 always evaluates to False.

 Edit: Also: consider what is to happen on the boundary conditions. when C2 = 80, or C2 = 97? 
Apache OpenOffice 4.1.9 on Xubuntu 20.04.1 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 32194
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Trouble with a long if string

Postby JohnSUN-Pensioner » Fri Jul 17, 2020 10:28 am

Just try
Code: Select all   Expand viewCollapse view
=LOOKUP(Sheet2.B2; {0|5|10|20|40|60|80|96}; {"FE"|"PR"|"TYP"|"GD"|"EX"|"REM"|"INC"|"AM"})
=LOOKUP(C2;  {0|5|10|20|40|60|80|96}; {"FE"|"PR"|"TYP"|"GD"|"EX"|"REM"|"INC"|"AM"})
etc.
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 828
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Trouble with a long if string

Postby robleyd » Fri Jul 17, 2020 10:42 am

Another simplified version of your formula:
Code: Select all   Expand viewCollapse view
=IF(C2>96,"AM",
IFC2>80,"INC",
IF(C2>60,"REM",
IF(C2>40,"EX",
IF(C2>20,"GD",
IF(C2>10,"TYP",
IF(C2>5,"PR",
IF(C2>1,"FE","WTF"
))))))))


I note your signature says Open Office 6.0.7.3; latest version of AOO is 4.1.7 - perhaps you mean LibreOffice?
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.4.6.2 (SlackBuild for 6.4.6 by Eric Hameleers) - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3759
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Trouble with a long if string

Postby blairapd » Mon Jul 20, 2020 1:34 am

Thanks gentlemen. Fixed. The AND statements worked.
Open Office 6.0.7.3 on Linux Mint 19.3
blairapd
 
Posts: 4
Joined: Fri Jul 17, 2020 5:21 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 24 guests