# Thread: Roundup and roundown for specific decimals

1. ## Roundup and roundown for specific decimals

is it possible to write a formula to do this?

i want to "round down" up to including .5
and "round up" whic are between .6 to .9

1.1 to 1.5 -> 1
1.6 to 1.9 -> 2

if I want to round data on this sheet how do write the code?

2. ## Re: Roundup and roundown for specific decimals

ChatGPT offers this function

Code:
```Function CustomRound(ByVal value As Double) As Long
Dim integerPart As Long
Dim decimalPart As Double

integerPart = Int(value)
decimalPart = value - integerPart

If decimalPart <= 0.5 Then
CustomRound = integerPart
Else
CustomRound = integerPart + 1
End If
End Function```
So I used it thus
Code:
```Option Explicit

Function CustomRound(ByVal value As Double) As Long
Dim integerPart As Long
Dim decimalPart As Double

integerPart = Int(value)
decimalPart = value - integerPart

If decimalPart <= 0.5 Then
CustomRound = integerPart
Else
CustomRound = integerPart + 1
End If
End Function

Private Sub Command1_Click()
MsgBox (CustomRound(2.6))  'also tested for 2.5 and other decimals
End Sub```
ChatGPT seems excellent for simple vb6 questions like I used to ask here and provided you specify Vb6 it omits NET answers that highly irritate when googling. (BTW it is often wrong on facts but seems excellent for vb6 simple stuff... so far...)

3. ## Re: Roundup and roundown for specific decimals

And once more the reason, why relying on something like ChatGPT.......*sigh*
Code:
```Sub main()
Dim d As Double
d = 1.1
Debug.Print d & " --> " & Round(d - 0.1, 0)
d = 1.2
Debug.Print d & " --> " & Round(d - 0.1, 0)
d = 1.3
Debug.Print d & " --> " & Round(d - 0.1, 0)
d = 1.4
Debug.Print d & " --> " & Round(d - 0.1, 0)
d = 1.5
Debug.Print d & " --> " & Round(d - 0.1, 0)
d = 1.6
Debug.Print d & " --> " & Round(d - 0.1, 0)
d = 1.7
Debug.Print d & " --> " & Round(d - 0.1, 0)
d = 1.8
Debug.Print d & " --> " & Round(d - 0.1, 0)
d = 1.9
Debug.Print d & " --> " & Round(d - 0.1, 0)
d = 2
Debug.Print d & " --> " & Round(d - 0.1, 0)
End Sub```
Returns
1.1 --> 1
1.2 --> 1
1.3 --> 1
1.4 --> 1
1.5 --> 1
1.6 --> 2
1.7 --> 2
1.8 --> 2
1.9 --> 2
2 --> 2

4. ## Re: Roundup and roundown for specific decimals

Code:
```Private Function Round2%(Value!)
If Value < 0 Then Round2 = CInt(Value + 0.1) Else Round2 = CInt(Value - 0.1)
End Function```
this will also fix the negative, since u need to "reverse" it below 0
it is possible to use Round() as well as Zvoni showed instead of CInt
U can also use CLng if u need values below or above -30k/30k

5. ## Re: Roundup and roundown for specific decimals

Originally Posted by baka
Code:
```Private Function Round2%(Value!)
If Value < 0 Then Round2 = CInt(Value + 0.1) Else Round2 = CInt(Value - 0.1)
End Function```
this will also fix the negative, since u need to "reverse" it below 0
it is possible to use Round() as well as Zvoni showed instead of CInt
U can also use CLng if u need values below or above -30k/30k
Agreed. Missed the negatives, since OP didn't mention them

6. ## Re: Roundup and roundown for specific decimals

yeah. we don't know what he needs. maybe just 1-10?

another thing, I compared the chatgpt, heres the result (showing where it differs only mine & chatgpt)

-0,6=-1=-1
-0,5=0=-1
-0,4=0=0

so, at -0.6 and -0.4 we get the same values
but -0.5 my function return 0, as I assume the reverse

the question is.
should -0.5 be 0 or -1?

poe told me:
chatgpt is called: "round half up"
while my function is called: "round half away from zero"

so both are correct depending on the needs of the user.

7. ## Re: Roundup and roundown for specific decimals

I was taught that >= .0 and < .5 should round down and >=.5 and <= .9 should round up (round half up).

So .5 -> 1
-.5 -> -1

8. ## Re: Roundup and roundown for specific decimals

Originally Posted by 2kaud
I was taught that >= .0 and < .5 should round down and >=.5 and <= .9 should round up (round half up).

So .5 -> 1
-.5 -> -1
Called Banker's Rounding.
But for whatever reason OP needed different "borders"

9. ## Re: Roundup and roundown for specific decimals

What's wrong with the Round() function? And, if different "borders" are needed just add (or subtract) small amounts to the number before it's rounded.

Always want .###5 to round up? Just add .00000001 to the number before rounding.

10. ## Re: Roundup and roundown for specific decimals

