IF command Help

[bionerd]

Hello,

I’m attempting write a funky IF command to construct the matrix below and I’m stuck. This is what I'm trying to achieve. I know it's a combination of IF and perhaps the RIGHT functions, but I can't seem to put it all together correctly...

	Species 1 (1)	Species 2 (10)	Species 3 (100)	Species 4 (1000)	Species 5 (10000
1011	1	1	0	1	0
10010	0	1	0	0	1
11010	0	1	0	1	1
101	1	0	1	0	0
100	0	0	1	0	0
11010	0	1	0	1	1

<tbody>
</tbody>

Any help is much appreciated. The far left column and top row are what I currently have in my spreadsheet. I'm just trying to figure out what I need to put in the cells to achieve the 1/0 matrix.

 

[FDibbins]

Perhaps you could explain how you are populating the table? (It may be obvious, but I cant see it)

 

[bionerd]

	Species 1 (1)	Species 2 (10)	Species 3 (100)	Species 4 (1000)	Species 5 (10000
1011
10010
11010
101
100
11010

<tbody>
</tbody>

This is my current working table. I'm trying to fill it with a command that does something like this:

Here would be an example for the formula in cell B2:
=IF(first character from the right is "1", 1, 0)

Then C2 would be...
=IF(second character from the right is "1", 1, 0)D2
=IF(third character from the right is "1", 1, 0)E2
=IF(fourth character from the right is "1", 1, 0)D2
=IF(fifth character from the right is "1", 1, 0)



Then I would drag the formulas down to complete the table.

 

[bionerd]

	Species 1 (1)	Species 2 (10)	Species 3 (100)	Species 4 (1000)	Species 5 (10000
1011
10010
11010
101
100
11010

<tbody>
</tbody>

This is my current working table. I'm trying to fill it with a command that does something like this:

Here would be an example for the formula in cell B2:
=IF(first character from the right is "1", 1, 0)

Then C2 would be...
=IF(second character from the right is "1", 1, 0)

D2
=IF(third character from the right is "1", 1, 0)

E2
=IF(fourth character from the right is "1", 1, 0)

D2
=IF(fifth character from the right is "1", 1, 0)



Then I would drag the formulas down to complete the table.

 

[FDibbins]

Perhaps this? (It doesn't agree with your sample answer in post 1)...

	A​	B​	C​	D​	E​	F​
1​		Species 1	Species 2	Species 3	Species 4	Species 5
2​		-1	-10	-100	-1000	(10000
3​	1011	1	0	1	1
4​	10010	1	0	0	1	0
5​	11010	1	1	0	1	0
6​	101	1	0	1
7​	100	1	0	0
8​	11010	1	1	0	1	0

B3=MID($A3,COLUMN(A1),1)
copied down and across

 

[bionerd]

That'll work. Thanks! I'll just switch the species. Instead of Species 1 -> Species 5 across the top row, it'll be 5->1.

 

[jtakw]

Hi,

This should work with your setup:

Excel 2010
	A	B	C	D	E	F
1		Species 1	Species 2	Species 3	Species 4	Species 5
2		(1)	(10)	(100)	(1000)	(10000)
3	1011	1	1	0	1	0
4	10010	0	1	0	0	1
5	11010	0	1	0	1	1
6	101	1	0	1	0	0
7	100	0	0	1	0	0
8	11010	0	1	0	1	1
Sheet1

Cell Formulas
Range	Formula
B3	=IF(LEN(B$2)-2>LEN($A3),0,LEFT(RIGHT($A3,LEN(B$2)-2),1))

Copy B3 formula down and across.