I need a statement that will allow me to get answer based on a certain criteria
I have a column of answers ranging from -400 to 400. Now I need a certain
range of numbers to be changed into one value.
< -150 has to come as a -2

150 to -20 has to come up at a -1

20 - 150 has to come up as a 0

150 - 250 has to come up as a1

250 has to come up as a 2

I want the single integars in a column right beside the larger number.

One way.
=IF(C6250,2,IF(C6=150,1,IF(C6=20,0,IF(C6=-20,-1,-2))))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Shortstopper00" wrote in message
...
I need a statement that will allow me to get answer based on a certain
criteria
I have a column of answers ranging from -400 to 400. Now I need a certain
range of numbers to be changed into one value.
< -150 has to come as a -2

150 to -20 has to come up at a -1

20 - 150 has to come up as a 0

150 - 250 has to come up as a1

250 has to come up as a 2

I want the single integars in a column right beside the larger number.

Perhaps the following will meet your needs:

=LOOKUP(A1,{-400,-150,-20,150,250},{-2,-1,0,1,2})


< -150 has to come as a -2
150 to -20 has to come up at a -1
20 - 150 has to come up as a 0
150 - 250 has to come up as a1
250 has to come up as a 2

I assume you mean -150 to -20 becomes -1, and -20 to 150 becomes 0. I also
assume you mean =250 becomes 2; and I assume that "N to M" means =N to <M
("N up to, but not including M").


I want the single integars in a column right beside the larger number.

If the "larger number" is in A1, the formula goes into B1. If the column of
"larger numbers" is in A1:A100, you can simply copy B1 down through B100,
and the reference to A1 will automagically change to A2, A3, etc.


----- original message -----

"Shortstopper00" wrote in message
...
I need a statement that will allow me to get answer based on a certain
criteria
I have a column of answers ranging from -400 to 400. Now I need a certain
range of numbers to be changed into one value.
< -150 has to come as a -2

150 to -20 has to come up at a -1

20 - 150 has to come up as a 0

150 - 250 has to come up as a1

250 has to come up as a 2

I want the single integars in a column right beside the larger number.