Originally Posted by Zvoni
Called Banker's Rounding.
But for whatever reason OP needed different "borders"
No, in banker's rounding, 0.5 rounds to zero, and 1.5 rounds to two. If you're exactly on x.5 it rounds to the nearest even integer.

11. ## Re: Roundup and roundown for specific decimals

Originally Posted by ahenry
No, in banker's rounding, 0.5 rounds to zero, and 1.5 rounds to two. If you're exactly on x.5 it rounds to the nearest even integer.
Yeah, i was wrong, You're right.
Got confused there myself

12. ## Re: Roundup and roundown for specific decimals

I would question the whole premise. The point of rounding is to come out with whole integers (or whatever decimal place you are rounding to), where the sum of a large number of errors will be 0. Basically, you don't want the fractional parts to bias the results over time.

The rounding requested will definitely bias the results low over time. That's because four digits round up, five digits round down, and one remains unchanged. Therefore, with a random distribution of numbers being rounded, more will round down than will round up.

13. ## Re: Roundup and roundown for specific decimals

Originally Posted by Shaggy Hiker
Basically, you don't want the fractional parts to bias the results over time.
Maybe you do.

If I were running a bank (and it was legal), I might want an algorithm that rounded all loan interest calculations up (to the nearest penny), and rounded all fractional payouts down (to the nearest penny). Depending on the number of transactions, it might amount to a chunk of change. They even once made a movie about this (but I don't remember the name of it).

14. ## Re: Roundup and roundown for specific decimals

Superman 3 -> Office Space.

15. ## Re: Roundup and roundown for specific decimals

Originally Posted by OptionBase1
Superman 3 -> Office Space.
Ahhh, yeah, Superman 3 was the one I was thinking of.

16. ## Re: Roundup and roundown for specific decimals

Elroy:

If I were running a bank (and it was legal) . . . They even once made a movie about this (but I don't remember the name of it).
There was an actual case (Fairfax Bank of Virginia) if I recall correctly, sometime between 1973-1975, where rounding was used. The amount of money by rounding pennies and putting it into the programmers own account was quite a bit. Go directly to jail, and do not collect \$200 :>).

17. ## Re: Roundup and roundown for specific decimals

Originally Posted by vb6forever
Elroy:

There was an actual case (Fairfax Bank of Virginia) if I recall correctly, sometime between 1973-1975, where rounding was used. The amount of money by rounding pennies and putting it into the programmers own account was quite a bit. Go directly to jail, and do not collect \$200 :>).
Office Space.

18. ## Re: Roundup and roundown for specific decimals

Canada doesn't have pennies any more. Studies say the rounding gets done to the disadvantage of consumers. Now they toss around the idea of eliminating nickels as well.

Penny ends, rounding begins, profits made

19. ## Re: Roundup and roundown for specific decimals

Originally Posted by Zvoni
And once more the reason, why relying on something like ChatGPT.......*sigh*
You are right, of course...

20. ## Re: Roundup and roundown for specific decimals

Well, that study says it, but it's based on an already disingenuous practice by the stores that now works slightly more explicitly in their favor. That's makes the whole thing more nuanced than that report talks about. So many things are priced to .99 or .98. That isn't done because stores have pulled that number out of the air, nor is it the actual cost plus profit margin. They are charging that price because they have long felt (perhaps correctly) that people will mostly look at the leftmost number. They feel such a price gives them a psychological edge. Now it gives them a financial edge, as well, so long as people only buy one item.

If they hadn't been playing for psychological advantage before, then getting rid of the penny would have made no difference.

21. ## Re: Roundup and roundown for specific decimals

Originally Posted by Shaggy Hiker
Well, that study says it, but it's based on an already disingenuous practice by the stores that now works slightly more explicitly in their favor. That's makes the whole thing more nuanced than that report talks about. So many things are priced to .99 or .98. That isn't done because stores have pulled that number out of the air, nor is it the actual cost plus profit margin. They are charging that price because they have long felt (perhaps correctly) that people will mostly look at the leftmost number. They feel such a price gives them a psychological edge. Now it gives them a financial edge, as well, so long as people only buy one item.

If they hadn't been playing for psychological advantage before, then getting rid of the penny would have made no difference.
They've done social psychology studies that show that it does work. Take gasoline/petrol for instance. It's sold to the nearest 1/10th of a cent, so that the "big numbers" show a penny less per gallon (but with \$.009 down in the fine print). If you saw 3.39 9/10 on one corner and 3.40 on the other corner, where are you going (and you're only saving a penny per 10 gallons)?

22. ## Re: Roundup and roundown for specific decimals

if I want to round data on this sheet how do write the code?

23. ## Re: Roundup and roundown for specific decimals

Besides the Fact we're suddenly in Excel, and not VB

In B2 --> =ROUND(A1-0.1,0)
and copy down

24. ## Re: Roundup and roundown for specific decimals

Originally Posted by Zvoni
Besides the Fact we're suddenly in Excel, and not VB

In B2 --> =ROUND(A1-0.1,0)
and copy down
This is a effective solution

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•