+ Reply to Thread
Results 1 to 6 of 6

Replace cell value based on a logic statement

  1. #1
    Registered User
    Join Date
    01-16-2014
    Location
    Davis, CA
    MS-Off Ver
    Excel 2007
    Posts
    3

    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. #2
    Registered User
    Join Date
    01-17-2014
    Location
    America
    MS-Off Ver
    Excel 2007
    Posts
    27

    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. #3
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    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
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  4. #4
    Registered User
    Join Date
    01-16-2014
    Location
    Davis, CA
    MS-Off Ver
    Excel 2007
    Posts
    3

    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. #5
    Registered User
    Join Date
    01-16-2014
    Location
    Davis, CA
    MS-Off Ver
    Excel 2007
    Posts
    3

    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.
    Last edited by spujr; 01-17-2014 at 03:49 PM.

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Replace cell value based on a logic statement

    Try this
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by newdoverman; 01-17-2014 at 05:02 PM.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Splitting a Cell based on "/" and adding new Rows based on Part Number Logic
    By NeedForExcel in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 03-30-2013, 10:11 AM
  2. Text in a single cell based on logic of multiple other cells
    By dtrimble in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-12-2013, 01:33 AM
  3. IF Statement: Replace Cell with True Value
    By firehousetk in forum Excel General
    Replies: 5
    Last Post: 11-01-2011, 04:44 PM
  4. Macro logic: extract higher/lower values based on Unique Cell
    By ExcelQuestFL in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-13-2009, 03:11 AM
  5. Help: Logic for reading/writing cell values based on dual combobox selections
    By MyUserName in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-03-2006, 09:16 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1