meaning of -- in the formulas

[Guest]

Hi all,

does anybody know the meaning of "--" in a formula?

for example:
=IF(SUMPRODUCT(--(B4:B5=B5))=1;1;0)


thanks a lot!
Massimo

 

[keepITcool]

Massimo,

the -- is called a unary minus and used to force a conversion from
booleans to numbers. Many array oriented functions work better with
numbers than with booleans and the guru's have found it faster than n().

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


max wrote :

 

[Bob Phillips]

Massimo,

Take a look at http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Aladin Akyurek]

Hi all,

does anybody know the meaning of "--" in a formula?

for example:
=IF(SUMPRODUCT(--(B4:B5=B5))=1;1;0)


thanks a lot!
Massimo

Don't be seduced to invoke SumProduct when unnecessary, even costly...

The goal of your formula can be achieved with...

=IF(COUNTIF(B4:B5,B5)=1;1;0)

Even shorter...

=(COUNTIF(B4:B5,B5)=1)+0

=--(COUNTIF(B4:B5,B5)=1)

 

[Guest]

Hi 'keepITcool',

thanks a lot for your clear definition

Massimo

 

[Guest]

Hi Bob,

thanks for the link, it's extremely explanatory and detailed.

Massimo

 

[Guest]

Thanks!

much easier especially for a non-formula-expert like me !!

cheers
Massimo