Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
=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 |
#6
|
|||
|
|||
=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formating Extreme Question | Excel Worksheet Functions | |||
IF Statement question | Excel Worksheet Functions | |||
IF Statement question | Excel Worksheet Functions | |||
If Statement Question | Excel Worksheet Functions | |||
An easy macro question and one I believe to be a little more diffi | Excel Worksheet Functions |