Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numerical value of logicals
As I understand it, when logicals are used in a formula, TRUE=1 and FALSE=0.
If this is so, then why is Excel evaluating the formula below as 0 instead of 1? =SUMPRODUCT({FALSE,TRUE,FALSE},{TRUE,TRUE,FALSE}) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numerical value of logicals
You need to coerce the Boolean into numeric with an arithmetic operation.
Here are two example; each gives result of 1. 1) =SUMPRODUCT(--{FALSE,TRUE,FALSE}, --{TRUE,TRUE,FALSE}) This uses the unary negation operator (-) twice 2) =SUMPRODUCT({FALSE,TRUE,FALSE}*{TRUE,TRUE,FALSE}) This uses multiplication which is what =SUMPRODUCT({FALSE,TRUE,FALSE}, {TRUE,TRUE,FALSE}) is expected to do but does not! -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "hmm" wrote in message ... As I understand it, when logicals are used in a formula, TRUE=1 and FALSE=0. If this is so, then why is Excel evaluating the formula below as 0 instead of 1? =SUMPRODUCT({FALSE,TRUE,FALSE},{TRUE,TRUE,FALSE}) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numerical value of logicals
Hi,
Try this instead. The double dash signs coerce TRUE to 1 and FALSE to 0. SUMPRODUCT(--{FALSE,TRUE,FALSE},--{TRUE,TRUE,FALSE}) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "hmm" wrote in message ... As I understand it, when logicals are used in a formula, TRUE=1 and FALSE=0. If this is so, then why is Excel evaluating the formula below as 0 instead of 1? =SUMPRODUCT({FALSE,TRUE,FALSE},{TRUE,TRUE,FALSE}) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numerical value of logicals
You need to turn the trues & falses to numbers.
One way is a double unary minus: =SUMPRODUCT(--{FALSE,TRUE,FALSE},--{TRUE,TRUE,FALSE}) http://www.mcgimpsey.com/excel/formulae/doubleneg.html http://xldynamic.com/source/xld.SUMPRODUCT.html Another way is multiplication. =SUMPRODUCT({FALSE,TRUE,FALSE}*{TRUE,TRUE,FALSE}) -- David Biddulph "hmm" wrote in message ... As I understand it, when logicals are used in a formula, TRUE=1 and FALSE=0. If this is so, then why is Excel evaluating the formula below as 0 instead of 1? =SUMPRODUCT({FALSE,TRUE,FALSE},{TRUE,TRUE,FALSE}) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numerical value of logicals
Thanks to all of you. The solutions you presented worked!
"hmm" wrote: As I understand it, when logicals are used in a formula, TRUE=1 and FALSE=0. If this is so, then why is Excel evaluating the formula below as 0 instead of 1? =SUMPRODUCT({FALSE,TRUE,FALSE},{TRUE,TRUE,FALSE}) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
numerical error | Excel Discussion (Misc queries) | |||
Excel Conditional Formating using 'like' or wildcard logicals | Excel Worksheet Functions | |||
numerical integration | Excel Discussion (Misc queries) | |||
Numerical values only | Excel Discussion (Misc queries) | |||
Associated Numerical Values | Excel Discussion (Misc queries) |