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

R

RLind

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

Dave F

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
 
B

Beege

RLind said:
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
 
B

Bob Phillips

Dave F said:
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
 
D

Dave F

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
 
B

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top