# Replace cell value based on a logic statement

1. ## Replace cell value based on a logic statement

Hi,

I tried searching but couldn't find a relevant thread. I have data where each row has different number of cells containing "A"s and "C"s. For example, row 1 may have 10 "A"s and 2 "C"s. Row 2 may have 5 "A"s and 8 "C"s. A simple CountIf statement can tell me how many A's and C's I have for each row.

Based on this information, I want to replace the "A"s and "C"s cell value with "1"s and "2"s. Only, here I want the "1"s to be the whatever has the highest count and the "2"s be the other. So going with the example above, row one will replace the "A"s with 1 and the "C"s with 2 whereas row two will replace the "A"s with 2 and the "C"s with 1.

It doesn't have to replace the cell value but instead put "1" or "2" into a new cell as long as I can do it for each of the cells in the row (Range).

2. ## Re: Replace cell value based on a logic statement

I'm not really an excel master, but there is a "Find and Replace" option.. It's located in the "Ctrl + F" pop-up.

I know you plan on making it in a formula, and I don't know how that would be possible, but a macro could work..

3. ## Re: 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

4. ## Re: Replace cell value based on a logic statement

Hi,

Thanks for the responses. I will try out your formula. Yeah, I was thinking about what to do in the case of equal As and Cs too as I was writing out the question. In such cases, I don't think it would matter which way it goes but will need to think more on this.

Thanks again.

5. ## Re: Replace cell value based on a logic statement

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.

6. ## Re: Replace cell value based on a logic statement

Try this
Formula:
`Please Login or Register  to view this content.`

There are currently 1 users browsing this thread. (0 members and 1 guests)