Using ranges in excel “if” conditions

Excel function tutorials

Good morning and welcome to another one of our excel tutorials. This time we are answering to a common question that we get which concerns using ranges in conditions. Here is the question:

If marks is 40-60 text should be “PASS” if it is more than 60 & less than 75 First Class , If more than 75 “Disti”.

It looks like a simple task but as many of you know, it is much more tricky than you would initially imagine. To make things a bit more complex, here is the range we will decide for our student grades:

0-49%: Fail
50-59%: Pass
60-75%: First Class
76%-100%: Distinction

As is always the case in excel, there are many different ways to get this done but we will try a few here, let`s start by getting a sample of students. Obviously, the goal is to make it as easy to correct 10, or 250 students!

There are many different ways to get it done but in this case we will simply be using conditions. It will look like a difficult one but you simply need to be disciplined when you write it. Doing it by steps

Step #1-If the student got under 50% = fail

=IF(B2<0.5,"Fail","Pass") Step #2-If the student got under 50% = fail, if he got under 60%, he “passed”

=IF(B2<0.5,"Fail",IF(B2<0.6,"Pass","Good")) Step #3-If the student got under 50% = fail, if he got under 76%, he got “First Class”. If none of those conditions are filled, he got a “Distinction”

=IF(B2<0.5,"Fail",IF(B2<0.6,"Pass",IF(B2<0.76,"First Class","Distinction"))) What you are doing is simply adding a condition. Step by step, you are looking at: -Did the student fail? -If he did not fail, did he get under 60%? -If he did get it 60%, did he get 76%? It looks like a difficult argument to build but if you do it one step at a time, it actually works very well. Please note that you must do these in order.

As is always the case, we’d love to get your comments or questions! Thanks in advance!

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************

4 Feedbacks on "Using ranges in excel “if” conditions"

sher shah

my questions is
if i have 5 rows in a sheet and i have different values as A1=22,A2=33,A4=44,A5=55
now i want define a formula if i put same value 44 in A6 then
how to define a formula if i put same value the computer should gives me a message that this value already exists. Please
my Email Id is: sher_bci@yahoo.com



ram

thanks for sharing information and helping in understanding the “IF” clause.



Sanela Mahmutovic

I need to track the time and I am struggling to make IF function to work. I am trying to convert the time to the production shift (1st vs 2nd or 3rd).



Puncture Wounds

eval(ez_write_tag([[300,250],’brighthub_com-medrectangle-2′]));.
After an Achilles tendon rupture, a player will be able to walk flat-footed, but will not be able to stand up on his or her
toes on the affected side. “Frozen” immobile toe and ankle joints gain mobility.