Roundup and roundown for specific decimals

[dinukamp]

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?

[el84]

ChatGPT offers this function

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

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...)

[Zvoni]

And once more the reason, why relying on something like ChatGPT.......*sigh*

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

[baka]

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

[Zvoni]

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

[baka]

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.

[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

[Zvoni]

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"

[Elroy]

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.

[ahenry]

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.

[Zvoni]

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

[Shaggy Hiker]

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.

[Elroy]

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).

[OptionBase1]

Superman 3 -> Office Space.

[Elroy]

Superman 3 -> Office Space.

Ahhh, yeah, Superman 3 was the one I was thinking of.

[vb6forever]

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 :>).

[Shaggy Hiker]

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.

[dilettante]

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

[el84]

And once more the reason, why relying on something like ChatGPT.......*sigh*

You are right, of course...

[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.

[Elroy]

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)?

[dinukamp]

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

[Zvoni]

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

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

[dinukamp]

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