changing cell info

G

Greenback

Hi
I have a column of figures, and in this column, I have the numbers 5, 6, 7,
8 & 9.
Now, only these figures are in this column, ie, cell a1 could be a 6, a7
could be a 5, etc etc ....it is a rating for an employee.
Now, what I need to do is show that if the rating shows as a 9, then a 4
must be shown in its place, if an 8, a 3 must show, if a 7, a 2 must show, if
a 6, then 2+ must show, if a 5, a 1 must show.
I have hundreds of these figures in column a. How can I change the above to
show the new ratings?
Thanks in advance,
 
M

Max

You can also transform it in an adjacent col using a vlookup

Assuming ratings in A1 down, put in B1:
=IF(OR(A1="",A1<5,A1>9),"",VLOOKUP(A1,{5,1;6,"2+";7,2;8,3;9,4},2,0))
Copy B1 down to return required results. Then if desired, just copy col B
and overwrite col A with a paste special as values. Clean up by clearing col
B (press Delete).
 
G

Greenback

Max,many thanks for that. I have tried that but my figures begin in cell
L4...would I just change your formual to suit?, ie, change A1 to L4, or does
it need to begin at the top of a row?

Cheers,
 
M

Max

... ie, change A1 to L4, or does it need to begin at the top of a row?

Yes, just change the A1 to L4 in the earlier formula ..
Put in say, M4:
=IF(OR(L4="",L4<5,L4>9),"",VLOOKUP(L4,{5,1;6,"2+";7,2;8,3;9,4},2,0))
Copy M4 down
 
J

John

This is the if then statement that I built for my project.
If(a3="2.5",Vlookup(d5,web_25,Match(R2,headers,0,false)

1. How many of this statement that I can build ?

2. In doing this, I have limited myself to the match value of "2.5". If the
"2.5" value needed to be changed to a different value such as "3.5", then I
have to modify the if then statement. Is there a way that I can build an if
then statement that will give me a flexibility in changing the value without
modifying the statement.

Appreciate for the help and sample of the if then statement. My email
address is [email protected].

Thanks in advance!

John
 
M

Max

This is the if then statement that I built for my project.
If(a3="2.5",Vlookup(d5,web_25,Match(R2,headers,0,false)

1. How many of this statement that I can build ?
Click to expand...

Think it's up to a max of 7 nested levels
2. In doing this, I have limited myself to the match value of "2.5". If
the "2.5" value needed to be changed to a different value such as "3.5",
then I have to modify the if then statement. Is there a way that I can
build an if then statement that will give me a flexibility in changing the
value without modifying the statement.
Click to expand...

Just point the value to another cell which will house the value, eg instead
of
If(A3=2.5,...
use:

If(A3=X3,...
Click to expand...

where X3 would house, for instance: 2.5

Btw, note that the numeric value of 2.5 should be written without the double
quotes
 
Top