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)

See

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

In article ,
RLind wrote:

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)

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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"RLind" wrote:

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 wrote:
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

Thanks!

"RLind" wrote:

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)

"Dave F" wrote in message
...
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

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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Beege" wrote:

RLind wrote:
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

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

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Bob Phillips" wrote:




"Dave F" wrote in message
...
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

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)



"Beege" wrote in message
...
RLind wrote:
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