Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 133
Default Formatting TRUE/FALSE to 1/0

It's nice to use TRUEs and FALSEs in simple calculations (such as add and
multiply) where TRUE cells are treated by Excel as having a value of 1 and
FALSE cells 0 - although it is strange that =SUM treats them all as 0.

Does anyone know a way of formatting logical cells to actually display 1 for
TRUE and 0 for FALSE?

Obviously one could put together a macro or UDF that physically converted
these values but I would ideally like some kind of Excel format.
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Formatting TRUE/FALSE to 1/0

Yes, there is a way to format logical cells to display 1 for TRUE and 0 for FALSE. Here are the steps:
  1. Select the cells that contain the TRUE/FALSE values you want to format.
  2. Right-click on the selected cells and choose "Format Cells" from the context menu.
  3. In the "Format Cells" dialog box, select the "Custom" category.
  4. In the "Type" field, enter the following format code:
    Formula:
    0;0;;@ 
  5. Click "OK" to apply the formatting.

Now, the cells that contain TRUE will display 1 and the cells that contain FALSE will display 0. Note that this formatting does not change the underlying values of the cells, so you can still use them in calculations as before.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9,101
Default Formatting TRUE/FALSE to 1/0

You can do it yourself with a if statement

if(a1, 1, 0)

"Smallweed" wrote:

It's nice to use TRUEs and FALSEs in simple calculations (such as add and
multiply) where TRUE cells are treated by Excel as having a value of 1 and
FALSE cells 0 - although it is strange that =SUM treats them all as 0.

Does anyone know a way of formatting logical cells to actually display 1 for
TRUE and 0 for FALSE?

Obviously one could put together a macro or UDF that physically converted
these values but I would ideally like some kind of Excel format.

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 133
Default Formatting TRUE/FALSE to 1/0

Thanks but that's another column, not a format.

"Joel" wrote:

You can do it yourself with a if statement

if(a1, 1, 0)

"Smallweed" wrote:

It's nice to use TRUEs and FALSEs in simple calculations (such as add and
multiply) where TRUE cells are treated by Excel as having a value of 1 and
FALSE cells 0 - although it is strange that =SUM treats them all as 0.

Does anyone know a way of formatting logical cells to actually display 1 for
TRUE and 0 for FALSE?

Obviously one could put together a macro or UDF that physically converted
these values but I would ideally like some kind of Excel format.

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 620
Default Formatting TRUE/FALSE to 1/0

.... or just =--A1
--
David Biddulph

"Joel" wrote in message
...
You can do it yourself with a if statement

if(a1, 1, 0)


"Smallweed" wrote:

It's nice to use TRUEs and FALSEs in simple calculations (such as add and
multiply) where TRUE cells are treated by Excel as having a value of 1
and
FALSE cells 0 - although it is strange that =SUM treats them all as 0.

Does anyone know a way of formatting logical cells to actually display 1
for
TRUE and 0 for FALSE?

Obviously one could put together a macro or UDF that physically converted
these values but I would ideally like some kind of Excel format.





  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,726
Default Formatting TRUE/FALSE to 1/0

Not a format, it is not a number so format does not apply.

Change the formula to return 1 or 0, for instance

=(U1="")

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Smallweed" wrote in message
...
It's nice to use TRUEs and FALSEs in simple calculations (such as add and
multiply) where TRUE cells are treated by Excel as having a value of 1 and
FALSE cells 0 - although it is strange that =SUM treats them all as 0.

Does anyone know a way of formatting logical cells to actually display 1
for
TRUE and 0 for FALSE?

Obviously one could put together a macro or UDF that physically converted
these values but I would ideally like some kind of Excel format.



  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default Formatting TRUE/FALSE to 1/0

On Mon, 19 Feb 2007 04:18:16 -0800, Smallweed
wrote:

It's nice to use TRUEs and FALSEs in simple calculations (such as add and
multiply) where TRUE cells are treated by Excel as having a value of 1 and
FALSE cells 0 - although it is strange that =SUM treats them all as 0.

Does anyone know a way of formatting logical cells to actually display 1 for
TRUE and 0 for FALSE?

Obviously one could put together a macro or UDF that physically converted
these values but I would ideally like some kind of Excel format.


The number formats only apply to cells that contain numbers.

If you want to display 1 or 0, you would have to convert the logical results to
numbers, perhaps by preceding your formula with a double unary.

e.g. =--(your_formula_that_returns_TRUE/FALSE)


--ron
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Formatting TRUE/FALSE to 1/0

I wouldn't do this.

I think it causes other problems later on, but you could use:
Tools|Options|transition tab|Check Transition formula evalation.

If you do this, remember that you changed this setting when you're debugging
other stuff that looks fine--but just doesn't work the way you think it should.


Smallweed wrote:

It's nice to use TRUEs and FALSEs in simple calculations (such as add and
multiply) where TRUE cells are treated by Excel as having a value of 1 and
FALSE cells 0 - although it is strange that =SUM treats them all as 0.

Does anyone know a way of formatting logical cells to actually display 1 for
TRUE and 0 for FALSE?

Obviously one could put together a macro or UDF that physically converted
these values but I would ideally like some kind of Excel format.


--

Dave Peterson
  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 133
Default Formatting TRUE/FALSE to 1/0

Thanks for all the replies; I especially like the double unary as a neat
method of, in effect, multiplying by 1. (Incidentally I did a search on that
and was interested to see that you can also use this -- prefix for the lookup
value in a VLOOKUP in case your lookup value is text and the first column of
the lookup table is numbers - useful if unrelated!).

