Does anyone know a way to tabulate multi-coded data using Excel?
I have a set of variables that I would like to tabulate.
Each contains responses coded from 0 to 9
In some cases the same unit can provide more than one response, i.e. the
variable is multi-coded.
I have coded these with a number made up from the codes given, e.g. 120
means that the unit gave responses 1, 2 and 0.
I now wish to produce tables showing how many times each of the responses
from 0 to 9 were given, i.e.3 entries would be made in the table for a unit
coded 120.
One way of doing this would be to use the FIND function to expand the single
column containing the data to 10 columns with binary responses for the
presence or absence of each code. However this seems laborious. Does
anyone have an easier way, preferably one that would work with the pivot
tables that I am using to tabulate those variables that are single coded?
I have tried COUNTIF( FIND( )) but that does not work. I thought of
using a series of functions like INT(N/10^(n-1))-10*INT(N/10^n) where N is
the number coded and n is a series of numbers from 1 to 10 all combined
together in an OR statement but that also seems too complicated.
James Rothman
[log in to unmask]
|