Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jul 2001
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Add Special Conditions in a COLUMN (Excel 2000)

    hi,
    how can i add a if conditions for all cell in a particular column? for intance,
    column A , range from A1:A20 contains data like OLC, ABC, BSC. i would like excel to replace
    OLC change to GOLD, ABC to SILVER, BSC to OTHERS CLUB.

    pls provide guidance what are the procedure to perform this operations. In fact, the main
    login i need is how to change a range of data in a column to other text.
    like if cell A1 = 1 then GOLD else if A1 = 2 then SILVER

    A thing to note that the data in the column will not fixed to a specific range..
    sometimes from A1:A20... somtimes from A1:A80

    thanks for the help.
    tianlong

  2. #2
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add Special Conditions in a COLUMN (Excel 2000)

    HI THERE
    This formule will work:=IF(TEST="OLC","GOLD",IF(TEST="ABC","SILVER", "OTHERS CLUB"))
    or
    =IF(TEST="OLC","GOLD",IF(TEST="ABC","SILVER",IF(TE ST="BSC","OTHERS CLUB","")))

    The first one will give others club for anything that is not OLC OR ABC.
    The second one will give a blank if anything is not OLD,BSC OR ABC

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Add Special Conditions in a COLUMN (Excel 2000)

    You could set a table with the the values and the replacement text like :

    1 GOLD
    2 SILVER

    etc., and name it say Table and the use a VLOOKUP formula like :
    <pre>=VLOOKUP(A1,Table,2)</pre>


    - Sample file attached

    RENE's formula should also work fine if the column containing the original values is named TEST. The VLOOKUP approach would be better if you intend to use more than 3 values, or if you need to change them a lot.

    Andrew C
    Attached Files Attached Files

Posting Permissions

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