Replace cell value based on a logic statement

Hi and welcome to the forum!

Perhaps create a new array by entering this formula to the right of the first row in your set (I'll assume that's row 1 and from column A to L for the sake of argument) and then copying down and to the right a sufficient number of cells:

=2-(COUNTIF($A1:$L1,A1)>COUNTA($A1:$L1)/2)

By the way, you don't say what the results should be if the counts for "A" and "C" are equal.

Regards

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.

Try this

Formula:

Please Login or Register  to view this content.