Rounding to nearest 25,000

[CJD#11]

This is not an ordinary rounding issue. Here's what I need to do. I have to round any number up to the nearest 25,000 increment.

For example:

124,698 would need to round to 125,000
126,125 would need to round to 150,000
1,278,189 would need to round to 1,300,000

Anyone know of a mathematical formula for doing this? Or any good ideas as to how to accomplish this? THANKS!

[MartinLiss]

MsgBox YourValue + 25000 - YourValue Mod 25000

[FunkyDexter]

Use an integer division (same as regular division but use \ instead of /). This will ALWAYS round down.

You'll need to add 1 (to make it a round up) unless it divided exactly (cos then you shouldn't be rounded at all). You check that by using Mod.

So:-
[Highlight=VB]
answer = x\25000
if answer Mod 25000 > 0 then
answer = answer + 1
end if

[Pradeep1210]

Something like this:
(replace with appropriate variables)

Msgbox Round(124698 /1000) * 1000

Pradeep

[MartinLiss]

My solution does not work properly if decimals are involved.

[MartinLiss]

Something like this:
(replace with appropriate variables)

Msgbox Round(124698 /1000) * 1000

Pradeep

That doesn't work. eg 134998 -> 135000

[dglienna]

If you want 250 to round down, just add 1 to t

Option Explicit
 
Private Sub Form_Load()
 Dim x As Long
 Dim t As Long
 x = Val(InputBox("Enter a Number"))
 Do While x <> 0
   t = (x \ 25000) * 25000
   if t=0 then t=t+ 1 ' if you want it rounded up
   MsgBox t
   x = Val(InputBox("Enter a Number"))
 Loop
 End
End Sub

[MartinLiss]

If you want 250 to round down, just add 1 to t

Option Explicit
 
Private Sub Form_Load()
 Dim x As Long
 Dim t As Long
 x = Val(InputBox("Enter a Number"))
 Do While x <> 0
   t = (x \ 25000) * 25000
   if t=0 then t=t+ 1 ' if you want it rounded up
   MsgBox t
   x = Val(InputBox("Enter a Number"))
 Loop
 End
End Sub

Did someone ask how to round down?

[dglienna]

Using my method, 250 would be the nearest 25000, which would be zero
If he wanted it like that, he'd have to remove the line.
Numbers up to 24999 would show up as 0.

If he wants 24999 to point to 25000, then I'd have to rewrite some code.
My code just finds number of times 25000 goes into the number.

EDIT: This seems to work

Option Explicit
 
Private Sub Form_Load()
 Dim x As Long
 Dim t As Long
 x = Val(InputBox("Enter a Number"))
 Do While x <> 0
   t = (x \ 25000) * 25000
   If Abs(x - t) > 12500 Then
     t = t + 25000
   End If
   MsgBox t
   x = Val(InputBox("Enter a Number"))
 Loop
 End
End Sub

But it still rounds 500 down to 0, and I wasn't sure if thats what the poster wanted.

[Harsh Gupta]

thumbs up ML............i was abt to post the same thing

[MartinLiss]

"Using my method, 250 would be the nearest 25000, which would be zero" ==> What does that mean?

My code just finds number of times 25000 goes into the number.==> No it doesn't.

[techgnome]

Using my method, 250 would be the nearest 25000, which would be zero
If he wanted it like that, he'd have to remove the line.
Numbers up to 24999 would show up as 0.

If he wants 24999 to point to 25000, then I'd have to rewrite some code.
My code just finds number of times 25000 goes into the number.

Well, since the OP said

I have to round any number up to the nearest 25,000 increment.

And

124,698 would need to round to 125,000

I would assume that 24999 would point to 25000, as would 250. Or even 1 for that matter. The only time the result should be 0 is if the value is 0 to begin with. At 25001, it becomes 50000. But your solution would still work, by adding 1 to t as you mentioned.

-tg

[dglienna]

Hmm. I read it before I went out, and posted when I got back. Must have missed that. Thanks.

[Pradeep1210]

That doesn't work. eg 134998 -> 135000

That example rounded it off to nearest 1000.
Try this:

