I am trying to get a formula that will do the following:

a score of:
0-9 = normal
10-13 = mild
14-20 = moderate
21-27 = severe
28+ = extremely severe

i tried doing an if function but it won't allow more than 5 (i think) if
functions. could someone please advise if 'if' is the function to use or
whether I should be trying something else?

thanks heaps

Here's one way:

=IF(ISNUMBER(A1),LOOKUP(A1,{0,10,14,21,28},{"norma l","mild","moderate","severe","extremely
severe"}),"")

Biff

"tam25" wrote in message
...
I am trying to get a formula that will do the following:

a score of:
0-9 = normal
10-13 = mild
14-20 = moderate
21-27 = severe
28+ = extremely severe

i tried doing an if function but it won't allow more than 5 (i think) if
functions. could someone please advise if 'if' is the function to use or
whether I should be trying something else?

thanks heaps

One way would be to set up a criteria page with all your numbers
listed and becide those numbers place their outcomes. Then complete a
Vlookup based on the criteria.

This will only work if the figures you are looking at are whole
numbers.

Oz.

On Feb 21, 12:42 pm, tam25 wrote:
I am trying to get a formula that will do the following:

a score of:
0-9 = normal
10-13 = mild
14-20 = moderate
21-27 = severe
28+ = extremely severe

i tried doing an if function but it won't allow more than 5 (i think) if
functions. could someone please advise if 'if' is the function to use or
whether I should be trying something else?

thanks heaps

I'd use the VLOOKUP function.

If you have the numbers in column A and you want the words in column B
consider this:

Put this in column B:

=VLOOKUP(A1,C$1:D$5,2)

and this table in column C1 and D1:

0 normal
10 mild
14 moderate
21 severe
28 extremely severe

IF() allows nesting up to 7 deep. Others have suggested alternative routes,
but if you want to use IF you could try:
=IF(AND(A1=0,A1<=9),"normal",IF(AND(A1=10,A1<=13 ),"mild",IF(AND(A1=14,A1<=20),"severe",IF(A1=28, "extremely
severe","answer undefined"))))

You could simplify the formula if you know that the input number is integer
(so that you don't fall between your specified categories), and/or if you
know that it isn't negative.
--
David Biddulph

"tam25" wrote in message
...
I am trying to get a formula that will do the following:

a score of:
0-9 = normal
10-13 = mild
14-20 = moderate
21-27 = severe
28+ = extremely severe

i tried doing an if function but it won't allow more than 5 (i think) if
functions. could someone please advise if 'if' is the function to use or
whether I should be trying something else?

thanks heaps