Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
hmm hmm is offline
external usenet poster
 
Posts: 175
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,393
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
hmm hmm is offline
external usenet poster
 
Posts: 175
Default 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
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
numerical error Srinivas Excel Discussion (Misc queries) 20 May 6th 10 02:43 AM
Excel Conditional Formating using 'like' or wildcard logicals LDUNN1 Excel Worksheet Functions 0 October 23rd 06 11:30 AM
numerical integration integreat Excel Discussion (Misc queries) 4 May 12th 06 02:40 AM
Numerical values only saziz Excel Discussion (Misc queries) 4 March 17th 06 01:50 AM
Associated Numerical Values kdoggity Excel Discussion (Misc queries) 3 February 24th 06 08:07 PM


All times are GMT +1. The time now is 04:28 PM.

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"