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)

"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

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

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

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

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

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)