Assign Specific #s to Value Range

April 15, 2009 at 00:21:48
Specs: Windows XP
Hi Everyone,
This might be elementary but I have never used this function before and I need desperate help from you experts of Excel.

I need to assign specific #s to specific value ranges. Example: Value Range 1-20, I need to rate these value ranges and give a points out of 10. Anything between 0-2 will be given 10 points (assigning 10 to all numbers between 0-2), anything >2, <4 will be 9, >4 n <6 will be assigned 8, and so on.
I also need the same formula/function to work with ranges from 0-1000+ such as 0-200 will get 10, 200+ to 300 will get 9, and so on. Please let me know how I can do this, this work I need to do for my father, so I hope to get it right. Thanks guys!! Really appreciate it.

See More: Assign Specific #s to Value Range

Report •

April 15, 2009 at 06:25:29
Sounds like a homework assignment...

Consider Nested If statements:

=IF(AND(A1>=0,A1<=2),10,IF(AND(A1>2,A1<=4),8, etc.

Keep in mind that you can't have more than 7 Nested If's in a single formula, but there are workarounds.

Once you get the Nested If's working and find you need more than 7, Google the issue and you'll find some workarounds.

Report •

April 15, 2009 at 11:20:03
Thank you so much! This is exactly what I need DerbyDad03! This is actually what my father asked me to do as he wants me to rate and rank all the fundamentals of all the stocks in Hong Kong and come out with a total rating for each one of them based on their numbers. How does the work around work? You are right, I can only put a maximum of 7 Nested in the formula, I've tried to GOOGLE it but I get all these random things. I will keep looking, but if any you experts here know how, please let me know, I would appreciate it a lot, thank you so much once again DerbyDad03.

Report •

April 15, 2009 at 12:07:22
re: I've tried to GOOGLE it but I get all these random things

I guess I don't know your definition of random.

DAGS excel 7 nested if

Lots of hits with various workarounds.

Report •

Related Solutions

Ask Question