Microsoft EXCEL 2000 Formula

L

Lou

Hi
I have a mixed column of numbers between 0 and 75. From these numbers I
want a second column to predict a future score. eg 0-20 would produce 1A,
20-50 would produce 2A and 50- 75 would produce 3A. Is it possible to do
with a formula please?

Thank you

Lou
 
R

Ron Coderre

Try something like this:

For a value in A1

B1: =VLOOKUP(A1,{0,1;21,2;51,3},2,1)&"A"
or
B1: =LOOKUP(A1,{0,21,51},{1,2,3})&"A"

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
B

Bob Phillips

=(A1>0)+(A1>20)+(A1>50)&"A"

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
L

Lou

Hi Ron

Thanks for trying to help me. I am afraid I am not too skilled in excel. I
have only used Sum and Average before. I have tried using the formulas but
instead of producing a result of 1A, 2A or 3A as appropriate in cell B2 it
simply pastes the whole formula into cell B2. Its probably me Ahhh!!!!

Lou
 
L

Lou

Bob

Thanks for this. Is it possible to extend it further? As a second formula,
it would be good if I could show other levels too. i.e. up to and including
29 would give a W in column B, 30 -37 would give a 1, 38-44 would give a 2C,
45 -55 would give a 2B 56-65 would give a 2A and over 66 would give a 3.

I have been trying to adapt the first one you gave me with little success.
Any help would be greatly appreciated. Thanks again.

Lou
 
G

Gord Dibben

If copying and pasting from Ron's post, possible it pasted in as Text.

If typing it in, possible the cell was pre-formatted as Text.

Format to General, hit F2 and ENTER key.

Now you should get a result if you have a value in A1


Gord Dibben MS Excel MVP
 
B

Bob Phillips

I am sorry, I don't understand how this relates to what you first asked.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
R

Ron Coderre

Lou

I'm afraid it feels like you're treatings us like mushrooms and that's
making this a bit more complicated than it has to be. If you tell us all of
the rules, up front, along with what you've tried, you'll make it easier for
us to tailor our advice to your specific need.

Anyway, from what I can infer from your posts, I think Debra Dalgleish's
instructions for the VLOOKUP function will get you pointed in the right
direction.
Start here:
http://www.contextures.com/xlFunctions02.html#Range

Post back with any question.

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
Top