auto round to multiple of 10

[ajitsingh]

i am trying to round a number to next multiple of 10 with condition i.e.
for example if number is 1230.56 then it should round off to 1230
but if the unit number is more than zero i.e. 1231.56 then it should round off to 1240.
that means the main number should round off to next multiple of 10 only if unit number is equal to or more than 1.

example 120.11 should round off to 120
121 to 130
120.99 to 120
119 to 120 and so on

 

[Marcelo Branco]

Hi,

Maybe this

=IF(MOD(TRUNC(A1),10)<>0,TRUNC(A1)+10-MOD(TRUNC(A1),10),TRUNC(A1))

HTH

M.

 

[jasonb75]

Or

=CEILING(INT(A1),10)

 

[Marcelo Branco]

Or

=CEILING(INT(A1),10)

Simpler, i have to say

M.

 

[Useful]

Also
=IF(ISERROR(SEARCH(".";A1));ROUNDUP(A1;-1);IF(--MID(A1;SEARCH(".";A1)-1;1)=0;ROUND(A1;-1);ROUNDUP(A1;-1)))

 

[ajitsingh]

thanx dear it works as required

 

[maha12may]

or =ceiling(int(a1),10)

hi jason, thank u for ur excel command for rounding off to the next multiple of 10 timely help

 

[ajitsingh]

Hi jason, the formula doesn't in the following way
i.e.
i) if the value is say 10.9 (it should round to 10) not 20 (which your formula does)
ii) yeah if the value is 11 or more it rounds to 20.

but however Branco's formula works as required...

Thanx anyway

 

[jasonb75]

if the value is say 10.9 (it should round to 10) not 20 (which your formula does)

It works correctly for me, and for everyone else, sounds like your workbook is broken.

*	A	B

<COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]10.9[/TD]
[TD="align: right"]10[/TD]

</TBODY>

Spreadsheet Formulas

Cell Formula B1 =CEILING(INT(A1),10) <TBODY> </TBODY>

<TBODY>
</TBODY>