Tristate Switching Oddness

English
#1

Hi All,

Hoping someone can help me. I’m experimenting with tristate switches, and I’m experiencing something I can’t find reference to anywhere.

When I engage tristate, pointing it to an empty cell as the Linked cell definition (in the Properties/Data tab) and then cycle the switch through the three available states, the usual TRUE and FALSE values show, but the third value is an output of #N/A with a formula indicator in the bottom lefthand corner.

=ISFORMULA returns TRUE, so it’s definitely a formula, but what I don’t understand is why it’s not a simple 3rd definable value, and where do I enter the formula that it represents?

I’ve searched the User Guide, this forum, and the general web, and found very little to nothing on “tristate” behaviour. The user guide shows nothing. Frustrating.

Any help anyone can offer, is much appreciated.

Kind regards.

CT

#3

Oh, this is such a minor inconvenience compared to the fact that this world is going to hell! Don’t be upset, let’s think logically.
So, with two states everything is simple and clear - a logical value that can be in two and only two mutually exclusive states.
How would you define the state “Little information”, “Not clear yet”, “Data not available”? (see? “not available” = NA)
How are you going to use this third value? Maybe you should think about a radio button? Everything will be unambiguous there - each of the states can be clearly defined without unnecessary tricks.

#4

Hey again JohnSUN,

Thanks for the reply.

Why on earth would LO (or more importantly the designers) assume the third state has to be so vague? “Little information”, “Not clear yet”, “Data not available”?

Bar Open
Bar Close
Mid Price

What’s so “unknowable” about that? Why would it require a formula to define? Why not have a simple state defined in this window like the other two?

Screenshot 2025-05-21 at 10.58.21 am

Reference value 1 (on) …
Reference value 2 (off) …
Reference value 3 (alt) …

That’s what I call the definition of frustrating. “Not simple”. “Unobvious”. “Unintuitive”.

Thanks again for all your help the other day. I’m still working my way through the sheet and replacing all the repeated formulas with Named formulas. Wow, you were SO right. What a huge difference it makes to the Save time and file size etc!! Brilliant stuff.

Cheers.

CT

#5

PS,

My original reference to “Frustrating” was in connection to the user guide having no mention of tristate that comes up by entering that word in the search bar.

#6

And I just don’t understand WHERE I’m supposed to enter this mystical missing definition formula? When I enter it into the cell that’s being switched itself, it reverts back to N/A when I cycle the switch around twice more. Daft.

Maybe there’s somewhere obvious in the control properties windows, but it’s not clear to me. No doubt my failing.

#7

Mid Price? There is no word Bar in this phrase… Price is not a third state for Open/Close. Perhaps you meant to write “Is it really a Bar?”

#8

Okay, let’s get around the problem described, it’s not difficult.
We start from the settings shown in your screenshot - linked cell is P15.
Place the formula =IFNA(P15;"Mid Price") in P16 and use this cell in further calculations.

#9

Mid Price? There is no word Bar in this phrase… Price is not a third state for Open/Close. Perhaps you meant to write “Is it really a Bar?”

No, I have a formula that differentiates between those 3 text entries, and executes a calculation based on one of the 3 ie: Bar Open and Bar Close are self evident, but when it reads “Mid Price”, it takes the high and low prices and averages them as an output. So I could have chosen “Bar Mid” to maintain the nomenclature convention, but so what, the point still stands. Why does LO make a third optional state so bloody cryptic to implement when the first two are so damn obvious and just work?? Frustrating.

#10

Thanks JohnSUN, I’ll give this a go now. CT

#11

Well, once again Sir, your genius knows no bounds!! That fixed it. Great stuff.

I’m up and running again, thank you so much.

Retribution shall be swift and final! :sweat_smile:

Cheers from Wales.

CT

#13

transfer at first the text to a numeral like:

  • “bar open” => ‘1’,
  • “mid price” => ‘2’,
  • “bar closed” => ‘3’.
  • a empty cell = ‘0’.
=IFS(A1="bar open",1, "mid price",2, "bar closed",3, 1=1,0)

than you are able to calculate and operate with those numerals. At least you can transform it into a text.

// that is my suggestion //

#15

Am I the only one who doesn’t understand this?

??? once again.

image to tell contributors that a questiuon is about CheckBox objects?
The word would do better.

Yes.

#20

A tristate is no, yes, or indeterminate.

https://api.libreoffice.org/docs/idl/ref/TriState_8idl.html

#12

CALC only recognizes two modes of calculation:

  • the binary/dual system with ‘0’ and ‘1’ for “false/low” and “true/high” respectively, and
  • the decimal/integer system of the numbers 0…10…100…1000…ff.

This is the preferred method of any spreadsheet. Calculating with text is done as a last resort and doesn’t always make sense or produce a satisfactory result. You want to force the “tri-state” system of “1, 2, 3” into the “bi-state” system of “0 and/or 1”?! That always fails without exception! Use the decimal system for your task and limit the possible numbers to 3: e.g., “2-4”. With the function ‘=DEC2BiN(2)’, you’ll see how to calculate in the binary system, bit by bit. Each bit can only be ‘0’ or ‘1’. The decimal ‘2’ becomes ‘10’ in bits, the ‘3’ becomes ‘11’, the ‘4’ becomes ‘100’ (that’s not hundred but one-null-null!), and the ‘5’ becomes ‘101’. Only 1 bit can be represented in CALC as logical “false” or “true”.

Tipp: Avoid trying to “calculate” with letters whenever possible; in CALC, you “operate” with text using functions other than mathematical/arithmetric ones.

#14

Hey Koyotak,

Thanks for that info.

This has echoes of something JohnSUN was touching on with me the other day.

If I understood him right, it was along the lines of adopting a style of using numbers in place of conditions in formulas where possible (how very Godelian of everybody :rofl:).

This will be an excellent learning exercise for someone at my stage of development. Thanks for the tip.

Regards.

CT

#16

Hey CT

With these features you will enter the continent of digital humans where you will not find any normal person.

#17

Ha!!! :rofl: :upside_down_face:

Even my wife laughed at that one. She already thinks that’s where I live, and I already KNOW it’s where all you Fruit-Bats around here DEFINITELY live.

All power to you!! :index_pointing_at_the_viewer:

1 Like
#18

TEXT versus NUMBER: The eternal conflict has finally been resolved after many years, instead of petrifying forever. In those years, in which I desperately sometimes got a desired result, sometimes an undesired one, precisely because CALC, how could it be otherwise(?!), simply can’t always calculate correctly with TEXT. The dilemma of the conflict between the elements that can only read and those that can only calculate. The questionable elements that can also write seem to continue to languish in their dark dilemma, if not long since extinct. Therefore, it doesn’t surprise me that, however hard I search, I’ve only found things by chance, when I direct the “right” question to the “right” audience at the “right” time. That happened the day before yesterday!

#19

«That continent is very small, the surf heavy, so only shipwrecked people are stranded on its shores.» :ocean:🫨:face_with_head_bandage::cloud_with_lightning_and_rain::ocean:

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.