Array formula with AND formula

K

Kuede

Hi, I have the following formula, which does not work, since Array and
AND cannot be combined.

{=-SUM(IF($B27='E-front CFs'!$A$8:$A$3285;IF($G$6='E-front
CFs'!$J$8:$J$3285;IF(AND($Y$5<='E-front
CFs'!$D$8:$D$3285;$Y$6>='E-front CFs'!$D$8:$D$3285);'E-front
CFs'!$H$8:$H$3285;0);0);0))}

What this formula tries to do is extracting cash flows from a file
within a certain range of time (IF(AND($Y$5<='E-front
CFs'!$D$8:$D$3285;$Y$6>='E-front CFs'!$D$8:$D$3285). $Y$5 and $Y$6 are
references to two different dates, i.e. 14.06.2006 and 30.06.2006.
Unfortunately I was told that the AND-formula within an Array formula
does not work, but is there a way to get around this problem?

Thanks for helping.

Kuede
 
B

Bob Phillips

=-SUM(IF($B27='E-front CFs'!$A$8:$A$3285;
IF($G$6='E-front CFs'!$J$8:$J$3285;
IF(($Y$5<='E-front CFs'!$D$8:$D$3285)*($Y$6>='E-front CFs'!$D$8:$D$3285);
'E-front CFs'!$H$8:$H$3285;0);0);0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
B

Biff

Hi!

No need for an array.

Normally entered:

=-SUMPRODUCT(--($B27='E-front CFs'!$A$8:$A$3285),--($G$6='E-front
CFs'!$J$8:$J$3285),--($Y$5<='E-front
CFs'!$D$8:$D$3285),--($Y$6>='E-front CFs'!$D$8:$D$3285),'E-front
CFs'!$H$8:$H$3285)

Biff
 
K

Kuede

Thanks a lot Biff. It works, but can you explain it to me, why it works
this way, especially what the "--" are for?

Thanks.

K
 
B

Biff

The Sumproduct function is designed to work with arrays. Each of the arrays:

($B27='E-front CFs'!$A$8:$A$3285)
($G$6='E-front CFs'!$J$8:$J$3285)
($Y$5<='E-front CFs'!$D$8:$D$3285)
($Y$6>='E-front CFs'!$D$8:$D$3285)

Will evaluate and return an array of TRUEs or FALSEs.

The "--" converts TRUE to 1 and FALSE to 0. Then all the arrays are
multiplied and then summed together for the result:

TRUE;FALSE;TRUE;TRUE;30
TRUE;TRUE;TRUE;TRUE;50
TRUE;FALSE;TRUE;FALSE;10

1 * 0 * 1 * 1 * 30 = 0
1 * 1 * 1 * 1 * 50 = 50
1 * 0 * 1 * 0 * 10 = 0

=SUMPRODUCT({0;50;0}) = 50

For more info see:

http://xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html

Biff
 
Top