How can I replace a CELL with an IF statement containing that same cell?

IF my A column has the following cells:

$B$2
$D$3
$F$9
$B$4
.
.
.
(2000 such values)

How can I get my B Column to be:


=IF($B$2=0,"NA",IF($B$2=1,"excellent",IF($B$2=2,"Good",IF($B$2=3,"Fair",IF($B$2=4,"Poor",IF($B$2=5,"Very Poor"))))))
=IF($D$3=0,"NA",IF($D$3=1,"excellent",IF($D$3=2,"Good",IF($D$3=3,"Fair",IF($D$3=4,"Poor",IF($D$3=5,"Very Poor"))))))
=IF($F$9=0,"NA",IF($F$9=1,"excellent",IF($F$9=2,"Good",IF($F$9=3,"Fair",IF($F$9=4,"Poor",IF($F$9=5,"Very Poor"))))))
=IF($B$4=0,"NA",IF($B$4=1,"excellent",IF($B$4=2,"Good",IF($B$4=3,"Fair",IF($B$4=4,"Poor",IF($B$4=5,"Very Poor"))))))
.
.
.
(2000 such values)



How can I do this without manually going through each cell? Can I use replace?

I tried replacing :

" $*$* "

with

" =IF($*$*=0,"NA",IF($*$*=1,"excellent",IF($*$*=2,"Good",IF($*$*=3,"Fair",IF($*$*=4,"Poor",IF($*$*=5,"Very Poor")))))) "

But it doesnt seem to know what I'm trying to do. It wont associate each pair of wildcard *'s .

Any help on this would be greatly appreciated!

Thanks!

-Panos-

Can you upload a sample file showing what you're trying to accomplish?

Can't you write your B column to say
=IF(A1=0,"NA",IF($B$2=1,"excellent",IF($B$2=2,"Good",IF($B$2=3,"Fair",IF($B$2=4,"Poor",IF($B$2=5,"Very Poor"))))))
and copy that down, seeing as the A column is already referencing the other cell?

Hope that helps.

-Z

My B column in my example shouldn't correlate to the $B$2 . Instead of $B$* lets say its $Q$*. I will be copying this all later into a separate excel file later to arrange data.

In the proposed file (with A and B being arbitrary columns I chose) am trying to replace the cell values : $Q$2 and the other $(Letter)$* in column A with their associated IF statements I proposed in column B. Each $(Letter)$* will be therefore associated with an individual IF statement containing that $(Letter)$*.

Example :

A1 : $Q$2
A2 : $D$3
A4 : $F$9
A5 : $Q$4

What I want to automate

B1 : =IF($Q$2=0,"NA",IF($Q$2=1,"excellent",IF($B$2=2,"Good",IF($Q$2=3,"Fair",IF($Q$2=4,"Poor",IF($Q$2=5,"Very Poor"))))))
B2 : =IF($D$3=0,"NA",IF($Q$2=1,"excellent",IF($D$3=2,"Good",IF($D$3=3,"Fair",IF($D$3=4,"Poor",IF($D$3=5,"Very Poor"))))))
B3 : etc
B4 : etc
B5 : etc

When I copy this new column B into my data file it will be pasted in an arbitrary position on the sheet (where Q,D,F are) and sort the data accordingly.

Originally Posted by Speshul

Try indirect, and a choose will make the formula shorter!

=IFERROR(CHOOSE(INDIRECT(A1),"Excellent","Good","Fair","Poor","Very Poor"),"NA")

Im not quite sure how to use this.. Can you make column B have the associated IF statements? How can I upload an excel file onto here?

All I need is column B. When I suggested replacing, i meant replacing column A with B. All I need is a list of the IF statements (column B) .

Can anyone help? This is still unsolved

test.xlsx

Thanks for the reply! But I need column B's cells to assosiate with column A's cells. They arent in any specific order (column A is made up of random $(Letter)$* cells) , But can I autofill by dragging down? Try to make all of column B into what I askd, in my uploaded file! Thanks

What I need is a list like the following (the actual charaters in each line (3 in example), not just the values).


Need the following text in full:

=IF($D$13=0,"NA",IF($D$13=1,"excellent",IF($D$13=2,"Good",IF($D$13=3,"Fair",IF($D$13=4,"Poor",IF($D$13=5,"Very Poor"))))))
=IF($D$18=0,"NA",IF($D$18=1,"excellent",IF($D$18=2,"Good",IF($D$18=3,"Fair",IF($D$18=4,"Poor",IF($D$18=5,"Very Poor"))))))
=IF($D$21=0,"NA",IF($D$21=1,"excellent",IF($D$21=2,"Good",IF($D$21=3,"Fair",IF($D$21=4,"Poor",IF($D$21=5,"Very Poor"))))))
.
.
.
etc

This new coloumn will then be copied over to my raw data file, and convert my numbers to words, regardless of where I paste it! My problem is I have many files of such data and dont want to have to do anything but paste once per file!

Is there any way to actually obtain these?

Actually I can work backwards with what you provided! It might just work! Can you modify the function to have ISBLANK? If it is blank I want it to not show any value (blank) not NA. (this is to distinguish between zero and blank).

Ignore that file ! Ill update it!

Heres the updated file you asked for!

testhelp2.xlsx

Thanks for your help!

I get #REF in cell A5 when I did what you asked. problem.xlsx


How could I write a macro to do such a thing? It is the text im after! This is only because im going to later use this with other excel files where B1-B* for example arent alligned with the proper 1-5 : "word" designation.

Oh I see! I just dont know how this helps my problem because im still going to have to go into each individual formula and substititue $D$13 for example instead of B1 because this is the position in the data file. What I need is a macro substitution? Can you help me on this? Exactly how I asked in my file.