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

[gahelton1]

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.

[robleyd]

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.

[keme]

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

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

[gahelton1]

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.