Hi,
Your formula works but in my situation is missing a component because of a detail I didn't provide. In my data set I have values other than A or C. Therefore, if I ran the formula above to this data:
A A A C A
C N A M C
It would put:
1 1 1 2 1
2 2 2 2 2
When I would like for it to look like this:
1 1 1 2 1
1 N 2 M 1
I thought about putting an If statement beginning of your function above but don't know how to get the 'A1= "A" or "C"' to work. This is what I have so far:
=IF(A1=OR("A","C"), (2-(COUNTIF($A1:$E1,A1)>COUNTA($A1:$E1)/2)), A1)
Just an added detail, there are many other letters besides what I have in the example above (M and N) in the array, and of course, this is just a very small subset size of the data I am trying to convert.
I also thought about more about what to do in case count of A and C are equal. I think I will just default to having whatever is in the first cell in the range (A1 for row 1) as "2" and the alternate as "1". The first column will always have A or C and I wouldn't need to worry about other letters.
Bookmarks