For some reason I can't figure this one out. What's the most elegant way
to sum up a list of numbers according to a binary truth table? To start
with, here's an example of the raw data:

C B A Combo Numbers
N N N None 42
N N Y A only 23
N Y N B only 16
N Y Y A & B 26
Y N N C only 20
Y N Y A & C 11
Y Y N B & C 10
Y Y Y All 51

But now I want to add up all the rows in which, e.g. B has any part,
like so:

C B A Combo Numbers
N N Y A any 23+26+11+51=111
N Y N B any 16+26+10+51=103
N Y Y A & B 26+51=77
Y N N C any 20+11+10+51=92
Y N Y A & C 11+51=62
Y Y N B & C 10+51=61
Y Y Y All 51

I thought this would be easy, but I can't get my head around it. I'm
willing to change Y/N to binary TRUE/FALSE or 1/0 if it will make an
elegant function, but what function will best read the list above to
make the list below?

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

The SUMPRODUCT function should work well he

For just A, use:

=SUMPRODUCT(--(A1:A7="Y"),E1:E7)

For A and B, use:

=SUMPRODUCT(--(A1:A7="Y"),--(B1:B7="Y"),E1:E7)

For all 3, use:

=SUMPRODUCT(--(A1:A7="Y"),--(B1:B7="Y"),--(C1:C7="Y"),E1:E7)

Adjust these accordingly for whatever combination you need.

HTH,
Elkar


"Del Cotter" wrote:


For some reason I can't figure this one out. What's the most elegant way
to sum up a list of numbers according to a binary truth table? To start
with, here's an example of the raw data:

C B A Combo Numbers
N N N None 42
N N Y A only 23
N Y N B only 16
N Y Y A & B 26
Y N N C only 20
Y N Y A & C 11
Y Y N B & C 10
Y Y Y All 51

But now I want to add up all the rows in which, e.g. B has any part,
like so:

C B A Combo Numbers
N N Y A any 23+26+11+51=111
N Y N B any 16+26+10+51=103
N Y Y A & B 26+51=77
Y N N C any 20+11+10+51=92
Y N Y A & C 11+51=62
Y Y N B & C 10+51=61
Y Y Y All 51

I thought this would be easy, but I can't get my head around it. I'm
willing to change Y/N to binary TRUE/FALSE or 1/0 if it will make an
elegant function, but what function will best read the list above to
make the list below?

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

B only

=SUMIF(B:B,"B",D:D)

which assumes that B's Ys are in column B, the values in column D

A&B

=SUMPRODUCT(--(B1:B100="Y"),--(C1:C100="Y"),D1:D100)
--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Del Cotter" wrote in message
...

For some reason I can't figure this one out. What's the most elegant way
to sum up a list of numbers according to a binary truth table? To start
with, here's an example of the raw data:

C B A Combo Numbers
N N N None 42
N N Y A only 23
N Y N B only 16
N Y Y A & B 26
Y N N C only 20
Y N Y A & C 11
Y Y N B & C 10
Y Y Y All 51

But now I want to add up all the rows in which, e.g. B has any part, like
so:

C B A Combo Numbers
N N Y A any 23+26+11+51=111
N Y N B any 16+26+10+51=103
N Y Y A & B 26+51=77
Y N N C any 20+11+10+51=92
Y N Y A & C 11+51=62
Y Y N B & C 10+51=61
Y Y Y All 51

I thought this would be easy, but I can't get my head around it. I'm
willing to change Y/N to binary TRUE/FALSE or 1/0 if it will make an
elegant function, but what function will best read the list above to make
the list below?

--
Del Cotter
NB Personal replies to this post will send email to
,
which goes to a spam folder-- please send your email to del3 instead.

On Tue, 2 Oct 2007, in microsoft.public.excel.worksheet.functions,
Bob Phillips said:

B only

=SUMIF(B:B,"B",D:D)

which assumes that B's Ys are in column B, the values in column D

A&B

=SUMPRODUCT(--(B1:B100="Y"),--(C1:C100="Y"),D1:D100)

