multiple numeric values applied to single text

  • Thread starter Fam via OfficeKB.com
  • Start date
F

Fam via OfficeKB.com

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
 
E

EdMac

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
 
F

Fam via OfficeKB.com

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.
 
E

EdMac

OK

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

Does this help

E
 
V

vezerid

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
 
Top