If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




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. 
Ads 
#2




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. 
#3




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. 
#4




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. 
#5




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. 
#6




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 
#7




Formatting TRUE/FALSE to 1/0
I wouldn't do this.
I think it causes other problems later on, but you could use: ToolsOptionstransition tabCheck Transition formula evalation. If you do this, remember that you changed this setting when you're debugging other stuff that looks finebut 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 
#8




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 > 
#9




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 > > 
#10




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 
Thread Tools  
Display Modes  


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 