IF formula in Excel / Replace numbers with 'words'

[Emsmaps]

Hey there,

I got the following table: see below

Now what I want to do, is to replace the numbers with words.

In words this would look something like this

FOR ROW Q:

If Value'Q' = 1 replace by A
If Value'Q' = 2 replace by B

FOR ROW R:

If Value'Q' = 1 and Value 'R' = 1 replace by aa
If Value'Q' = 1 and Value 'R' = 2 repalce by aa'
If Value'Q' = 2 and Value 'R' = 1 replace by bb

etc ...

I hope this explains it.

Cheers and thx a lot.

Philipp


+-------------------------------------------------------------------+
|Filename: TABLE1.JPG |
|Download: http://www.excelforum.com/attachment.php?postid=4599 |
+-------------------------------------------------------------------+

 

[Bryan Hessey]

I guess the two difficulties are that 1) Excel doesn't replace other
cell values unless you use VB code, and 2) in a 2 part test, if
Value'Q' were replaced then value R would fail the test because value Q
was no longer a 1 or 2, therefore the test is failed, therefore the Q
value should not have been changed (or something like that)

So, instead of 'changing' the values, I would use a second sheet and in
cell A1 put:

=IF(Sheet1!A1="","",Sheet1!A1)

and formula-drag that across and down to cover all required cells.

This would give me an active copy. (ie, if you change Sheet1 it will be
reflected here)

Then, for the required Q row, in cell Q1 use

=IF(Sheet1!Q1="","",if(Sheet1!Q1=1,"A",Sheet1!Q1))

and formula drag that across for the number of cells required to be
changed,

and for row R, in cell R1 put


=IF(Sheet1!R1="","",IF(AND(Sheet1!Q1=1,Sheet1!R1=1),"aa",IF(AND(Sheet1!Q1=1,Sheet1!R1=2),"aa'",IF(AND(Sheet1!Q1=2,Sheet1!R1=1),"bb",Sheet1!R1))))

and formula drag that down to cover all required cells.

This should provide the look you require.

-Note, when you copy and paste these formula, watch for inserted spaces
and remove them. -