complex formula

[Lou]

i have a list of random numbers in column A1 0-75. i am trying to make a
formula that will predict results based on the number in column A that will
appear in column B

up to 29 should result in a letter W in column B
30 - 37 should result in a number 1
38 - 44 should result in 2C
45 - 55 should result in 2B
56 -65 should result in 2A
66 and over should result in 3

Have been trying all day, can get the numbers but as soon as I try to add
letters I am lost. any help would be greatly appreciated.

 

[Gary''s Student]

You should try a VLOOKUP table. See:

http://www.contextures.com/xlFunctions02.html

 

[Don Guillett]

Not really that hard. Look in the help index for LOOKUP
try
=LOOKUP(G8,{0,30,38,45,56,66},{"w",1,"2c","2b","2a",3})

 

[Sandy Mann]

Try:

=IF(A1>65,"3",IF(A1>55,"A2",IF(A1>44,"2B",IF(A1>37,"2C",IF(A1>29,"1","W")))))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Gord Dibben]

Lou

Try this formula in B1

=LOOKUP(A1,{0,30,38,45,56,66},{"W",1,"2C","2B","2A",3})

Double-click to copy down if you have data in A1:A75


Gord Dibben MS Excel MVP

 

[Lou]

What can I say? thank you so much, now I can sleep.

 

[Lou]

Not really hard when you know what you are doing! I unfortunately am still a
novice. Thanks for trying to help me much appreciated, but I seem to get w's
with every result when I tried this one. Don't use any more of your valuable
time on this one though -another kind person has helped me too. sorted!

 

[Sandy Mann]

You're welcome, glad you got it to work.

Don & Gord's formulas work just as well and are shorter but at first sight
it is not as obvious how they work as it is with my simple formula.

Thanks for posting back.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Don Guillett]

Did you try changing g8 to a2 or whatever cell you wanted to evaluate