 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / December 2007Rounding
Hi All, Excel has a function (mround), that returns the entered number rounded to the number you enter, i.e =mround(13,5) would return 15, because I told it to round 13 to the nearest 5. I would like to create a function in VBA to do this. Any ideas? Thanks in advance. Rich To do it with a Worksheet function without using MROUND() you would use something like: =ROUND(E23/5,0)*5 Do the same sort of thng in VBA.  SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > Hi All, > Excel has a function (mround), that returns the entered number rounded to [quoted text clipped - 8 lines] > > Rich Thanks -- that works. Here's what I did Function RoundToNearest(numin, roundto) RoundToNearest = ((numin \ roundto) + 1) * roundto End Function > To do it with a Worksheet function without using MROUND() you would use > something like: [quoted text clipped - 15 lines] >> >> Rich That is actually rounding up to round to the nearest try: Function RoundToNearest(numin, roundto) RoundToNearest = Int(numin / roundto + 0.5) * roundto End Function You may also want to add: Application.Volatile at the start of the function. SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > Thanks -- that works. > Here's what I did [quoted text clipped - 22 lines] >>> >>> Rich Is that function really doing what you want? First, you named it RoundToNearest, but it actually should be called RoundUpTo. For example, use 11 for numin and 5 for roundto and you get 15 even though the nearest multiple of 5 to 11 is 10. Also, when you set numin equal to 10 and roundto equal to 5, your function returns 15 even though the "nearest" multiple of 5 to numin is itself... 10. Are these result really what you wanted? If not, tells how you want your rounding to take place and we come up with a function for you. Also, as a side question, can any of your numbers, either numin or roundto, contain fractional elements to them? Rick > Thanks -- that works. > Here's what I did [quoted text clipped - 22 lines] >>> >>> Rich Thanks Rick -- You are right -- it doesn't do what I want -- did with the numbers I tested. I replaced it with RoundToNearest = Int(numin / roundto + 0.5) * roundto And yes, numin could be a fraction, but roundto will always be an integer > Is that function really doing what you want? First, you named it > RoundToNearest, but it actually should be called RoundUpTo. For example, [quoted text clipped - 34 lines] >>>> >>>> Rich
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.