# Change rounding formula

#### phil133

##### Active Member
Hi. This formula
Code:
``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!

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
=trunc(c4)+if(c4-int(c4),0.5,0)

Or

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

Or

=CEILING(C4,0.5)

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.

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

=CEILING(C4,5)

Thank you!

Replies
1
Views
280
Replies
1
Views
422
Replies
12
Views
442
Replies
0
Views
215
Replies
8
Views
179

1,196,028
Messages
6,012,956
Members
441,740
Latest member
IammeResources

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back