# rounding

#### josorens

##### New Member
hi there,

problem:

I need to roundup numbers to nearest 5 or 9.

example:
142,95 to 145,00
92,54 to 95,00
97,02 to 99,00

I tried using the round functions, but didnt get any good results.

Do you have a suggestion?

Thanks and cheers

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### Guitarde

##### Board Regular
If you wanted it to be in one cell then the formula is:
=MIN(ROUNDDOWN(ROUNDUP(B12;0)/10;0)*10+9;CEILING(B12;5))

#### josorens

##### New Member
Thanks for your help! however I ran into a problem with your formula

Initially I wanted the formula to roundup to nearest 5 or 9.

If I use your formula, the number 39,99 rounds up to 40 instead of the nearest roundup to 45.

Cheers

#### rrdonutz

##### Well-known Member
One possibility:

=FLOOR(A1,10)+IF(MOD(A1,10)<=5,5,IF(MOD(A1,10)<=9,9,15))

[EDIT] But probably better as:

=FLOOR(A1,10)+IF(A1=0,0,IF(MOD(A1,10)<=5,5,IF(MOD(A1,10)<=9,9,15)))

Replies
4
Views
231
Replies
4
Views
75
Replies
1
Views
191
Replies
3
Views
260
Replies
1
Views
147

1,187,188
Messages
5,962,102
Members
438,584
Latest member
MASV

### 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