Ask Your Question
0

Change Boolean to Yes/No instead of True/False

asked 2016-05-31 23:36:20 +0200

ventolinmono gravatar image

updated 2016-05-31 23:39:52 +0200

Hi

How can Calc interpret or format cells that have "Yes" or "No" word strings to 1 or 0 number values?

(Really using Spanish Si and No.)

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
0

answered 2019-05-14 21:07:21 +0200

librelive gravatar image

updated 2019-05-15 22:23:32 +0200

i suggest basically the same as m.a.riosv wrote, but a little better:

in format cells dialogue fill format code input with the following:

[=1]"Yes";[=0]"No";General - this means it will be yes on true/1 values, no on false/0 values and will use general format otherwise.

one may even use the following string to format it as / instead of yes/no and color differentiation: [GREEN][=1]"✓";[RED][=0]"✗";[BLUE]General;[BLUE]@

image description

this works on libreoffice calc v6.1.5.2 for ubuntu - i've tested

i call it "a little bit better" solution because it implements design by contract pattern, i.e. if for some unexpected reason the value will be something else than a boolean, then it will be displayed as usual, and not hidden, thus hiding a possible error in design!

edit flag offensive delete link more

Comments

Caveat, negative values are displayed without minus sign then (which may be neglect-able though but confusing). And no, if negative values are desired AFAIK there's no format code that would cover all possibilities as there are only 3 subformats (plus the text subformat).

erAck gravatar imageerAck ( 2019-05-14 22:18:48 +0200 )edit
0

answered 2019-05-14 22:37:20 +0200

erAck gravatar image

It occurred to me that the question was not about formatting but actually literally about interpreting a Yes or No word, or Si and No. So that would be the formula

=IF( A1="Si"; 1; IF( A1="No"; 0; A1))

assuming content in cell A1, which if that is Si displays 1, if No displays 0, else the content of A1, or 0 if A1 is empty.

edit flag offensive delete link more
0

answered 2016-05-31 23:58:38 +0200

m.a.riosv gravatar image

updated 2016-06-01 02:24:21 +0200

Use as number format

"Yes";;"No"

Result is "Yes" for positive values, "No" for zeroes, nothing for negatives.

Updated

Maybe in your language the separator is the comma not the semicolon.

Please see the attached file: YesNoFormat.ods

edit flag offensive delete link more

Comments

Sorry, first time formatting a user-defined cell. What do I type under "Format code"? Typed "Yes";;"No" but nothing happens.

ventolinmono gravatar imageventolinmono ( 2016-06-01 01:49:28 +0200 )edit

Please attach minimal sample file about what you want achieve. If you like there is a site in Spain https://ask.libreoffice.org/es/questi...

m.a.riosv gravatar imagem.a.riosv ( 2016-06-04 11:39:47 +0200 )edit

The format code separator is always ; semicolon, language independent.

erAck gravatar imageerAck ( 2019-05-14 22:19:43 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-05-31 23:36:20 +0200

Seen: 1,865 times

Last updated: May 15