Private Sub Command1_Click()
    Dim nNum As Long, nRound As Long
    nRound = 25000
    nNum = InputBox("Enter Number:")
    MsgBox Round(nNum / nRound) * nRound
End Sub

Pradeep

[BlueRose]

i think which decimal place you want to round is important, first this must be decided then calculation performed.

for example if you want to round 124,698 to 125,000
firstly you must divide number by 1000 and round it
secondly zeros needed must be added

[Joacim Andersson]

Use an integer division (same as regular division but use \ instead of /). This will ALWAYS round down.

You know what? That's not true. The integer division operator will round to the nearest integer, not always round down.

[MartinLiss]

That example rounded it off to nearest 1000.
Try this:

Private Sub Command1_Click()
    Dim nNum As Long, nRound As Long
    nRound = 25000
    nNum = InputBox("Enter Number:")
    MsgBox Round(nNum / nRound) * nRound
End Sub

Pradeep

No, sorry 35000 -> 25000

[dglienna]

"Using my method, 250 would be the nearest 25000, which would be zero" ==> What does that mean?

My code just finds number of times 25000 goes into the number.==> No it doesn't.

The first example was rounding down to the nearest 25000

Using my method, 250 goes into 25000, zero times

My original code just found number of times a number goes into 25000 if it is over 25000

My explanatiion was a bit off. Sorry

[wspguy]

Here, try this...

Private Function getRnd(ByRef lNumIn As Long, lRnd As Long) As Long
    Dim lNew As Long
    Dim lTmp As Long
    
    On Error GoTo Err_H
    'lNumIn = Number to Round off
    'lRnd   = Control Variable
    
    lTmp = lNumIn Mod lRnd
    
    If lTmp < (lRnd / 2) Then
        lNew = lNumIn - lTmp
    Else
        lNew = lNumIn + (lRnd - lTmp)
    End If
    
    getRnd = lNew
Exit Function
Err_H:
    getRnd = -1
End Function

I hope I'm on the right track for you.

[Pradeep1210]

No, sorry 35000 -> 25000

That's right.
35000 is nearer to 25000 than 50000
The midpoint is 37500. So after this it will round to 50000


Pradeep

[MartinLiss]

But as I pointed out to dglienna, the poster wants to round UP.

[Joacim Andersson]

I don't get it. Why are you all givin new complex answers when Marty showed such a simple brilliant solution in the first reply?

[techgnome]

I have this theory that I think has just been further proven by this very thread:
Ask 10 developers how to do something and you will get 23 different opinions.

-tg

