ARRAY functions... Please don't say it's true!

[bill]

Had a visit to the following: http://www.j-walk.com/ss/excel/tips/tip74.htm and found a neat article regards using boolean tests in CTRL+SHIFT+ENTER functions BUT, they don't appear to work quite as I planned!

Populate cells a1:a6 with various letters and column b1:b6 with various values

Doing something like:=SUM((OR(A1:A6="b"))*(B1:B6)) will result in the a straight sum of ALL values in column B!

Help!!!!!! Yes, use SUMIF but SUMIF doesn't work good when the criteris is complex!

 

[bill]

Aladin & tactps,

Thanks both for the knowledge.

It does seem that mastering boolean logic in conjunction with the sumproduct can be rewarding.

However, I'm still uncertain how the '--' works.
Easy enough to say -- is multipy the result twice by negative 1, the first converting the result from TRUE/FALSE to -1 or 0, the 2nd the result to be positive.

But assume this please.

A1=123
A2=456
A3=456

=SUMPRODUCT(--(A1:A3=123)+(A1:A3=456)) produce 3
=SUMPRODUCT(-(A1:A3=123)+(A1:A3=456)) produce 1?

 

[bill]

I suppose using a pivot table never entered my thoughts.

In addition to perhaps 10+ result rows that will use a variation of the formula, each of the rows will report perhaps 28 columns.

The rows of 28 columns will be used as basis for graphing by providing 2 years of data in addition to the normal cm, pm, ytd, pytd.

My solution for now will be to use the formula on a row by row basis against the raw query data. Each row will evaluate to either true or false, accordingly.

For my results set, the value of true/false will be factored into an ARRAY function where further filtering will occur, this time avoiding AND/OR. As I build my columns, I'll use the new ARRAY formula against with an OFFSET function so that I can dynamically build each of the columns without hardcoding the range that is returned.

This was my orginal solution however a team memeber turned me on to ARRAY functions and the possibility of using AND OR within it. As I did not know my data well enough and the numbers were quite large, it was easy to believe the results I was getting was legitimate. Compounded my believe that the system worked were the various criteria using spread along 2 year of historical results, each row/column producing a different answer.

 

[Aladin Akyurek]

Aladin & tactps,

Thanks both for the knowledge.

It does seem that mastering boolean logic in conjunction with the sumproduct can be rewarding.

However, I'm still uncertain how the '--' works.
Easy enough to say -- is multipy the result twice by negative 1, the first converting the result from TRUE/FALSE to -1 or 0, the 2nd the result to be positive.

But assume this please.

A1=123
A2=456
A3=456

=SUMPRODUCT(--(A1:A3=123)+(A1:A3=456)) produce 3
=SUMPRODUCT(-(A1:A3=123)+(A1:A3=456)) produce 1?

When + is used, there is no need for an additional coercer like --. The reason is that + is itself a coercer. That is, it is also capable of adding logical values as numbers for TRUE = 1 and FALSE = 0 in Excel by design.

=TRUE+TRUE ==> 1
=TRUE+FALSE ==> 1
=FALSE+FALSE ==> 0

=SUM(--TRUE,--TRUE) ==> 2 etc.

The first - coerces a TRUE to -1 and second -(-TRUE) ==> -(-1) ==> 1

There are some posts on this board which take up this coercion issue.

One thing that is essential to know is that formulas like {=SUM(...)} and SumProduct formulas need numbers to calculate. The conditional terms in such formulas evaluate to arrays of logical values, which cannot be used without coercion. A * in array-formulas works similarly: the multiplication coerces logical values to 1's and 0's.

 

[bill]

Regards this,
=SUMPRODUCT(--(A1:A3=123)+(A1:A3=456)) produce 3
=SUMPRODUCT(-(A1:A3=123)+(A1:A3=456)) produce 1?

assumed both (A1:A3=123)+(A1:A3=456) were enclosed by () as per
=SUMPRODUCT(-((A1:A3=123)+(A1:A3=456)))

It's as clear as rain now.

Thanks again.

 

[Aladin Akyurek]

Regards this,
=SUMPRODUCT(--(A1:A3=123)+(A1:A3=456)) produce 3
=SUMPRODUCT(-(A1:A3=123)+(A1:A3=456)) produce 1?

assumed both (A1:A3=123)+(A1:A3=456) were enclosed by () as per
=SUMPRODUCT(-((A1:A3=123)+(A1:A3=456)))

It's as clear as rain now.

Thanks again.

The second causes substraction because of single -, hence 1. But, I said:

(Range=Cond1)+(Range=Cond2)

does not require --.

 

[PA HS Teacher]

=TRUE+TRUE ==> 1

Glad I read this today. I had been wondering if
Sumproduct(Condition 1 + Condition 2) would double count if both conditions, were true, but apparently not.


In retrospect, it makes sense that booleans would add this way.

 

[Aladin Akyurek]

=TRUE+TRUE ==> 1

Glad I read this today. I had been wondering if
Sumproduct(Condition 1 + Condition 2) would double count if both conditions, were true, but apparently not.


In retrospect, it makes sense that booleans would add this way.

Bill,

I see I have a typo with that TRUE + TRUE bit. The result should be 2, not 1.

Since product numbers are mutually exclusive, that is, multiple product numbers cannot hold at the same for the same record (row), double counting will not occur. This is the very reason of OR'ing or +'ing.