for Mark W

No EXACT returns a boolean (TRUE or FALSE) value.
This tidbit can be found in the Help index topic
for "EXACT worksheet function". If you haven't
noticed I'm very anal about encouraging the
consultation of function-related Help topics.
This is the best way to learn the data types
for the inputs (arguments) and outputs (results)
of new and exciting functions.

Since SUM ignores boolean values in a cell reference
(see the help topic) it is necessary to coerce
the boolean value into a numeric 0 or 1. Excel's
arithmetic operators will force Excel to convert
boolean constants into a numeric values. There
are other examples of Excel's data type coercion
that are quite useful.

I agree Mark, BUT, don't rely (This is out of experience) too much on what the help file says. It's not being updated/modified/changed in any way... and I've seen some mistakes in it. Mainly in VBA help, but, the guys who created both help files are probably the same, right ?

Anyway... it's still a very good start !

Juan Pablo G.

Don't know who's responsible for the maintenance
of Excel and VBA help files, but can't disagree
about the presence of errors. In fact, just the
other day I found a discrepancy in the help file
for TRIMMEAN.

I'd supplement the help files with a couple of
other techniques....

1. After selecting the function and its complete
argument list (including parentheses) in the
formula bar, use F9 to see what the function is
returning.

2. After typing a function without its argument
list (e.g., =EXACT) press Control+A and examine
the suggested argument data types.

thanks Mark and Juan Pablo,

so a boolean TRUE result plus zero will result in a numeric value of 1, whilst a boolean FALSE result plus zero will result in a numeric value of 0 ?

bear with me here
:-)

Yes, sir!!...

...also, =NOT(0) is TRUE, =OR(1,0) is TRUE,
=AND(0,1) is FALSE.

...and, the array formula...
{=IF({-1,0,1},TRUE)} is {FALSE,TRUE,FALSE}.

Oops, I goofed!! Re: Yes, sir!!...

...and, the array formula...
{=IF({-1,0,1},TRUE)} is {TRUE,FALSE,TRUE} because
IF treats any non-zero, numeric value as TRUE.

Re: Oops, I goofed!! Re: Yes, sir!!...

no....

good one - it forced me to utilise that help file on "if". I was okay up until that damn array formula. I was gonna post an "I give up it past my bedtime" but refreshed first, luckily ! SO it made me think, rather than just accept it at face value :-)

interesting the coerction thing though, I hadn't fully appreciated that little but potentially massive trick.

Cheers Mark
:-)

Yeah, coercion is fun! Here are some more...

="1/1/02"+0 produces 37257, the date value for Jan 1.
="6:00"+0 produces 0.25, the time value for 6:00 AM.
="1"+0 produces 1 (0 is the identity value for addition)
="2"*1 produce 2 (1 is the identity value for multiplication)
=1&"" produces "1"
="-1"+0 produces -1
="(1)"+0 produces -1
=-"1" produces -1 (this is the unary minus operation)

Re: Yeah, coercion is fun! & is...

bread and butter of array and sumproduct formulas with Boolean terms.