Solved logical test in IF statement in the form of interval in exce

April 22, 2015 at 04:07:56
Specs: Windows XP
I have a table containing student grade averages with the first column containing the names and the second containing the averages, I want to write an IF statement that tests the interval that the average belongs to and assign a label to it, that is: if the average is in the interval between 50 and 60, the IF statement should assign the label "Good" to it, and if it lies between 61 and 80, it is assigned a label " Very Good", and so on...

See More: logical test in IF statement in the form of interval in exce

Report •


#1
April 22, 2015 at 06:13:25
When you say "the interval between 50 and 60" I assume you mean to include the 50 and the 60.

You also don't mention what you want to return if the average is something like 60.5 since you only inlcuded whole numbers, e.g. 60 and 61.

The answer also depends on how many "and so on"s you need.

Within reason, you should be able to use a Nested IF, similar to this. Note that I used A1<61 and A1<81. By using that method, 60.99999 is still classified as Good. Very Good doesn't start until 61. Modify as required.

=IF(AND(A1>=50,A1<61),"Good",IF(AND(A1>=61,A1<81),"Very Good",IF(A1>=81,"Excellent","Fail")))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
April 25, 2015 at 03:30:28
Many thanks DerbyDad03, isn't there any formula that does not use AND statement (a more simple formula)?
the reason is that I want to teach it to someone very new to excel and he will get confused if I talk about nesting functions.

Report •

#3
April 26, 2015 at 04:08:33
✔ Best Answer
You can eliminate the AND, but you can not eliminate the Nested IF.

A basic IF statement checks for the occurrence of one value and returns one value if that value exists, and another value if it doesn't. Once you add a second condition, you are actually looking for 1 of 3 values to be returned - one if the first value exists, another if the second value exists, and a third if neither value exists - therefore you must use a nested IF.

This untested formula eliminates the AND. In this case you must test for the highest value first and work your way down since the IF will stop checking as soon it is TRUE.

=IF(A1>80,"Excellent",IF(A1>=61,"Very Good",IF(A1>=50,"Good","Fail")))

BTW...If you don't want to use a Nested IF, you can use VLOOKUP, but that is not as simple.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Related Solutions


Ask Question