What does the "+" sign do at the start of a formul

[simsy]

Hi folks...

I have asked a few questions here, and, very occasionaly, been able to offer some help...

I browse a lot without posting, just to read and learn. One thing that I am curious about is the practise/need to put the "+" symbol at the start of a formula, and sometimes the minus sign, "-" is used.

Can someone explain to me what this achieves. When I have seen it used, if it is a formula I can decipher, it usually seems to be redundant to me. Obviously it is doing something... can someone explain what it serves?

Thanks in anticipation,

Regards,

Simsy

 

[Legacy 21301]

Re: What does the "+" sign do at the start of a fo

Excel follows the normal mathematical rules of addition and subtraction.

For example, put in A1 =-5
Then put in B1 =-A1 and put in C1 =+A1 (or =A1)

 

[Aladin Akyurek]

Re: What does the "+" sign do at the start of a fo

Starting a formula with +, if that's what you mean, is the Lotus 1-2-3 way of starting a formula, which Excel understands. Can you give examples with - ?

 

[simsy]

Re: What does the "+" sign do at the start of a fo

Thank you...

" the Lotus 1-2-3 way of starting a formula, which Excel understands"

That now makes sense....

With regard to starting with a "-", I can't give any examples... I just know I've seen then! It is possible, I suppose, that they were just a mathematical "minus", but I'm sure I even saw one beginning with "--" on one occassion!

Anyway, thank you. If I do see any more examples I'll repost here.

Thanks again,

Regards,

Simsy

 

[Mark W.]

Re: What does the "+" sign do at the start of a fo

The unary minus operator can be used twice to coerce an text representation of a numeric value into a numeric value. For example...

=--"65" produces 65

and, so does...

="65"+0

 

[Aladin Akyurek]

Re: What does the "+" sign do at the start of a fo

Thank you...

" the Lotus 1-2-3 way of starting a formula, which Excel understands"

That now makes sense....

With regard to starting with a "-", I can't give any examples... I just know I've seen then! It is possible, I suppose, that they were just a mathematical "minus", but I'm sure I even saw one beginning with "--" on one occassion!

Anyway, thank you. If I do see any more examples I'll repost here.

Thanks again,

Regards,

Simsy

The -- bit is a coercer: That is, it is used in particular to convert the logical values into numbers like in...

=SUMPRODUCT(--(ISNUMBER(MATCH(LEFT(A1:A10),{"X","Y"},0))))

which counts the strings that starts with either an X or a Y in A1:A10. Since the ISNUMBER returns logical values instead of numbers SumProduct needs, it is necessary to invoke coercion. The above can also be written with a different but equivalent coercer, that is, with +0...

=SUMPRODUCT(ISNUMBER(MATCH(LEFT(A1:A10),{"X","Y"},0))+0)

 

[simsy]

Re: What does the "+" sign do at the start of a fo

Thank you Aladin...

If I understand you correctly what you have just written can be simplified as;

"'--' means, treat the boolean value 'True' as the number '1', and the boolean value 'False' as the number '0'"

I thought this was the way Excel would interpret a boolean in a calculation anyway. Is my previous understanding incorrect, or have I misunderstood your explanation?

Thanks in advance,

Regards,

Simsy

 

[Aladin Akyurek]

Re: What does the "+" sign do at the start of a fo

...If I understand you correctly what you have just written can be simplified as;

"'--' means, treat the boolean value 'True' as the number '1', and the boolean value 'False' as the number '0'"

I thought this was the way Excel would interpret a boolean in a calculation anyway. Is my previous understanding incorrect, or have I misunderstood your explanation?...

Yes. 1 and 0 are the values, associated with TRUE and FALSE, respectively. These numeric values come into play only by explicit coercion, that is, they are subjected to arithmetic operators (+,-,*,/,^) and --. something that occurs in most calculations [but not in =IF(A1=2,X,Y), for example].

In fact, it would be nice to have implicit coercion with some of the worksheet functions, like SumProduct...

We could then have...

[1]

=SUMPRODUCT((A1:A10=C1),(B1:B10=D1))

instead of:

[2]

=SUMPRODUCT(--(A1:A10=C1),--(B1:B10=D1))

[3]

=SUMPRODUCT((A1:A10=C1)+0,(B1:B10=D1)+0)

[4]

=SUMPRODUCT((A1:A10=C1)*(B1:B10=D1))

[2] to [3] are all equivalent. As a side note, [3] is faster. [1] would possibly be even faster.

 

[Legacy 21301]

Re: What does the "+" sign do at the start of a fo

Thank you...

" the Lotus 1-2-3 way of starting a formula, which Excel understands"

That now makes sense....

With regard to starting with a "-", I can't give any examples... I just know I've seen then! It is possible, I suppose, that they were just a mathematical "minus", but I'm sure I even saw one beginning with "--" on one occassion!

Anyway, thank you. If I do see any more examples I'll repost here.

Thanks again,

Regards,

Simsy

No formula in Excel starts with anything other than "=" (after the formula has been entered in the cell)