As Bob says, TRUE/FALSE are not numbers so I guess we should just be
grateful they return 1 and 0 in simple calculations (and aren't -1 and 0 as
they are in VBA).

Looking at it the other way around, it would be nice if Excel could format
1s and 0s to TRUEs and FALSEs for use in truth tables, e.g.

A B A x B
TRUE TRUE TRUE
TRUE FALSE FALSE
FALSE TRUE FALSE
FALSE FALSE FALSE

You can fiddle it with the number format "TRUE";;"FALSE" but this goes wrong
(strangely) for a typed 0.

"Ron Rosenfeld" wrote:

On Mon, 19 Feb 2007 04:18:16 -0800, Smallweed
wrote:

It's nice to use TRUEs and FALSEs in simple calculations (such as add and
multiply) where TRUE cells are treated by Excel as having a value of 1 and
FALSE cells 0 - although it is strange that =SUM treats them all as 0.

Does anyone know a way of formatting logical cells to actually display 1 for
TRUE and 0 for FALSE?

Obviously one could put together a macro or UDF that physically converted
these values but I would ideally like some kind of Excel format.


The number formats only apply to cells that contain numbers.

If you want to display 1 or 0, you would have to convert the logical results to
numbers, perhaps by preceding your formula with a double unary.

e.g. =--(your_formula_that_returns_TRUE/FALSE)


--ron

  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 133
Default Formatting TRUE/FALSE to 1/0

Actually the "TRUE";;"FALSE" format has suddenly started working for me for
typed zeroes in a new sheet.

"Smallweed" wrote:

Thanks for all the replies; I especially like the double unary as a neat
method of, in effect, multiplying by 1. (Incidentally I did a search on that
and was interested to see that you can also use this -- prefix for the lookup
value in a VLOOKUP in case your lookup value is text and the first column of
the lookup table is numbers - useful if unrelated!).

As Bob says, TRUE/FALSE are not numbers so I guess we should just be
grateful they return 1 and 0 in simple calculations (and aren't -1 and 0 as
they are in VBA).

Looking at it the other way around, it would be nice if Excel could format
1s and 0s to TRUEs and FALSEs for use in truth tables, e.g.

A B A x B
TRUE TRUE TRUE
TRUE FALSE FALSE
FALSE TRUE FALSE
FALSE FALSE FALSE

You can fiddle it with the number format "TRUE";;"FALSE" but this goes wrong
(strangely) for a typed 0.

"Ron Rosenfeld" wrote:

On Mon, 19 Feb 2007 04:18:16 -0800, Smallweed
wrote:

It's nice to use TRUEs and FALSEs in simple calculations (such as add and
multiply) where TRUE cells are treated by Excel as having a value of 1 and
FALSE cells 0 - although it is strange that =SUM treats them all as 0.

Does anyone know a way of formatting logical cells to actually display 1 for
TRUE and 0 for FALSE?

Obviously one could put together a macro or UDF that physically converted
these values but I would ideally like some kind of Excel format.


The number formats only apply to cells that contain numbers.

If you want to display 1 or 0, you would have to convert the logical results to
numbers, perhaps by preceding your formula with a double unary.

e.g. =--(your_formula_that_returns_TRUE/FALSE)


--ron



  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default Formatting TRUE/FALSE to 1/0

On Mon, 19 Feb 2007 06:57:10 -0800, Smallweed
wrote:

Actually the "TRUE";;"FALSE" format has suddenly started working for me for
typed zeroes in a new sheet.


Yes it should. You are formatting a number to display TRUE or FALSE. In your
original request, you wanted to opposite (type TRUE or FALSE, and display a
number).

Of course, any positive number will display TRUE; so if someone accidently
typed in a 2 or 10, you might see TRUE, but any mathematic operation would
perhaps not give the expected results.

You could perhaps use the custom format:

[=1]"TRUE";[=0]"FALSE";General

That would display TRUE for a typed in 1; FALSE for a typed in 0; and the
actual value if you typed in anything else.


--ron
  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,726
Default Formatting TRUE/FALSE to 1/0


"Ron Rosenfeld" wrote in message
...
On Mon, 19 Feb 2007 06:57:10 -0800, Smallweed
wrote:


Of course, any positive number will display TRUE; so if someone accidently
typed in a 2 or 10, you might see TRUE, but any mathematic operation would
perhaps not give the expected results.


Actually, it is any non-zero number


  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,726
Default Formatting TRUE/FALSE to 1/0


"Smallweed" wrote in message
...
Thanks for all the replies; I especially like the double unary as a neat
method of, in effect, multiplying by 1. (Incidentally I did a search on
that
and was interested to see that you can also use this -- prefix for the
lookup
value in a VLOOKUP in case your lookup value is text and the first column
of
the lookup table is numbers - useful if unrelated!).


Yes, because -- is coercing to number, so anything that is numeric,
including say "1234", can be coerced.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Search for 2 true arguments and return true or false David Excel Discussion (Misc queries) 3 July 15th 06 10:18 AM
Formatting TRUE as 1 and FALSE as 0 reddykkk Excel Discussion (Misc queries) 2 April 5th 06 12:13 PM
Function to return True/False if all are validated as True by ISNU Tetsuya Oguma Excel Worksheet Functions 2 March 15th 06 11:28 AM
True or False Krish Excel Worksheet Functions 3 November 29th 05 02:09 AM
Reverse false and combine with true true value Emmie99 Excel Worksheet Functions 5 August 17th 05 04:38 PM


All times are GMT +1. The time now is 10:43 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"