Change rounding formula

[phil133]

Hi. This formula

ROUND(C4*2,0)/2

rounds to the nearest 0.5. I would like to add a condition that if C4>ROUND(C4*2,0)/2 then

e.g if C4= 16.21, I would like E4= 16.5 (not 16 like the formula above)
but if C4=23.51, I would like E4= 24 (not 23.5 like the formula above)

Hope you understand what I'm trying to do!

 

[njimack]

=trunc(c4)+if(c4-int(c4),0.5,0)

 

[gaz_chops]

Or

=INT(C4)+IF(MOD(C4,1)<0.5,0.5,1)

 

[FormR]

Or

=CEILING(C4,0.5)

 

[phil133]

Thanks to all 3 of you! The last 2 formulas work for me.

I have a bonus question!

I want the same concept but the results need to be in increments of 5.

eg. if 120<C4<125, I would like E4=125
if 125<C4<130, I would like E4=130
etc.

 

[barry houdini]

You can simply change the second argument of CEILING to the required increment, e.g.

=CEILING(C4,5)

 

[phil133]

Thank you!