Yes, I'm quite capable of applying a custom solution by hand for each
row, using my eye and human judgment to decide which function applies to
which row. I think you and Elkar missed the point of "elegant".

SUMPRODUCT or some array function with curly brackets feels like it
should be the way to go, but I was surprised I wasn't able to see my way
toward such a function. The ideal winner would be trivially simple to
modify for a table of four columns of Yes/No, having sixteen rows, and
so forth.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

Then I think you really need to explain what you mean by elegant as we are
not mind readers.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Del Cotter" wrote in message
...
On Tue, 2 Oct 2007, in microsoft.public.excel.worksheet.functions,
Bob Phillips said:

B only

=SUMIF(B:B,"B",D:D)

which assumes that B's Ys are in column B, the values in column D

A&B

=SUMPRODUCT(--(B1:B100="Y"),--(C1:C100="Y"),D1:D100)

Yes, I'm quite capable of applying a custom solution by hand for each row,
using my eye and human judgment to decide which function applies to which
row. I think you and Elkar missed the point of "elegant".

SUMPRODUCT or some array function with curly brackets feels like it should
be the way to go, but I was surprised I wasn't able to see my way toward
such a function. The ideal winner would be trivially simple to modify for
a table of four columns of Yes/No, having sixteen rows, and so forth.

--
Del Cotter
NB Personal replies to this post will send email to
,
which goes to a spam folder-- please send your email to del3 instead.

On Wed, 3 Oct 2007, in microsoft.public.excel.worksheet.functions,
Bob Phillips said:
Then I think you really need to explain what you mean by elegant as we are
not mind readers.

1) Having written the function for the first row, you should be able to
copy it down to the remaining six or seven, and have it work for those
rows too.

2) For bonus elegance points, a simple hand modification should make it
work for 2 columns * 4 rows, 4 columns * 16 rows, or 5 columns * 32
rows. But 1) is the feature I'm really looking for. If the solution
only works for 3 columns * 8 rows, then so be it.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

It is confusing that in your second table you are using N to indicate
"don't care" - I have assumed that you would leave it blank for this
and use N and Y for exact matches. I put your table including headings
in A1:E9, and used 1's and 0s instead of "Y" and "N". Then, allowing
space for you to make it 4 variables, I put C, B and A as headings in
A19:C19.

I made use of 3 helper columns as follows:

F20: =IF(A20="","(1)",IF(A20=0,"(A2:A9=0)","(A2:A9=1)") )
G20: =IF(B20="","(1)",IF(B20=0,"(B2:B9=0)","(B2:B9=1)") )
H20: =IF(C20="","(1)",IF(C20=0,"(C2:C9=0)","(C2:C9=1)") )

These make up the constituent parts of an SP formula, but before
setting that up I added this User-defined function:

Function eval(func As String)
Application.Volatile
eval = Evaluate(func)
End Function

Then in K20 I added this:

=eval("sumproduct("&F20&"*"&G20&"*"&H20&"*(E2:E9)) ")

I copied F20:K20 down a few rows and put some values in A20:C20
onwards - I got the following results:

0 <blank 1 49
<blank 1 <blank 103
<blank 1 1 77

so it seems to do its job. If you only wanted one formula then you can
substitute the formulae from the helper columns into the formula in
K20, but it would become difficult to maintain. If you have more
variables (and thus more rows), you would have to change the ranges in
F20:H20 (using Find & Replace would be easiest - change 9 to 17 etc)
as well as having a new formula in I20 and incorporating this into
K20.

I'm not sure if this is "elegant", but hope it helps.

Pete




On Oct 3, 1:39 pm, Del Cotter wrote:
On Wed, 3 Oct 2007, in microsoft.public.excel.worksheet.functions,
Bob Phillips said:

Then I think you really need to explain what you mean by elegant as we are
not mind readers.

1) Having written the function for the first row, you should be able to
copy it down to the remaining six or seven, and have it work for those
rows too.

2) For bonus elegance points, a simple hand modification should make it
work for 2 columns * 4 rows, 4 columns * 16 rows, or 5 columns * 32
rows. But 1) is the feature I'm really looking for. If the solution
only works for 3 columns * 8 rows, then so be it.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.