Home |
Search |
Today's Posts |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search for 2 true arguments and return true or false | Excel Discussion (Misc queries) | |||
Formatting TRUE as 1 and FALSE as 0 | Excel Discussion (Misc queries) | |||
Function to return True/False if all are validated as True by ISNU | Excel Worksheet Functions | |||
True or False | Excel Worksheet Functions | |||
Reverse false and combine with true true value | Excel Worksheet Functions |