If Statement Help Please

[tristangemus]

I had this solved however I made a mistake! The ceiling has to occur before the if statement. One of the values is 47.59 and it is first seen as less than 48 then it is rounded to 48 using the ceiling. I need it to be rounded to 48 then seen as 48 therefore rounded to 50! Below is what I have can you please help!

=CEILING(IF(AND(I11>47.99,I11<50),50,I11),1)

I am creating a gradebook which calculates grades and basically if you have a 48 or 49 final grade you will receive a 'mercy pass' and therefore given a 50. The grade is in cell L4 and the final is in cell J4.

If they have a grade of 51 or above it will remain at that grade. My problem is keeping their current grade. Anyone help create this formula please ?

47.99 or below = the same value
48 to 49.99 = 50
50+ = same value

[alansidman]

=if(or(j4=48,j4=49),50,j4)

Try this in your destination cell. You listed grade and final as both in J4. I'm guessing the final is in some other cell.

[tristangemus]

=if(or(j4=48,j4=49),50,j4)

Try this in your destination cell. You listed grade and final as both in J4. I'm guessing the final is in some other cell.

Hey the final is in Cell J4, their grade is in L4

[alansidman]

Put the formula in J4. Change the J4's in my formula to L4.

Alan

[tristangemus]

I tried and got a value of 0!

[tristangemus]

47.99 or below = the same value
48 to 49.99 = 50
50+ = same value

[alansidman]

Look at the attached with the formulas in column C

[tristangemus]

Look at the attached with the formulas in column C

Thank you! And shoot, forgot to mention there is a ceiling to the function as well. Anything with .01 or more will me rounded up. Example, 77.04 would be rounded up to 78

I have this =ceiling(l4,1) but i dont know how to combine it with the If statement.

[alansidman]

Add it to the existing formula as follows:

=CEILING(IF(AND(B6>47.99,B6<50),50,B6),1)

[tristangemus]

Hey one more issue (i updated the thread)

[alansidman]

In my original attachment, change the formula in C1 to:

=CEILING(IF(AND(ROUNDUP(B2,0)>=48,B2<50),50,B2),1)

unless you wish for grades less than 47.50 to not be rounded up. In that case, then change roundup to round.