So many greater than and less than values to calculate

[Geezer]

I need to get this formula into a single cell

=if(x <999 then A
=if(x is between and inclusive to 1000-1500 then B
=if(x is between and inclusive to 1501-2500 then C
=if(x is between and inclusive to 2501-3500 then D
=if(x is between and inclusive to 3501-4500 then E
=if(x is between and inclusive to 4501-5500 then F
=if(x is between and inclusive to 5501-7000 then G
=if(x is between and inclusive to 7001-10000 then H
=if(x is between and inclusive to 10001-15000 then I
=if(x is between and inclusive to 15001-20000 then J
=if(x is between and inclusive to 20001-30000 then K
=if(x >30001 then L

I then have to run it down 10,000 rows to define a new value for each row cell
How best to do it?
You're much brighter than I am!

Ta!
Geezer

 

[Arvi Laanemets]

Hi

=CHOOSE(MATCH(x,{0;1000;1501;2501;3501;4501;5501;7001;10001;15001;20001;3000
1},1),A,B,C,D,E,F,G,H,I,J,K,L)
or (depends whad you did mean with A, B, C etc.)
=CHOOSE(MATCH(x,{0;1000;1501;2501;3501;4501;5501;7001;10001;15001;20001;3000
1},1),"A","B","C","D","E,"F","G","H","I","J","K","L")

 

[Bob Phillips]

Create a lookup table like so

0 A
999 B
1500 C
2500 D
etc, you should get the idea by now, and use a formula of


=VLOOKUP(A1,$H$1:$J$12,2)

where A is the value to resolve, H1;J12 is that table, and just copy down.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Clive Geezer]

Greetings Arvil
- many thanks for the prompt reply
- I have applied the suggested formula substituting X with the cell to
check and sadly Excell moans at me and will not accept it - what am I
doing wrong?

Here
http://www.gemnetsolutions.com/lessthan.jpg
is the screen dump for your info
- can you help further please, I'm driving myself up the wall?

Ta!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Arvi Laanemets]

Hi

A quote mark was missing after "E"

--
When sending mail, use address arvil<at>tarkon.ee
Arvi Laanemets



Greetings Arvil
- many thanks for the prompt reply
- I have applied the suggested formula substituting X with the cell to
check and sadly Excell moans at me and will not accept it - what am I
doing wrong?

Here
http://www.gemnetsolutions.com/lessthan.jpg
is the screen dump for your info
- can you help further please, I'm driving myself up the wall?

Ta!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Bob Phillips]

If you are going to copy this formula to 10000 cells, I suggest you use an
explicit table, not embedded data.

--

HTH

RP
(remove nothere from the email address if mailing direct)