[Solved] Radio button: value or text?

[pmralbuquerque]

Hi,

I'm still adapting a xls to open format. Next step was radio buttons.
Thanks to Villeroy's example, I just had to make small adjustments to make it work. Thank you very much Villeroy.

But the question is:
When I assign a value to a radio button in a group shouldn't it be placed in the cell as value?
To achieve the intended result, I had to convert the cell using VALUE. Is this really needed or am I doing something wrong as usual?
How can I eliminate the ' that appears in front of the value: '1 or '4 for example?

Regards.

[Charlie Young]

The RefValue property of a radio button is a string, but this shouldn't cause any problems.

Instead of your nested IFs in E1, you can do

Code: Select all

=LOOKUP(F2;{"1";"2";"3";"4"};{"casados";"solteiro, viúvo, divorciado ou separado judicialmente";"separado de fato";"unidos de fato"})

This would work fine with the VALUE

Code: Select all

=LOOKUP(VALUE(F2);{1;2;3;4};{"casados";"solteiro, viúvo, divorciado ou separado judicialmente";"separado de fato";"unidos de fato"})

[Villeroy]

You must not use the LOOKUP function in this context. It returns wrong positive matches. May be not in this case because your form controls limit the possible search valaues. But in thousands of other use cases LOOKUP simply does not do what you expect and you may not even notice the "wrong" result ("wrong" in respect to your expectations, not wrong in respect to the documented behaviour of the LOOKUP function in all spreadsheets of the past 3 decades).

Code: Select all

    =LOOKUP(VALUE(F2);{1;2;3;4};{"casados";"solteiro, viúvo, divorciado ou separado judicialmente";"separado de fato";"unidos de fato"})

Now put any of 9999 or =PI() or 1.5 into F2. The values don't exist in {1;2;3;4} but the function always returns a result for any input value >= 1

[Charlie Young]

Excellent points Villeroy.

From Calc help:

If LOOKUP cannot find the search criterion, it matches the largest value in the search vector that is less than or equal to the search criterion.

[Villeroy]

This is what LOOKUP and its cousins are designed to do:

Code: Select all

X1:Y6
0  F
11  E
37  D
53  C
70  B
87  A

Lookup grade from numeric scale of points:
=LOOKUP(A1 ; $X$1:$Y$6)
for any point value >=0 in A1.

30 years ago nobody did foresee that spreadsheets become that widely used and that people try to look up single list items like with databases.
The cousins of LOOKUP MATCH, VLOOKUP and HLOOKUP follow the same priciple of "lookup number from ordered scale" but they have an additional operation mode which is NOT the default mode: =MATCH(A1 ; $X$1:$X$6 ; 0) matches the first exact occurrance of A1 in X1:X6, no matter if X1:X6 is sorted or not.
WIth Calc things are even more twisted. You've got to turn regular expressions OFF but turn whole cell matching ON in Tools>Options>Calc>Calculate. Otherwise you may get false negatives or false positive matches for other reasons.

[Charlie Young]

I was thinking we could use MATCH with type=0 to provide a safety valve

Code: Select all

=IF(NOT(ISNA(MATCH(F2;{"1";"2";"3";"4"};0)));LOOKUP(F2;{"1";"2";"3";"4"};{"casados";"solteiro, viúvo, divorciado ou separado judicialmente";"separado de fato";"unidos de fato"});NA())

But that gets pretty ugly.

Another idea

Code: Select all

=INDEX({"casados";"solteiro, viúvo, divorciado ou separado judicialmente";"separado de fato";"unidos de fato"};1;MATCH(F2;{"1";"2";"3";"4"};0))

[Villeroy]

Well, in this particular case the search value in F2 will always be one of "1","2","3" or "4" because F2 is bound to a group of 4 option buttons. I only wanted to emphasize that LOOKUP can not be used to lookup single items by arbitrary cell values.

Or one f these:

Code: Select all

=HLOOKUP(VALUE(F2);{1;2;3;4|"casados";"solteiro, viúvo, divorciado ou separado judicialmente";"separado de fato";"unidos de fato"};2;0)

Code: Select all

=VLOOKUP(VALUE(F2);{1;"casados"|2;"solteiro, viúvo, divorciado ou separado judicialmente"|3;"separado de fato"|4;"unidos de fato"};2;0)

However, I prefer to have the lookup table in cells rather than hard coded arrays because one range of cells is easier to adjust than an array within (thousands of) formulas

[pmralbuquerque]

Well, thank you for another great lesson here!
But, as the main purpose is to adapt a xls to open format, I think that making the least changes possible will be easier for the xls's author to compare both.
Since he originally used the nested IFs, I'll keep them.

Anyway, my doubt is answered, and with several ways to do the same thing. Thank you!
I consider this post solved and mark it as such.

Regards.

[Villeroy]

Oh, I forgot to mention:

Code: Select all

=CHOOSE(VALUE(F2);"casados";"solteiro, viúvo, divorciado ou separado judicialmente";"separado de fato";"unidos de fato")

[pmralbuquerque]

Very simple method, a lot more simpler than nested IFs. But still using VALUE for conversion. That same cell is used in many other helpers, not only on this 'array'.

If you want, I can post the original URL so that anyone can study it. It's an IRS simulator, very thorough, very competent but, of course, only useful here in Portugal. Hopefully, I 'm hopping to have, someday, permission to post it as an AOO template.

My version is 'password cracked' until I can present the author with an acceptable open format version. Which is only missing some 'macro translations' that I'll try to do using alternate methods, since I don't know how to program macros and haven't had the time yet to start studying that.

But that is another story, most likely for a different post.

Many thanks and regards.

[Villeroy]

VALUE does not matter. It returns the number if F2 has a number and it converts to number if F2 has a convertible number.
But it works without the VALUE function as long the text value represents an integer number with digits only.

[pmralbuquerque]

Yes, but the reference value of the radio button is placed in F2 as '1, '2, '3 or '4. That single quote prevents it from being interpreted as a number. So VALUE is still needed.

[pmralbuquerque]

Forgot to mention on the previous post, the original file is posted on a completely free finance blog, and available for anyone to download absolutely free of any charge or even registration.
I think the password exists only to prevent unwanted changes.
No steeling here, in any sense.

[Villeroy]

Yes, but the reference value of the radio button is placed in F2 as '1, '2, '3 or '4. That single quote prevents it from being interpreted as a number. So VALUE is still needed.

No, it doesn't. The apostrophe is not part of the cell's text.

[pmralbuquerque]

You're absolutely right, it doesn't. It's being interpreted as a number now.
What did I change to do that? Here is something I'd like to know.

[Villeroy]

Enter any numeric expression or any formula with a leading apostrophe into a cell. The input will not be interpreted by any means. Instead the input is treated as literal text input. The leading apostrophe tells the spreadsheet program to not evaluate this cell. In order to enter text with a leading apostrophe you would enter two apostrophes.
Alternatively you can format the cell as text which has the same effect without leading apostrophe. A leading apostrophe entered into a text formatted cell is taken as a literal apostrophe.

I strictly avoid numeric text instead of numbers which is why I added the explicit conversion by VALUE which does not harm. It simply puts things straight because the first argument to the CHOOSE function should be an integer number between 1 and the count of items.

[pmralbuquerque]

OK, in that case, I'll keep the use of VALUE, just to be certain.