+ Reply to Thread
Results 1 to 6 of 6

multiple numeric values applied to single text

  1. #1
    Fam via OfficeKB.com
    Guest

    multiple numeric values applied to single text

    Hello again guys & gals
    I have several loan code for example 1,2,3,4,5,6,7,8,9,10,11,12 and so on.
    First five code translate as "consumer" (1,2,3,4,5) and 6,7 may be translate
    as "commercial", 8,9,10 may be "residential" and so on.
    I just have loan codes coming to me in one coulmn and they are in thousands.
    So far, I am going line by line and write the loan category according to it
    numric number.Is there any formula that I can apply and dgar it down to get
    the text value according to the loan code?
    Help will be appreciated
    Fam

    --
    Message posted via http://www.officekb.com

  2. #2
    Forum Contributor EdMac's Avatar
    Join Date
    01-23-2006
    Location
    Exeter, UK
    MS-Off Ver
    2003
    Posts
    1,264
    Should not be a problem.

    You need to have a column where you would enter something along the lines of
    =IF(B2<5,"Residential",IF(AND(B2=>5,B5<10),"commercial"))

    Where B2 has the source code. Enter it in one cell then copy down the column

    You can then expand this to however many categories you have up to 7

    Ed

  3. #3
    Fam via OfficeKB.com
    Guest

    Re: multiple numeric values applied to single text

    Thanks for the response Ed.
    The proble is that the numeric value I post is just an example whereas in
    reality the numeric values are not in any order. I was looking for something
    like =if (b2=23,20,21,19,"Consumer",if(b2=18,17,57,81,"Commercial.........
    I hope I able to eloborate my question.

    EdMac wrote:
    >Should not be a problem.
    >
    >You need to have a column where you would enter something along the
    >lines of
    >=IF(B2<5,"Residential",IF(AND(B2=>5,B5<10),"commercial"))
    >
    >Where B2 has the source code. Enter it in one cell then copy down the
    >column
    >
    >You can then expand this to however many categories you have up to 7
    >
    >Ed
    >


    --
    Message posted via http://www.officekb.com

  4. #4
    Forum Contributor EdMac's Avatar
    Join Date
    01-23-2006
    Location
    Exeter, UK
    MS-Off Ver
    2003
    Posts
    1,264
    OK

    =If(or(B2=1,B2=22,B2=13),"Residential",if(or(B2=45.....etc),"Commercial"

    Does this help

    Ed

  5. #5
    vezerid
    Guest

    Re: multiple numeric values applied to single text

    If there is a whole group of numbers associating with Consumer,
    Commercial etc, you are better off creating a sequence of lists
    elsewhere with the numbers pertaining to each classification. In
    particular you can put the classifications as headers in a table of
    rows with unequal lengths. Say they occupy columns F:H, with F1:H1
    containing the headers, i.e. the classifications. Thus 3 different
    outcomes.

    The following *array* formula will then return the classification:

    =INDEX(F1:H1,MIN(ROW(1:3)*IF(COUNTIF(OFFSET(F:F,0,ROW(1:3)-1),A2)=1,1,10)))

    *Array Formula* : Needs to be entered with Shift+Ctrl+Enter.

    HTH
    Kostis Vezerides


  6. #6
    Fam via OfficeKB.com
    Guest

    Re: multiple numeric values applied to single text

    Ed thank you so much. It worked.

    EdMac wrote:
    >OK
    >
    >=If(or(B2=1,B2=22,B2=13),"Residential",if(or(B2=45.....etc),"Commercial"
    >
    >Does this help
    >
    >E


    --
    Message posted via http://www.officekb.com

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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