Round number to xx9 number function

[BOGIA]

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

 

[dafan]

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

=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).

=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.

 

[mikerickson]

=round(a1-9,-1)+9

 

[dafan]

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

 

[mikerickson]

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

 

[BOGIA]

Hi Stefan and Mike,

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

Thanks again for your quick reply.

cheers,
bogia