If Statement Question
 

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!


--
mimmson
------------------------------------------------------------------------
mimmson's Profile: http://www.excelforum.com/member.php...o&userid=23809
View this thread: http://www.excelforum.com/showthread...hreadid=374637

mimmson Wrote:
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!

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.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=374637

Thanks!

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


--
mimmson
------------------------------------------------------------------------
mimmson's Profile: http://www.excelforum.com/member.php...o&userid=23809
View this thread: http://www.excelforum.com/showthread...hreadid=374637

Benjilop's IF function should work. I expect you are entering
percentages (98%=0.98 which is always <96). Adjusting the above IF
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 might
be needed. In this case, I set up a lookup table in an out of the way
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). The
last parameter controls whether or not VLOOKUP finds an exact match or
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 the
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 see
a 96, so I'll return the value corresponding to 96."

Either approach should work just fine in this case.


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=374637

=if(g5=0.997,c2,if(g5=0.98,d2,if(g5=0.96,e2,if( g5<0.96,1))))


--
mimmson
------------------------------------------------------------------------
mimmson's Profile: http://www.excelforum.com/member.php...o&userid=23809
View this thread: http://www.excelforum.com/showthread...hreadid=374637

=LOOKUP(A10,{0,1;0.96,2;0.98,3;0.997,4})

--
HTH

Bob Phillips

"mimmson" wrote in
message ...

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!


--
mimmson
------------------------------------------------------------------------
mimmson's Profile:
http://www.excelforum.com/member.php...o&userid=23809
View this thread: http://www.excelforum.com/showthread...hreadid=374637