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...


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Species 1
(1)[/TD]
[TD]Species 2
(10)[/TD]
[TD]Species 3
(100)[/TD]
[TD]Species 4
(1000)[/TD]
[TD]Species 5
(10000[/TD]
[/TR]
[TR]
[TD]1011[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]10010[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]11010[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]11010[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


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]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Species 1
(1)[/TD]
[TD]Species 2
(10)[/TD]
[TD]Species 3
(100)[/TD]
[TD]Species 4
(1000)[/TD]
[TD]Species 5
(10000[/TD]
[/TR]
[TR]
[TD]1011[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Species 1
(1)[/TD]
[TD]Species 2
(10)[/TD]
[TD]Species 3
(100)[/TD]
[TD]Species 4
(1000)[/TD]
[TD]Species 5
(10000[/TD]
[/TR]
[TR]
[TD]1011[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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)...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td][/td][td]Species 1[/td][td]Species 2[/td][td]Species 3[/td][td]Species 4[/td][td]Species 5[/td][/tr]

[tr][td]

2​

[/td][td][/td][td]-1[/td][td]-10[/td][td]-100[/td][td]-1000[/td][td](10000[/td][/tr]

[tr][td]

3​

[/td][td]1011[/td][td]1[/td][td]0[/td][td]1[/td][td]1[/td][td][/td][/tr]

[tr][td]

4​

[/td][td]10010[/td][td]1[/td][td]0[/td][td]0[/td][td]1[/td][td]0[/td][/tr]

[tr][td]

5​

[/td][td]11010[/td][td]1[/td][td]1[/td][td]0[/td][td]1[/td][td]0[/td][/tr]

[tr][td]

6​

[/td][td]101[/td][td]1[/td][td]0[/td][td]1[/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]100[/td][td]1[/td][td]0[/td][td]0[/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]11010[/td][td]1[/td][td]1[/td][td]0[/td][td]1[/td][td]0[/td][/tr]
[/table]
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.