software

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)

quot;Cowtoonquot; gt; wrote in message
...
gt; Is there a function that will allow me to round up to the nearest 10, i.e.
gt; 214 would be 220, 253 would become 260.
gt; I have no idea if that can be done.
gt; Would appreciate your insights.
gt; Thanks.
gt;
gt;
For a value in A1
B1: =CEILING(A1,10)

Does that help?

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

XL2002, WinXP-Proquot;Cowtoonquot; wrote:

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

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

quot;Bob Phillipsquot; gt; wrote in message
...
=roundup(A1,-1)

or

=CEILING(A1,10)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

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

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 = quot;H1:H10quot;

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)

quot;Cowtoonquot; gt; wrote in message
...
gt; Thank you Bob, Ron.
gt;
gt; Does this obviously mean that I'll need an extra column for this to occur
gt; and be visible. I guess there isn't a function that would work without
the
gt; extra cells. I was hoping I could set the function up with this parameter
gt; and just type in the value and have it show in the same cell.
gt;
gt; Your thoughts are welcome.
gt; If not ... I'll use your solution as it does work ... but need to add an
gt; extra column for it to work.
gt; Thanks.
gt; Diana
gt;
gt; quot;Bob Phillipsquot; gt; wrote in message
gt; ...
gt; =roundup(A1,-1)
gt;
gt; or
gt;
gt; =CEILING(A1,10)
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Cowtoonquot; gt; wrote in message
gt; ...
gt; gt; Is there a function that will allow me to round up to the nearest 10,
i.e.
gt; gt; 214 would be 220, 253 would become 260.
gt; gt; I have no idea if that can be done.
gt; gt; Would appreciate your insights.
gt; gt; Thanks.
gt; gt;
gt; gt;
gt;
gt;
gt;
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

quot;Bob Phillipsquot; gt; wrote in message
...
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 = quot;H1:H10quot;

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)

quot;Cowtoonquot; gt; wrote in message
...
gt; Thank you Bob, Ron.
gt;
gt; Does this obviously mean that I'll need an extra column for this to occur
gt; and be visible. I guess there isn't a function that would work without
the
gt; extra cells. I was hoping I could set the function up with this parameter
gt; and just type in the value and have it show in the same cell.
gt;
gt; Your thoughts are welcome.
gt; If not ... I'll use your solution as it does work ... but need to add an
gt; extra column for it to work.
gt; Thanks.
gt; Diana
gt;
gt; quot;Bob Phillipsquot; gt; wrote in message
gt; ...
gt; =roundup(A1,-1)
gt;
gt; or
gt;
gt; =CEILING(A1,10)
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Cowtoonquot; gt; wrote in message
gt; ...
gt; gt; Is there a function that will allow me to round up to the nearest 10,
i.e.
gt; gt; 214 would be 220, 253 would become 260.
gt; gt; I have no idea if that can be done.
gt; gt; Would appreciate your insights.
gt; gt; Thanks.
gt; gt;
gt; gt;
gt;
gt;
gt;