What do these markings (--) do in a formula?

[Guest]

The following formula was provided to me a while ago so that i could make the
following statement happen for a monthly cash flow...take the month i input
into a cell and then find the corresponding month in another row and then
return the sum of the next 12 months of cash flow. for example, if i entered
12 into the cell, the formula would then go to the cash flow page and find
the corresponding month and then sum cash flows for months 13-24 (the next 12
months). The formula works great, but I have never seen the -- function and
would like to know what it does...thanks!

=SUMPRODUCT(--('Rental Income
Asumptions'!D15:EE15>=Summary!E57+1),--('Rental Income
Asumptions'!D15:EE15<=Summary!E57+12),'Rental Income Asumptions'!D16:EE16)

 

[JE McGimpsey]

See

http://www.mcgimpsey.com/excel/doubleneg.html

 

[Guest]

The -- changes the range to which it applied into 0s and 1s. When a
condition is TRUE you get a 1, and when it is false you get a 0. Therefore,
if both conditions are TRUE, then you have a record (1 * 1 = 1). If one or
both conditions are FALSE then you don't have a record (0 * 0 = 0, 1 * 0 = 0,
0 * 1 = 0).

There is a great link that explains this in more detail, except I can't find
it. Perhaps someone else can.

Dave

 

[Beege]

The following formula was provided to me a while ago so that i could make the
following statement happen for a monthly cash flow...take the month i input
into a cell and then find the corresponding month in another row and then
return the sum of the next 12 months of cash flow. for example, if i entered
12 into the cell, the formula would then go to the cash flow page and find
the corresponding month and then sum cash flows for months 13-24 (the next 12
months). The formula works great, but I have never seen the -- function and
would like to know what it does...thanks!

=SUMPRODUCT(--('Rental Income
Asumptions'!D15:EE15>=Summary!E57+1),--('Rental Income
Asumptions'!D15:EE15<=Summary!E57+12),'Rental Income Asumptions'!D16:EE16)

RLind,

I believe ANY mathematical operation turns a true/false answer into a
Numeric answer. True is the same as 1, false is 0 ( like on is 1 and 0
is off, bytewise)

The"--" is actually multiplying the result by negative one, twice. You'd
get the same result if you multiplied by 1, or added 0, but I think
looking at more letters and operands sometimes confuses the user,
especially with longer formulae.

But I'm certainly not an expert.

Beege

 

[Guest]

Thanks!

 

[Bob Phillips]

The -- changes the range to which it applied into 0s and 1s. When a
condition is TRUE you get a 1, and when it is false you get a 0.
Therefore,
if both conditions are TRUE, then you have a record (1 * 1 = 1). If one
or
both conditions are FALSE then you don't have a record (0 * 0 = 0, 1 * 0 =
0,
0 * 1 = 0).

There is a great link that explains this in more detail, except I can't
find
it. Perhaps someone else can.

http://xldynamic.com/source/xld.SUMPRODUCT.html

 

[Guest]

Multiplying by negative one twice is the same as multiplying by positive one
once, which seems the simpler explanation.

In any event, the -- simply creates a binary code out of the TRUE or FALSE
alternative. SUMPRODUCT then adds the 1s together to count the TRUE records.

Dave

 

[Guest]

Yes, that's the link I was thinking of. Thanks.

Dave

 

[Bob Phillips]

Type =--TRUE into a cell, and see what you get. Or =TRUE+0, or =TRUE*1, or
TRUE^1, or =TRUE^0, or =TRUE/1. You should believe <G>

And True is not the same as 1, ANY non-zero value will test to be TRUE, zero
will test to FALSE.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)