Round up to nearest 10

[Cowtoon]

Is there a function that will allow me to round up to the nearest 10, i.e.
214 would be 220, 253 would become 260.
I have no idea if that can be done.
Would appreciate your insights.
Thanks.

 

[Bob Phillips]

=roundup(A1,-1)

or

=CEILING(A1,10)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

For a value in A1
B1: =CEILING(A1,10)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Cowtoon]

Thank you Bob, Ron.

Does this obviously mean that I'll need an extra column for this to occur
and be visible. I guess there isn't a function that would work without the
extra cells. I was hoping I could set the function up with this parameter
and just type in the value and have it show in the same cell.

Your thoughts are welcome.
If not ... I'll use your solution as it does work ... but need to add an
extra column for it to work.
Thanks.
Diana

=roundup(A1,-1)

or

=CEILING(A1,10)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bob Phillips]

Yes it does mean an extra column, functions cannot work on the same cell
that contains the data, they would wipe the data.

You could use event code

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Value = Application.RoundUp(.Value, -1)
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.





--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Cowtoon]

Thanks Bob,
What I think I'll do is put the rounded values in a different worksheet.
that would work best for me, rather than having too many columns in view.

Thanks so much for your help ... appreciated

Yes it does mean an extra column, functions cannot work on the same cell
that contains the data, they would wipe the data.

You could use event code

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Value = Application.RoundUp(.Value, -1)
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.





--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)