[CJD#11]

I don't get it. Why are you all givin new complex answers when Marty showed such a simple brilliant solution in the first reply?

Really? I don't see how that would work. And yes, all numbers(regardless of decimal places, which would be 2 b/c all numbers are monetary values) have to be rounded up to the next 25000 increment.

[techgnome]

The first example was rounding down to the nearest 25000

Using my method, 250 goes into 25000, zero times

My original code just found number of times a number goes into 25000 if it is over 25000

My explanatiion was a bit off. Sorry

You mean 25000 goes into 250 0 times.... 250 will go into 25000 100 times....

So the logic should be to see how many times 25000 will go into a number....

The integer division operator will round to the nearest integer, not always round down. -- actualy it goes to the nearest number, unles it's at the break point, in which case it goes to the nearest EVEN integer.

So Round(1.5) gives you 2, but Round(2.5) also gives 2..... I don't know how many times that caused us greif in our app.

-tg

[Joacim Andersson]

Really? I don't see how that would work. And yes, all numbers(regardless of decimal places, which would be 2 b/c all numbers are monetary values) have to be rounded up to the next 25000 increment.

MsgBox Int(YourNumber) + 25000& - Int(YourNumber) Mod 25000&

EDIT: That works unless your number is negative in which case you need to subtract 25000 first.

If YourNumber < 0 Then
    YourNumber = YourNumber - 25000&
End If
MsgBox Int(YourNumber) + 25000& - Int(YourNumber) Mod 25000&

[CJD#11]

So Round(1.5) gives you 2, but Round(2.5) also gives 2..... I don't know how many times that caused us greif in our app.

-tg

Been there, done that!

[Joacim Andersson]

The integer division operator will round to the nearest integer, not always round down. -- actualy it goes to the nearest number, unles it's at the break point, in which case it goes to the nearest EVEN integer.

When I claimed that the integer division operator not always will round down. I was talking about the integer division operator (the backslash) not the Round function. Maybe I should elaborate on that, what I mean is that if not both operands are integers VB will first round the operands to the closest integer number before it does the division in which case it rounds down. So for example 17 \ 3 will be 5, meaning it rounds down. But 17.6 \ 3 will become 6, which means that VB first rounds 17.6 to 18 before it does the division.

[Pradeep1210]

But as I pointed out to dglienna, the poster wants to round UP.

Oh! I missed that UP
Here's the fix:

Private Sub Command1_Click()
    Dim nNum As Long, nRound As Long
    nRound = 25000
    nNum = InputBox("Enter Number:")
    If nNum Mod nRound = 0 Then
        MsgBox ((nNum \ nRound)) * nRound
    Else
        MsgBox (1 + (nNum \ nRound)) * nRound
    End If
End Sub

[Joacim Andersson]

I still think Marty had the best solution. With the simple fix of using the Int function as I showed above it works perfectly.

[CJD#11]

Oh! I missed that UP
Here's the fix:

Private Sub Command1_Click()
    Dim nNum As Long, nRound As Long
    nRound = 25000
    nNum = InputBox("Enter Number:")
    If nNum Mod nRound = 0 Then
        MsgBox ((nNum \ nRound)) * nRound
    Else
        MsgBox (1 + (nNum \ nRound)) * nRound
    End If
End Sub

Doing this math using the nubmer 26,000, the end result was 51000, which would not meet my needs. It would need to = 50,000. Or did I look at that wrong?

[Pradeep1210]

Doing this math using the nubmer 26,000, the end result was 51000, which would not meet my needs. It would need to = 50,000. Or did I look at that wrong?

To me it gives 50000

Did you copy the code exactly as it is ?

Pradeep

[CJD#11]

O.k., lets back off a little. Forget the whole 25000 thing. How about just a way to round any number UP every time?

Ex 4.1 = 5
4.3 = 5
4.6 = 5
5.1 = 6

From what I've read, there is no VB command to do this, so it has to be 'forced' to happen.

[Joacim Andersson]

I still don't get it... CJD#11 did you even try this out?

MsgBox Int(YourNumber) + 25000& - Int(YourNumber) Mod 25000&

[Joacim Andersson]

O.k., lets back off a little. Forget the whole 25000 thing. How about just a way to round any number UP every time?

Ex 4.1 = 5
4.3 = 5
4.6 = 5
5.1 = 6

From what I've read, there is no VB command to do this, so it has to be 'forced' to happen.

MsgBox Int(theNumber) + 1

[techgnome]

Stupid timeouts..... accidently double posted.....
Plz disregard.

-tg

[CJD#11]

To me it gives 50000

Did you copy the code exactly as it is ?

Pradeep

AHHHH, o.k. that did work.

I did the math out by hand, not using the code.

[Pradeep1210]

MsgBox Int(theNumber) + 1

It should be :

If Int(theNumber) = theNumber Then
    MsgBox theNumber
Else
    MsgBox Int(theNumber) + 1
End If

Pradeep

[Joacim Andersson]

Not if you want to round up every number.

[techgnome]

When I claimed that the integer division operator not always will round down. I was talking about the integer division operator (the backslash) not the Round function. Maybe I should elaborate on that, what I mean is that if not both operands are integers VB will first round the operands to the closest integer number before it does the division in which case it rounds down. So for example 17 \ 3 will be 5, meaning it rounds down. But 17.6 \ 3 will become 6, which means that VB first rounds 17.6 to 18 before it does the division.

I was smoking something when I wrote that.... the air has now cleared and I don't know *what* I was thinking.... but what will VB do to 18.5 vs 17.5 before doing int division?

-tg