Return a sum or 0 if a range is true/false in excel

enter image description here

I’m a new teacher and i was trying to write a program in excel that helps students with a maximum of 5 marks if needed to pass.

As an example:

  • if a student gets 46 I’d give him 4 marks.
  • if the student gets 49 I’d give him 1 mark.
  • if the student gets 44 or 50 he gets 0.

I want to write a formula in the column K that gives values from 0 to 5.

Not using excel before I tried =IF((44<J3<50),50-J3,0) for student A but got 0 and everyone else got the same 0.

>Solution :

What about:

IF(OR(B2<=44,B2>=50),0,50-B2)

enter image description here

Of course you could do this:

IF(abs(50-B2)>5,0,50-B2)

Based on the comment by User1122393 :
tested with more values:
enter image description here

Leave a Reply