MrExcel Publishing
Your One Stop for Excel Tips & Solutions

for Mark W


Posted by Chris D on February 14, 2002 11:52 AM

Hi Mark,

What's the "+0" for in 22319.html thread 22346 ?

WOuldn't the "exact" arguement return a 0 or 1 anyhow, or you just covering your bases ?

Sorry for the stupid question if it is stupid !

Nice formula though, you guys never cease to amaze me
:-)


Posted by Mark W. on February 14, 2002 12:16 PM

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.

Posted by Juan Pablo G. on February 14, 2002 12:35 PM

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.

Posted by Mark W. on February 14, 2002 12:51 PM

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.

Posted by Chris D on February 14, 2002 1:38 PM

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
:-)


Posted by Mark W. on February 14, 2002 1:50 PM

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}.


Posted by Mark W. on February 14, 2002 1:52 PM

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.


Posted by Chris D on February 14, 2002 2:12 PM

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
:-)

Posted by Mark W. on February 14, 2002 2:35 PM

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)

Posted by Aladin Akyurek on February 14, 2002 2:43 PM

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

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