Hello:

Your best bet is probably the Vlookup function.

In say H1 to I13 create a table that sets the limits of each range.

For example in H1 put 0 and in I1 put A, then in H2 put 500 and in

I2 put B etc. for 13 rows. (In your example what do you want to

happen if the value is exactly 500?) In this example if the value

is 500 it will return a B, if the value is less than 500 it will

return A. So you will need to decide on the break points.

Then in e1 put =VLOOKUP(D1,$H$1:$I$13,2), this assume your

first value is in D1. You can copy this formula down the

entire 20,000 rows.

The other option is to use the following form of the "choose" function:

(formula is entered all on one line)

=CHOOSE((D1<=500)+(D1<=1000)+(D1<=2000)+(D1<=3000)+(D1<=8000)

+(D1<=10000),"f","e","d","c","b","a")

For this example I used just 6 value you would continue on for 7 more. Be sure that

you have the letters in reverse order. For this example if the value in D1 was 9000

the result of the choose calculation would be 1 and would return "f", if the value in

D1 was 450 the choose calculation would be 6 and would return "a."

Pieter Vandenberg

(e-mail address removed) wrote:

: I have a list with over 20,000 rows in it. Each cell in column D has a

: number between 1 and 200,000. I would like to set up a formula in

: column E that will show a letter based on what range the number is in.

: For example, if the number is between 1 and 500, show "A", if it is

: between 501 and 1,000, show "B". If there were only 7 number ranges I

: would use a nested if:

: =IF(AND(D1>1,D1<500),"A",(IF(AND(D1>500,D1<1000)),"B",(IF.......

: and so on...

: The problem is that I have 13 number ranges that I need to create codes

: for. Any tips?