Round up to nearest 10

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.

=roundup(A1,-1)

or

=CEILING(A1,10)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Cowtoon" <noemailaddress@nowhere.com> wrote in message
news:%23gBh$fUPGHA.2300@TK2MSFTNGP15.phx.gbl...
> 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.
>
>

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

Does that help?

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

XL2002, WinXP-Pro


"Cowtoon" wrote:

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

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

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:%23XGS4mUPGHA.2624@TK2MSFTNGP12.phx.gbl...
=roundup(A1,-1)

or

=CEILING(A1,10)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Cowtoon" <noemailaddress@nowhere.com> wrote in message
news:%23gBh$fUPGHA.2300@TK2MSFTNGP15.phx.gbl...
> 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.
>
>

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" <noemailaddress@nowhere.com> wrote in message
news:eUS9wQVPGHA.720@TK2MSFTNGP14.phx.gbl...
> 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
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:%23XGS4mUPGHA.2624@TK2MSFTNGP12.phx.gbl...
> =roundup(A1,-1)
>
> or
>
> =CEILING(A1,10)
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Cowtoon" <noemailaddress@nowhere.com> wrote in message
> news:%23gBh$fUPGHA.2300@TK2MSFTNGP15.phx.gbl...
> > 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.
> >
> >
>
>
>

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

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:OVggOmVPGHA.3016@tk2msftngp13.phx.gbl...
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" <noemailaddress@nowhere.com> wrote in message
news:eUS9wQVPGHA.720@TK2MSFTNGP14.phx.gbl...
> 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
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:%23XGS4mUPGHA.2624@TK2MSFTNGP12.phx.gbl...
> =roundup(A1,-1)
>
> or
>
> =CEILING(A1,10)
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Cowtoon" <noemailaddress@nowhere.com> wrote in message
> news:%23gBh$fUPGHA.2300@TK2MSFTNGP15.phx.gbl...
> > 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.
> >
> >
>
>
>