# Round number to xx9 number function

#### BOGIA

##### Active Member
Hi all,

Could someone help to convert numbers below to 9 before .cent value by using function "ROUND" or VB code.

I have in column A numbers like 183.35, 195.57, 220.01 etc... expecting the results should be 179.00, 199.00, 219.00 etc... in column B. Please see the sample below:

183.35 179
195.57 199
220.01 219
207.79 209
195.57 199
183.35 179
195.57 199
207.79 209
195.57 199
207.79 209
207.79 219

Thanks heaps.

cheers,
bogia

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### dafan

##### Well-known Member
It's pretty nasty, and doesn't use ROUND or VBA but it does the trick :P

This goes in B1 if your number is in A1, drag down
Code:
``=IF(ABS(A1-((LEFT(A1,2)) & "9"))>ABS(A1-(((LEFT(A1,2)) & "9")-10)),(((LEFT(A1,2)) & "9")-10),((LEFT(A1,2)) & "9"))``

Hope it helps, even though it doesn't meet your requirements.

If you want it to work with numbers above 1000 to, use this (even nastier).
Code:
``=IF(ABS(A1-((LEFT(A1,(LEN(A1)-4))) & "9"))>ABS(A1-(((LEFT(A1,(LEN(A1)-4))) & "9")-10)),(((LEFT(A1,(LEN(A1)-4))) & "9")-10),((LEFT(A1,(LEN(A1)-4))) & "9"))``

In this case I assumed your numbers always are xxx.xx.

Last edited:

#### mikerickson

##### MrExcel MVP
=round(a1-9,-1)+9

#### dafan

##### Well-known Member
Haha told you...Nice Mike, but why not use =ROUND(A1,-1)-1 ?

#### mikerickson

##### MrExcel MVP
Its not as obvious that =ROUND(A1+1,-1)-1 ends in 9, but the result is the same.

#### BOGIA

##### Active Member
Hi Stefan and Mike,

Thanks both for your expert help. They all work well.

cheers,
bogia

Replies
1
Views
186
Replies
8
Views
127
Replies
1
Views
2K
Replies
4
Views
733
Replies
7
Views
578

1,191,707
Messages
5,988,198
Members
440,138
Latest member
yanaa

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