-- in a formula

[Desert Piranha]

Hi,
These two formulas give the same result, both of which are correct. They sum the Odd numbers in a Defined Range. The only difference is the -- in the second one. Whats it for, or whats it do? Anything?

=SUMPRODUCT((MOD(COLUMN(NumberRange),2)=1)*NumberRange)
=SUMPRODUCT(--(MOD(COLUMN(NumberRange),2)=1)*NumberRange)

[shades]

Howdy. It coerces Excel to recognize it as a numerical value.

[MWE]

Dave: what is the significance of your Sherlockian reference?

[Desert Piranha]

Shades,
Thx for the reply. I am old and slow also, so i guess i can use that as an excuse. What would be the alternative if its not a numerical value. Its summing numbers & the number thats returned from both can be used in another formula, so wouldn't that be a numerical value?

Howdy. It coerces Excel to recognize it as a numerical value.

Well MWE,
Sherlock is one of my heros,
Sherlock Holmes and Doctor Watson
221b Baker Street
London, England
Fog, Cobblestone streets, Horse dawn carriages, The Hat, The Cloche, The pipe, The magnifying glass, Fuddling Dr Watson.
The whole thing.

I'm particulary fond of the Basil Rathbone black & white films. Watch every one i can find one.

Dave: what is the significance of your Sherlockian reference?

[MWE]

...
Well MWE,
Sherlock is one of my heros,
Sherlock Holmes and Doctor Watson
221b Baker Street
London, England
Fog, Cobblestone streets, Horse dawn carriages, The Hat, The Cloche, The pipe, The magnifying glass, Fuddling Dr Watson.
The whole thing.

I'm particulary fond of the Basil Rathbone black & white films. Watch every one i can find one.

exactly as I hoped. I too have a great interest in The Great Detective. At one time it was close to an obsession; I belonged to a dozen scion orgns; collected everything available; my license plate was KCOLREHS. I even went to England just to visit the "special places". But my interest has mellowed a bit in the last few years and is now more controlled.

Rathbone certainly was an excellent Holmes. Nigel Bruce was entertaining, but not a very faithful portrayal of Dr. Watson. I suspect that those films were influenced by the "hero/sidekick" genre that was so popular in the 30's and 40's. Personally, I like the more recent Dr Watsons in the PBS/BBC series of a few years ago.

[TonyJollans]

Hi Piranha,

Using -- is one of several ways of coercing non-numeric values (e.g. a Boolean True/False - which is what you have with MOD(etc)=1) to numeric ones should it be required. Multiplication should also have the same effect so the -- is overkill in your case.

[Bob Phillips]

Hi Piranha,

Using -- is one of several ways of coercing non-numeric values (e.g. a Boolean True/False - which is what you have with MOD(etc)=1) to numeric ones should it be required. Multiplication should also have the same effect so the -- is overkill in your case.

It is not the double unary that is overkill, it is the *. It is NEVER necessary to coerce a number range.

Piranha, see http://www.xldynamic.com/source/xld.SUMPRODUCT.html

[TonyJollans]

(MOD(COLUMN(NumberRange),2)=1) is an array of Booleans and needs coercing to numeric.

Of course in this particular case, the array of numerics MOD(COLUMN(NumberRange),2) has effectively been coerced to booleans in the first place by the =1.

[Desert Piranha]

Tony,
Thx, i am having trouble getting my head around this simple thingy.
thx for your explanation.

Hi Piranha,
Using -- is one of several ways of coercing non-numeric values (e.g. a Boolean True/False - which is what you have with MOD(etc)=1) to numeric ones should it be required. Multiplication should also have the same effect so the -- is overkill in your case.

xld,
thx for the site and the.

It is not the double unary that is overkill, it is the *. It is NEVER necessary to coerce a number range.

Piranha, see http://www.xldynamic.com/source/xld.SUMPRODUCT.htmlYesterday 09:37 PM

MWE,
Yeah you may be a little possessed
Going to England is something i have only to dream about, Your lucky. I havn't seen this, that you refer to. I will look for them.

I like the more recent Dr Watsons in the PBS/BBC series of a few years ago.

[Bob Phillips]

(MOD(COLUMN(NumberRange),2)=1) is an array of Booleans and needs coercing to numeric.

Exactly, but NumberRange is not an array of booleans, so there is no need to use * to coerce two arrays as * does. Another operator should be used IMO on the conditional test, either --, +0, 0+, 1*,*1,1^,^1, but not the * operator as that is forcing the formula to do something that SUMPRODUCT does anyway, multiplying arrays of numbers, and thence makes SUMPRODUCT redundant. Formulae should be use the capabilities of a function, else why use it, it becomes an overhead?

[TonyJollans]

Sorry, xld, I misinterpreted your post. I know there is much debate about the various ways of doing this. As a rule of thumb I like things to be as obvious as possible over and above other considerations - unfortunately coercion rarely is, no matter how you do it.