Hello,

I have a cell (say A1) which contains a value 0 or 1 , or equivalently
FALSE or TRUE. I use it in an IF function elsewhere,, eg.
IF(A1,'Cat','Dog').

No problem so far.

I would prefer to display 'OFF' or 'ON' in A1. Can I make OFF & ON
equivalent to boolean values, or must I change the formula to IF(a1='ON',
'Cat','Dog') ?

Thanks
K

The second option is needed IF(A1="ON", "Cat", "Dog")
Note the double quotes (I expect you meant to use them!)
There is no way to alter FALSE to OFF, TRUE to ON
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"KRK" wrote in message
...
Hello,

I have a cell (say A1) which contains a value 0 or 1 , or equivalently
FALSE or TRUE. I use it in an IF function elsewhere,, eg.
IF(A1,'Cat','Dog').

No problem so far.

I would prefer to display 'OFF' or 'ON' in A1. Can I make OFF & ON
equivalent to boolean values, or must I change the formula to IF(a1='ON',
'Cat','Dog') ?

Thanks
K

"KRK" wrote:
I have a cell (say A1) which contains a value 0 or 1 ,
or equivalently FALSE or TRUE.
[....]
I would prefer to display 'OFF' or 'ON' in A1.

If the cell value is 0 or 1, not FALSE or TRUE, you can use the following
Custom format to display "off" or "on":

[=0]"off";[=1]"on"

I have not found a custom format that works when the cell value is FALSE or
TRUE per se.


----- original message -----

"KRK" wrote in message
...
Hello,

I have a cell (say A1) which contains a value 0 or 1 , or equivalently
FALSE or TRUE. I use it in an IF function elsewhere,, eg.
IF(A1,'Cat','Dog').

No problem so far.

I would prefer to display 'OFF' or 'ON' in A1. Can I make OFF & ON
equivalent to boolean values, or must I change the formula to IF(a1='ON',
'Cat','Dog') ?

Thanks
K

PS....

I wrote:
I have not found a custom format that works when
the cell value is FALSE or TRUE per se.

But you might be able to ensure that the value in the cell (A1) is zero or
one instead of FALSE or TRUE. That depends on how that value is derived.
For example, if A1 has the formula =(B1=B2), you can change that to
=--(B1=B2). The "--" (or any other arithmetic operation) has the effect of
converting the boolean values to zero or one.


----- original message -----

"Joe User" <joeu2004 wrote in message
...
"KRK" wrote:
I have a cell (say A1) which contains a value 0 or 1 ,
or equivalently FALSE or TRUE.
[....]
I would prefer to display 'OFF' or 'ON' in A1.

If the cell value is 0 or 1, not FALSE or TRUE, you can use the following
Custom format to display "off" or "on":

[=0]"off";[=1]"on"

I have not found a custom format that works when the cell value is FALSE
or TRUE per se.


----- original message -----

"KRK" wrote in message
...
Hello,

I have a cell (say A1) which contains a value 0 or 1 , or equivalently
FALSE or TRUE. I use it in an IF function elsewhere,, eg.
IF(A1,'Cat','Dog').

No problem so far.

I would prefer to display 'OFF' or 'ON' in A1. Can I make OFF & ON
equivalent to boolean values, or must I change the formula to IF(a1='ON',
'Cat','Dog') ?

Thanks
K

If the cell value is 0 or 1, not FALSE or TRUE, you can use the following
Custom format to display "off" or "on":

[=0]"off";[=1]"on"

A slightly simpler format pattern that can be used to do this is...

"On";;"Off"

--
Rick (MVP - Excel)

"Rick Rothstein" wrote:
A slightly simpler format pattern that can be used
to do this is...
"On";;"Off"

Right. Klunk!

That's what I started with. But when it did not work with cell values of
TRUE and FALSE, I moved on to conditional custom formats, still trying with
TRUE and FALSE (e.g. [=TRUE]"on"). And when that did not work, I simply
tweaked the conditional custom format to use 1 and 0. Which still did not
work with cell values of TRUE and FALSE, of course; hence the requirement to
actually use 1 and 0. At that point, I shoulda started back Square One.
But I was stuck in a particular mindset. Klunk!

Thanks for posting the better custom format.


----- original message -----

"Rick Rothstein" wrote in message
...
If the cell value is 0 or 1, not FALSE or TRUE, you can use the following
Custom format to display "off" or "on":

[=0]"off";[=1]"on"

A slightly simpler format pattern that can be used to do this is...

"On";;"Off"

--
Rick (MVP - Excel)

A slightly simpler format pattern that can be used
to do this is...
"On";;"Off"

Right. Klunk!

That's what I started with. But when it did not work with cell values of
TRUE and FALSE, I moved on to conditional custom formats, still trying
with TRUE and FALSE (e.g. [=TRUE]"on"). And when that did not work, I
simply tweaked the conditional custom format to use 1 and 0. Which still
did not work with cell values of TRUE and FALSE, of course; hence the
requirement to actually use 1 and 0. At that point, I shoulda started
back Square One. But I was stuck in a particular mindset. Klunk!

We've all been there 'Joe'.... we have all been there. <g

--
Rick (MVP - Excel)

Hi all, & thanks for the help, its very useful.

KK


"KRK" wrote in message
...
Hello,

I have a cell (say A1) which contains a value 0 or 1 , or equivalently
FALSE or TRUE. I use it in an IF function elsewhere,, eg.
IF(A1,'Cat','Dog').

No problem so far.

I would prefer to display 'OFF' or 'ON' in A1. Can I make OFF & ON
equivalent to boolean values, or must I change the formula to IF(a1='ON',
'Cat','Dog') ?

Thanks
K