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,

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,A19),"",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).
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Greenback" wrote:
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,

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,

"Max" wrote:

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,A19),"",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).
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Greenback" wrote:
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,

... 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,L49),"",VLOOKUP(L4,{5,1;6,"2+"; 7,2;8,3;9,4},2,0))
Copy M4 down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Greenback" wrote:
Max, many thanks for that. I have tried that but my figures begin in cell
L4...would I just change your formula to suit?, ie, change A1 to L4, or does
it need to begin at the top of a row?

Cheers,

Max, worked a treat !.
Thanks again for your help. Have a great weekend

"Max" wrote:

... 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,L49),"",VLOOKUP(L4,{5,1;6,"2+"; 7,2;8,3;9,4},2,0))
Copy M4 down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Greenback" wrote:
Max, many thanks for that. I have tried that but my figures begin in cell
L4...would I just change your formula to suit?, ie, change A1 to L4, or does
it need to begin at the top of a row?

Cheers,

Welcome! Thanks for the feedback.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Greenback" wrote in message
...
Max, worked a treat !.
Thanks again for your help. Have a great weekend

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

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 .

Thanks in advance!

John

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

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

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.

Just point the value to another cell which will house the value, eg instead
of

If(A3=2.5,...

use:

If(A3=X3,...

where X3 would house, for instance: 2.5

Btw, note that the numeric value of 2.5 should be written without the double
quotes
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---