Inserting a numerical 1 or 0 into a cell depending on the values of two other cells

Hi,

I'd like help writing a single formula that assigns either a numerical 1 or 0 to a cell based on values from two other cells.

By way of background, I'm working with three columns. Cells in column F contains values from 1 to 4. Cells in column G contain values from 5 to 11. Cells in column H are currently blank.

I need a single formula that will insert either a numerical 1 or a 0 into column H according to the following conditions.

• If F3 = 1 and G3 = 5 or 6 or 7 then let H3=1 otherwise let H3=0
• If F3 = 2 and G3 = 7 or 8 or 9 then let H3=1 otherwise let H3=0
• If F3 = 3 and G3 = 9 or 10 or 11 then let H3=1 otherwise let H3=0
• If F3 = 4 and G3 = 9 or 10 or 11 then let H3=1 otherwise let H3=0

I'd appreciate any help.

Cheers,

Dougal

Here's one way......

=((F3=1)*OR(G3={5,6,7}))+((F3=2)*OR(G3={7,8,9}))+(OR(F3={3,4})*OR(G3={9,10,11}))

I set it up this way which makes it easier to change the formula inputs if you need to.

The formula in column M is:
=IF(AND(F3=N3,OR(G3=I3,G3=J3,G3=K3)),1,0)
this allows a simple copy paste down across all 4 rows.


F G H I J K L M N
criteria formula
1 7 5 6 7 1 1
2 7 7 8 9 1 2
3 9 9 10 11 1 3
4 9 9 10 11 1 4

sorry, it might not be so clear from the representation.
Simnply copy and past it to excel, then use text to column to see each of the items in the relevan columns.
The criteria are columns i to K.
The formula is column M

Just for the fun of it, here are 2 more options:

Alt.1: =SUMPRODUCT((F3={1;2;3;4})*MMULT(--(G3={5\6\7;7\8\9;9\10\11;9\10\11});{1;1;1}))
Alt.2: =--(INDEX(MMULT(--(G3={5\6\7;7\8\9;9\10\11;9\10\11});{1;1;1});F3)=1)
Separators: row = ; col. = \

Alt.1 is more stable
Alt.2 it might just be a coincidence that F3 is 1,2,3,4,...

Ola

Thanks everyone!

Now for the round up...

Formulas that DON'T work (yet!):

• =IF(AND(F3<=4,G3>=5,G3<=11),1,0)

This returns all 1's

• =SUMPRODUCT((F3={1;2;3;4})*MMULT(--(G3={5\6\7;7\8\9;9\10\11;9\10\11});{1;1;1}))

This apparently contains an error.

• =--(INDEX(MMULT(--(G3={5\6\7;7\8\9;9\10\11;9\10\11});{1;1;1});F3)=1)

This also contains an error.

The following DO work (yay!):

• =IF(G90>4,IF(OR(AND(F90=1,G90>=5,G90<=7),AND(F90=2,G90>=7,G90<=9),AND(OR(F90=3,F90=4),G90>=9,G90<=11)),1,0),0)
• =((F3=1)*OR(G3={5,6,7}))+((F3=2)*OR(G3={7,8,9}))+(OR(F3={3,4})*OR(G3={9,10,11}))

Rasonline, thank you also for your suggestion. I haven't tried it as I didn't quite follow but from I did understand it involved creating extra columns which I was trying to avoid. (Have way too many columns for my brain to handle at the moment!)

For the curious, you can try out your formula on the following data set:
3 8 0
2 8 1
2 9 1
1 7 1
1 9 0
1 7 1
4 11 1

Going from left to right, the first column is column F, the second is column G while the third, column H, are the values your formula should produce.

Thanks again everyone. You've saved me heaps of time and just made the life of a graduate student that little bit happier

Cheers,

Dougal

Dougal,

Thanks for taking the time to test and comment on all the suggestions made, and to thank all the contributors

The problem you had is related to how different countries separate row and columns.
Where I come from we separate rows by ; and columns by \. Decimals are separated by , and thousands are separated by blanks. I think in Australia you use the same as the US where they separate rows by , and columns by ;. Decimals are separated by . and thousands by , (unless I'm mistaken).

I figured you would have more rows coming. Here is the new formula:

=--(INDEX(MMULT(--(G19=L19:N25),{1,1,1}),F19)=1)

Just to be on the safe side, I've attached the file with the formulas.

Hope it can be useful
Ola

Hi Olasa,

Thanks for taking the time to respond. I've had a look at your post and the Excel file attachment and I have to admit that I don't understand your approach. I'm pretty sure this is a lack of Excel expertise on my part since you appear to know your stuff. I'll look forward to spending some time to look at your approach some time in the future. For the moment, I better get back to analysing this data!

Thanks again.

Cheers,

Dougal

Thanks for the response.
I made a quick formula breakdown for that 'some time in the future'.
Same break down as in this blue box is enclosed in the attached file.

HTH
Ola

Case:
Condition1 =2
Condition2 =10

Steps in the solution, starting from the middle of the formula
Condition2:
Step1:
FALSE FALSE FALSE
FALSE FALSE FALSE
FALSE TRUE FALSE
FALSE TRUE FALSE

Step2: "--" will just change False to 0 and True to 1
0 0 0
0 0 0
0 1 0
0 1 0

Step3:
0 matrix multiply each row by {1,1,1}
0 (and sum each row in the matrix)
1
1

Condition 1:
Step4:
0 find what is in row No 2

Result:
Step5:
0