If Statement Question

M

mimmson

I'm trying to create a formula for the following.

A numerical score of between 1 and 4 is given, for anyone whose
accuracy score falls in the percentiles below.


ACCURACY

100- 99.7% = 4

99.6 - 98% = 3

97.9 - 96% = 2

95.9% and below = 1


What I would like to do is enter the percentage of accuracy in one cell
and have it automatically calculate the score (1-4) to another.

Any help would be greatly appreciated.

Thanks!
 
B

BenjieLop

mimmson said:
I'm trying to create a formula for the following.

A numerical score of between 1 and 4 is given, for anyone whose
accuracy score falls in the percentiles below.


ACCURACY

100- 99.7% = 4

99.6 - 98% = 3

97.9 - 96% = 2

95.9% and below = 1


What I would like to do is enter the percentage of accuracy in one cell
and have it automatically calculate the score (1-4) to another.

Any help would be greatly appreciated.

Thanks!
Click to expand...

Assuming that you enter your "percentage of accuracy" in Cell C1, this
is your formula ...


=IF(C1=\"\",\"\",IF(C1<96,1,IF(C1<98,2,IF(C1<99.7,3,4))))

Regards.
 
M

mimmson

Thanks!

For some reason however, it only provides results for the C1<96,1 par
of the equation and regardless of the score I enter the result i
always (1)
 
M

MrShorty

Benjilop's IF function should work. I expect you are enterin
percentages (98%=0.98 which is always <96). Adjusting the above I
function (or your input in C1) accordingly and it should work.

The IF function is limited in the number of nested IF's you can have.
If you ever wanted to expand the scores, a lookup table approach migh
be needed. In this case, I set up a lookup table in an out of the wa
place:

0 1
96 2
98 3
99.7 4

put my lookup value in A1 and B1=vlookup(a1,$M$1:$N$4,2,TRUE). Th
last parameter controls whether or not VLOOKUP finds an exact match o
not. With it set to TRUE (It's TRUE by default, so it can be omitted
if you like. See VLOOKUP in Excel help) the function will return th
value in the row just less than the lookup value in a1. For example
if a1=97, the function says, "I don't see a 97 in the table, but I se
a 96, so I'll return the value corresponding to 96."

Either approach should work just fine in this case
 
Top