Calc: how do I change Boolen annotation?

Todd2 · February 25, 2021, 8:36pm

Hi All,

LibreOffice-7.1.0-Linux_x86-64_rpm.tar.gz
Fedora 33

How do I change the annotation of boolean values from TRUE/FALSE to PASS/FAIL? PASS = 1, FAIL = 0.

What I want the user to see if PASS and FAIL. What I want the calculations to see is 1 and 0. So far “user-defined” is failing me. I obviously do not know how to use it.

Many thanks,
-T

erAck · February 25, 2021, 8:45pm

Use a format code

[<>0]"PASS";"FAIL"

or

"PASS";"PASS";"FAIL"

Todd2 · February 25, 2021, 9:23pm

How ?

anon73440385 · February 25, 2021, 9:29pm

How ?

Right click the cell -> Format Cells and literally add

[<>0]"PASS";"FAIL" or
"PASS";"PASS";"FAIL" into field named Format Code

You may want to read [LibreOffice Help - Number Format Codes](https://help.libreoffice.org/latest/en-US/text/shared/01/05020301.html?DbPAR=SHARED#bm_id3153514) - especially the first 4 items (1. through 4.)

Todd2 · February 25, 2021, 9:35pm

Awesome! Thank you!

Todd2 · February 25, 2021, 10:11pm

I am getting #VALUE! when I try multiplying the pass/fail variable with an intege in the cell next to it.

anon73440385 · February 26, 2021, 11:04am

Can’t repro - please upload a sample file showing the #VALUE! for data calculated from cells being integers and formatted with [<>0]"PASS";"FAIL"

image description

Todd2 · February 26, 2021, 8:12pm

I see what the issue is. [<>0]“PASS”;“FAIL” sows the analysis of the data. I want to create the data. If I write PASS, I want formulas to see 1 and FAIL to see 0. So [<>0]“PASS”;“FAIL” is backwards to what I want.

anon73440385 · February 26, 2021, 9:45pm

You can’t write PASS and have the cell value to be 1 and FAIL to have it 0. PASS/FAIL means it is text and that’s the content of the cell and not content can change itself. The only option you have: Write PASS/FAIL into cell A1 and into another cell (e.g. B1) write =IFS(A1="PASS";1;A1="FAIL";0;1;"") - otherwise you’d need to combine user functions with the format code given.

Example:

Define the following user function (macros)

Function PASS() as Variant
   PASS=1
End Function

Function FAIL() as Variant
  PASS = 0
End Function

and use =PASS() / =FAIL() in conjunction with the Format Code given in the answer. However: From my perspective really too much of unnecessary overhead for nothing.

erAck · February 27, 2021, 6:18pm

Copy&paste error, in FAIL() it should be FAIL = 0 instead of PASS = 0.

Todd2 · February 28, 2021, 1:12am

That explains it. And I will save for later as it will be useful. Thank you!