Dummy variables

[Izila]

Hi, I wish to create a dummy variable from one of the variables I am analyzing. I have four types of houses in a survey (1 - 4). Data input in that cell is from 1 to 4, integers. now, i want the excel spreadsheet to assign a 1 in a new dummy variable column that correspond to that number or 0 if it does not. I have illustrated this in a table below. I have over 1000 rows to analyse. please help

house	House Type 1	House Type2	House Type3	House Type4
4	0	0	0	1
3	0	0	1	0

 

[sandy666]

Book1
	B	C	D	E	F
3	house	House Type1	House Type2	House Type3	House Type4
4	4	0	0	0	1
5	3	0	0	1	0
Sheet5

Cell Formulas
Range	Formula
C4:F5	C4	=IF(--RIGHT(C$3)=$B4,1,0)

 

[Izila]

Book1
	B	C	D	E	F
3	house	House Type1	House Type2	House Type3	House Type4
4	4	0	0	0	1
5	3	0	0	1	0
Sheet5

Cell Formulas
Range	Formula
C4:F5	C4	=IF(--RIGHT(C$3)=$B4,1,0)

once again thank you very much sandy666 (LEGEND)

 

[sandy666]

You are welcome
Thanks for the feedback

or simpler: =--(--RIGHT(C$3)=$B4)

 

[Peter_SSs]

or simpler: =--(--RIGHT(C$3)=$B4)

I was just drafting something along similar lines - see below.

Since you are using 1 and 0 as the results, you don't really need the IF function.

Also, if you were to number the columns, say above, like I have in the bottom section, the formula becomes even simpler and it wouldn't matter if the house descriptions did not have the number on the end.

20 08 15.xlsm
	B	C	D	E	F
2
3		House Type1	House Type2	House Type3	House Type4
4	4	0	0	0	1
5	3	0	0	1	0
6
7
8		1	2	3	4
9		Small	Medium	Large	Mansion
10	4	0	0	0	1
11	3	0	0	1	0
0 v 1

Cell Formulas
Range	Formula
C4:F5	C4	=--(RIGHT(C$3)-$B4=0)
C10:F11	C10	=--($B10=C$8)

 

[sandy666]

Since you are using 1 and 0 as the results, you don't really need the IF function.

that is why I added

or simpler: =--(--RIGHT(C$3)=$B4)

in post#4

 

[Izila]

I was just drafting something along similar lines - see below.

Since you are using 1 and 0 as the results, you don't really need the IF function.

Also, if you were to number the columns, say above, like I have in the bottom section, the formula becomes even simpler and it wouldn't matter if the house descriptions did not have the number on the end.

20 08 15.xlsm
	B	C	D	E	F
2
3		House Type1	House Type2	House Type3	House Type4
4	4	0	0	0	1
5	3	0	0	1	0
6
7
8		1	2	3	4
9		Small	Medium	Large	Mansion
10	4	0	0	0	1
11	3	0	0	1	0
0 v 1

Cell Formulas
Range	Formula
C4:F5	C4	=--(RIGHT(C$3)-$B4=0)
C10:F11	C10	=--($B10=C$8)

thank you very much for your help peter

 

[Peter_SSs]

that is why I added

Yeah, I was drafting post 5 while you posted 4 & I had already drafted most of my post including that sentence you quoted. Then I saw your post & that is why I added ..

I was just drafting something along similar lines

.. before I clicked Post.

thank you very much for your help peter

You are very welcome.