How to do an AND with a Mask?

[fred3]

Suppose I have binary data values in a row 1 and suppose I have a mask in row 2:
A B C D E F G H I J
1 1 0 1 1 1 0 0 1 1
2 0 0 0 0 1 1 1 1 1
I want to calculate if the data values are "1" when the mask is "1" and generate a single output "1" if this is the case. Otherwise "0".

In other words, I want to find a simple expression that would generate a "1" or TRUE result if every "1" in the mask is matched by a "1" in the data. If the mask value is "0" then it doesn't affect the result.

 

[Luke M]

This will do what you ask.

=SUMPRODUCT(1*(A2:I2<A3:I3))=0

Formula will be TRUE only if the data equals mask, or mask is 0.

 

[fred3]

OK. Thanks.
Now what if I want something similar that will do this:
If the data matches the mask, 1's OR 0's, I want to get a "1" except
if the mask has a "don't care" symbol then ignore the corresponding cell in the data. (I'm not sure what to select as a "don't care" symbol).

 

[NARAYANK991]

Hi ,

A few questions :

1. How do you want the output ? Do you want a 0/1 in each cell , in a range as wide as the input , or do you want a binary value in one cell ? For example for the same mask and input data you have posted , do you want an output 000010011 in one cell ?

2. If you intend to use a mask , how will it reflect in the output ? Will the output be 1 if the input is 1 and 0 if it is 0 ? Or will the mask mean the output is always 0 or always 1 ?

3. The mask for don't care can be a wild-card character such as a ? or an *

Narayan

 

[fred3]

Ah yes....
I need a single output.
If the mask (or perhaps the data) has "don't care" entries then the data (or mask) corresponding to the "don't care" values does not affect the output.

Yes, I can imagine using ? or * but I'm not sure how to use that in an efficient way.

(Also, I can imagine matching a mask but might also find it useful/efficient to be able to "negate" some values. It probably amounts to using another mask anyway and, if so, then that's easy enough and there's no question about that then... )

Examples:

A B C D E F G H I J OUTPUT
1 MASK 1 0 1 1 1 0 0 1 1
2 DATA1 0 0 0 0 1 1 1 1 1 0
3 DATA2 1 0 1 1 1 0 0 1 1 1
4 DATA3 1 0 1 1 1 0 0 0 1 0
5 DATA4 1 0 1 1 1 0 0 * 1 1

Some negating ideas:
6 MASK 1 0 -1 -1 -1 0 0 1 1
7 DATA5 1 0 1 1 1 0 0 1 1 0
8 DATA6 1 0 0 0 0 0 0 1 1 1

Equivalently / probably likely:
6 MASK 1 0 1 1 1 0 0 1 1
7 DATA5 1 0 1 1 1 0 0 1 1 1
8 DATA6 1 0 0 0 0 0 0 1 1 0

9 MASK 1 0 0 0 0 0 0 1 1
10 DATA5 1 0 1 1 1 0 0 1 1 0
11 DATA6 1 0 0 0 0 0 0 1 1 1

 

[fred3]

It's been a while ... I'm just getting back to this.
I now realize in reading the thread that I wasn't very clear with my second question.
Just to be clear, Luke's answer to the original question worked great and is compact! Just what I wanted. One just has to carefully write down what it does so one doesn't have to re-derive it each visit.
And, in order to get a 1/0 result, I changed it to:
=1*(SUMPRODUCT(1*(DATA<MASK))=0)
maybe there's a better way but this is still compact enough for me!

I don't recall what I was heading toward when I asked about "don't care" states. It seems now that that's already taken care of as MASK=0 always results in a "1". And that's a kind of "don't care... what the data is".

Perhaps as I get into this project again, I'll figure out if there was something else needed.

Narayank991 asked the key question in the thread:

2. If you intend to use a mask , how will it reflect in the output ? Will the output be 1 if the input is 1 and 0 if it is 0 ? Or will the mask mean the output is always 0 or always 1 ?

The direct answer is: If the MASK is "0" then the output will be "1". If the MASK is "1" then the output will be the DATA. So this means that making the a MASK value "0" is like a "don't care". Then the result summary could be generated by ANDing all the individual cell results.

THANKS!

 

[r2c2]

Here is one more formula (of course @Luke M 's formula is simple and easy, no doubt).

{=AND(DATA + NOT(MASK)) }

You can derive this formula by making a truth table for various combinations of Data & Mask.

D M Output
0 0 1
1 0 1
0 1 0
1 1 1

So, output for a single bit will be 0 when M=1 and D=0 and 1 in all other cases.

This is nothing but Output = NOT(NOT (D) AND M)
or simply, D OR NOT(M)

Since we need to perform this operation over a range, we use array AND() on top.

Hope that helps.