#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default Rounding issue

I've searched the archives and can't really find what I am looking
for. I have a macro that will format a result based upon the number
of digits the result contains. If the result is 1000 then I need to
round the result to the 10's place (e.g. 1042 would be rounded to
1040). When I put in Round(result, -1), I get a generic error. I know
this is the Excel formula. I have copied the code below. If anyone
can help it would be greatly appreciated.

If result < 0.69 Then
new_result = "<0.69"
Else
If result 1000 Then
new_result = Round(result, -1) '''''''''This is where
the error occurs.''''''''''
Else
If result 100 Then
new_result = Round(result, 0)
Else
If result 10 Then
new_result = Round(result, 1)
Else
new_result = Round(result, 2)
End If
End If
End If
End If

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,593
Default Rounding issue

Try this

If result < 0.69 Then
new_result = "<0.69"
Else
If result 1000 Then
new_result = Application.Round(result, -1)
Else
If result 100 Then
new_result = Round(result, 0)
Else
If result 10 Then
new_result = Round(result, 1)
Else
new_result = Round(result, 2)
End If
End If
End If
End If

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
ups.com...
I've searched the archives and can't really find what I am looking
for. I have a macro that will format a result based upon the number
of digits the result contains. If the result is 1000 then I need to
round the result to the 10's place (e.g. 1042 would be rounded to
1040). When I put in Round(result, -1), I get a generic error. I know
this is the Excel formula. I have copied the code below. If anyone
can help it would be greatly appreciated.

If result < 0.69 Then
new_result = "<0.69"
Else
If result 1000 Then
new_result = Round(result, -1) '''''''''This is where
the error occurs.''''''''''
Else
If result 100 Then
new_result = Round(result, 0)
Else
If result 10 Then
new_result = Round(result, 1)
Else
new_result = Round(result, 2)
End If
End If
End If
End If



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,316
Default Rounding issue

Try using the following:

New_Result = Application.WorksheetFunction.Round("A1:A10", -1)

--
Kevin Backmann


" wrote:

I've searched the archives and can't really find what I am looking
for. I have a macro that will format a result based upon the number
of digits the result contains. If the result is 1000 then I need to
round the result to the 10's place (e.g. 1042 would be rounded to
1040). When I put in Round(result, -1), I get a generic error. I know
this is the Excel formula. I have copied the code below. If anyone
can help it would be greatly appreciated.

If result < 0.69 Then
new_result = "<0.69"
Else
If result 1000 Then
new_result = Round(result, -1) '''''''''This is where
the error occurs.''''''''''
Else
If result 100 Then
new_result = Round(result, 0)
Else
If result 10 Then
new_result = Round(result, 1)
Else
new_result = Round(result, 2)
End If
End If
End If
End If


  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default Rounding issue

On Aug 28, 11:24 am, "Bob Phillips" wrote:
Try this

If result < 0.69 Then
new_result = "<0.69"
Else
If result 1000 Then
new_result = Application.Round(result, -1)
Else
If result 100 Then
new_result = Round(result, 0)
Else
If result 10 Then
new_result = Round(result, 1)
Else
new_result = Round(result, 2)
End If
End If
End If
End If

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

wrote in message

ups.com...

I've searched the archives and can't really find what I am looking
for. I have a macro that will format a result based upon the number
of digits the result contains. If the result is 1000 then I need to
round the result to the 10's place (e.g. 1042 would be rounded to
1040). When I put in Round(result, -1), I get a generic error. I know
this is the Excel formula. I have copied the code below. If anyone
can help it would be greatly appreciated.


If result < 0.69 Then
new_result = "<0.69"
Else
If result 1000 Then
new_result = Round(result, -1) '''''''''This is where
the error occurs.''''''''''
Else
If result 100 Then
new_result = Round(result, 0)
Else
If result 10 Then
new_result = Round(result, 1)
Else
new_result = Round(result, 2)
End If
End If
End If
End If


Thank you, Bob, for your quick response. This little trick worked.
And thank you Kevin and Dave for your advice as well. It is very much
appreciated.

Asa

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Rounding issue Kimo Excel Worksheet Functions 5 July 31st 07 12:37 AM
I need a formula with rounding up & rounding down to the nearest . Tony Kay Excel Worksheet Functions 3 May 29th 07 11:13 PM
Excel Rounding Issue AccentStripe Excel Discussion (Misc queries) 3 May 18th 07 09:15 PM
Rounding issue with Goal Seek for IRR Curt Excel Worksheet Functions 2 May 23rd 06 04:51 PM
Another rounding issue Biff Excel Worksheet Functions 2 June 20th 05 01:10 PM


All times are GMT +1. The time now is 12:06 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"