[Solved] Value selected from IF() won't work

Discuss the spreadsheet application

[Solved] Value selected from IF() won't work

Postby gahelton1 » Mon Jan 21, 2019 7:31 am

The following statement gets one of four numeric values (0-3) from List of cell selections.

CELL D7 contains a List of values "CW", "OOK", "FSK", "GFSK" . FSK was selected for this example.

FORMULA FOR CELL E7 =IF(D7="CW";0;"")&IF(D7="OOK";1;"") & IF(D7="FSK";2;"")&IF(D7="GFSK";3;"")
VALUE IN CELL E7: 2 (when FSK was selected). Cell E7 is Category "Number"

VALUE IN CELL F7: 0

FORMULA IN CELL G7: =E7*POWER(2;F7)
The value in cell G7 should be 2, but it's 0

It seems like the formula for cell G7 is not picking up the value that was assigned by the IF statement in cell E7.

Why ?

Thanks.
OpenOffice 3.1 on Windows 7 Pro SP1
gahelton1
 
Posts: 2
Joined: Mon Jan 21, 2019 7:02 am

Re: Value selected from IF statement won't work for other ca

Postby robleyd » Mon Jan 21, 2019 9:03 am

I used your values and formulae above in a new Calc file and it gives the expected result of 2.

Can you upload a sample file that shows the problem? [Forum] How to attach a document here Note maximum file size is 128K. If your file is larger, use a file sharing site such as Mediafire and post the link here. The link also contains information on how to anonymise your document if it contains confidential information.
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3049
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Value selected from IF statement won't work for other ca

Postby keme » Mon Jan 21, 2019 10:13 am

Actually, your first formula concatenates all the IF() results by the & operator. Concatenation is a text operation, so the result in cell E7 is not the number 2, but rather the character "2".

With "strict type handling", text does not have a numerical value, and attempts to calculate from "text as numbers" will invariably return errors.
Calc (and other spreadsheets) have rather loose type handling, which appears "friendlier" but sometimes gives unexpected ("wrong") results.

Loose type handling goes like this: For spreadsheet formulas finding a text where a number is expected, the software first attempts an unambiguous numerical interpretation (newer versions of Calc, Excel). If numeric interpretation is ambiguous or impossible, either return numeric zero (older versions of Calc like in your case), or an error (as it should return, IMHO).

There are two sensible ways to deal with this. Either one should solve the issue at hand, but I suggest you do both:
  • Upgrade to a current version of Calc. (v.3.1 is rather old, and has a few flaws)
  • Alter your E7 formula so it returns a number instead of text.
Formula suggestions (either one should do):
Code: Select all   Expand viewCollapse view
=IF(D7="CW";0;IF(D7="OOK";1; IF(D7="FSK";2;IF(D7="GFSK";3;""))))
=MATCH(D7;{"CW";"OOK";"FSK";"GFSK"};0)-1

The first is a "nested if", which evaluates the next condition only if the previous returned "false".
The second uses list matching, which returns the sequence number of a matching entry in the list.
User avatar
keme
Volunteer
 
Posts: 3271
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Value selected from IF statement won't work for other ca

Postby gahelton1 » Mon Jan 21, 2019 4:36 pm

Thank you for the replies.

I like the simplicity of the MATCH function. This is what I will in this spreadsheet for now. However there a great number of times where the return of a sequential numeric value is not appropriate. For a more complex system of return values, the IF statement is more appropriate. Is it true that the nesting level of nested IF's is only 20 ? It seems that I read this somewhere. Also, the nested IF syntax is not very "clean", and it's easier to make a mistake for a long formula.

Also, I understand that the ampersand symbol is a concatenation symbol for text. However, it seems like it would only concatenate the textual portion of formula(s). The actual value assignment within each IF statement assigned numeric values (such as 0,1,2,3), not a text values (such as "0", "1", "2", or "3"). Maybe this has been fixed in a newer version since someone else who replied said that my formula worked for them.

Thanks again.
OpenOffice 3.1 on Windows 7 Pro SP1
gahelton1
 
Posts: 2
Joined: Mon Jan 21, 2019 7:02 am

Re: Value selected from IF statement won't work for other ca

Postby MrProgrammer » Mon Jan 21, 2019 5:23 pm

Hi, and welcome to the forum.

gahelton1 wrote:However there a great number of times where the return of a sequential numeric value is not appropriate.
[Tutorial] VLOOKUP questions and answers, especially Q20/A20 there, but you should read the entire tutorial.

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: 3875
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA


Return to Calc

Who is online

Users browsing this forum: No registered users and 23 guests