So many greater than and less than values to calculate
 

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

So many greater than and less than values to calculate
 

Hi

=CHOOSE(MATCH(x,{0;1000;1501;2501;3501;4501;5501;7 001;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;7 001;10001;15001;20001;3000
1},1),"A","B","C","D","E,"F","G","H","I","J","K"," L")

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Geezer" wrote in message
om...
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

So many greater than and less than values to calculate
 

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)


"Geezer" wrote in message
om...
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

So many greater than and less than values to calculate
 

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!

So many greater than and less than values to calculate
 

Hi

A quote mark was missing after "E"

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Clive Geezer" wrote in message
...

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!

So many greater than and less than values to calculate
 

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)


"Arvi Laanemets" wrote in message
...
Hi

A quote mark was missing after "E"

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Clive Geezer" wrote in message
...

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!