Round to nearest dollar & half dollar

I've tried searching the solved questions to help me with this but nothing seemed to work... without editing that is, and my ability to edit code is quite minimal.  Here is what I have and need:

Have: $2.18  Need: $2.00
Have: $3.29  Need: $3.50
Have: $2.55  Need: $2.50
Have: $1.77  Need: $2.00

Basically:
0.00 - 0.25 = 0.00
0.26 - 0.75 = 0.50
0.76 - 0.99 = 1.00

Thanks in advanced for your help.
TBayXXXVAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
0
dqmqCommented:
The general equation equation for that is

Round(Amount*/dollars),precision)*dollars

For our case, specifically, you want to round on  .50 dollars, so it becomes:
Round(2.18/.5,2)*.5 = 2.00
Round(3.29/.5,2)*.5 =3.5

So, do this:
Round(Amt/.5,2)*.5
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
btw ... in the thread I posted ... note that there are **several** ideas that don't quite work.

but .... look at cactus_data's solution (not accepted, but should have been) near the bottom of the post ... VERY ELEGANT, simple ... less is more.  This subject was complete hashed out in that thread.  Please check it out.

mx
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Jim P.Commented:
Try this function on. Just put it in a module and then save it and call it.
-----------------------------------------------
Public Function RoundToHalf(InputAmt As Double) As Double

Dim InVal As String

InVal = Format(InputAmt, "0.00")

Select Case CInt(Right(InVal, 2))
    Case Is <= 25
        RoundToHalf = CDbl(Left(InVal, Len(InVal) - 2) & "00")
    Case 26 To 75
        RoundToHalf = CDbl(Left(InVal, Len(InVal) - 2) & "50")
    Case Is >= 76
        RoundToHalf = CDbl(Left(InVal, Len(InVal) - 3)) + 1
End Select

End Function
0
TBayXXXVAuthor Commented:
DatabaseMX: That is the one I originally tried to work with and like cactus_data's said, I'd much rather handle currency as numeric, but I can't get it to work the way I need it.  It rounds everything up, while I need some rounded down.  I am not very Code savvy so my attempts at adjusting code is a bit limited.
0
twintaiCommented:
I tested the following code I created and it worked almost perfectly.

If VBA.Mid(Me.Test1.Value, ((VBA.InStr(1, Me.Test1.Value, ".")) + 1), ((VBA.Len(Me.Test1.Value)) - (VBA.InStr(1, Me.Test1.Value, ".")))) <= 25 Then
    Me.Field.Value = VBA.Mid(Me.Test1.Value, 2, ((VBA.InStr(1, Me.Test1.Value, ".")) - 1)) & "00"
End If

If VBA.Mid(Me.Test1.Value, ((VBA.InStr(1, Me.Test1.Value, ".")) + 1), ((VBA.Len(Me.Test1.Value)) - (VBA.InStr(1, Me.Test1.Value, ".")))) > 25 And VBA.Mid(Me.Test1.Value, ((VBA.InStr(1, Me.Test1.Value, ".")) + 1), ((VBA.Len(Me.Test1.Value)) - (VBA.InStr(1, Me.Test1.Value, ".")))) <= 50 Then
    Me.Field.Value = VBA.Mid(Me.Test1.Value, 2, ((VBA.InStr(1, Me.Test1.Value, ".")) - 1)) & "50"
End If

If VBA.Mid(Me.Test1.Value, ((VBA.InStr(1, Me.Test1.Value, ".")) + 1), ((VBA.Len(Me.Test1.Value)) - (VBA.InStr(1, Me.Test1.Value, ".")))) > 50 And VBA.Mid(Me.Test1.Value, ((VBA.InStr(1, Me.Test1.Value, ".")) + 1), ((VBA.Len(Me.Test1.Value)) - (VBA.InStr(1, Me.Test1.Value, ".")))) <= 75 Then
    Me.Field.Value = VBA.Mid(Me.Test1.Value, 2, ((VBA.InStr(1, Me.Test1.Value, ".")) - 1)) & "75"
End If

If VBA.Mid(Me.Test1.Value, ((VBA.InStr(1, Me.Test1.Value, ".")) + 1), ((VBA.Len(Me.Test1.Value)) - (VBA.InStr(1, Me.Test1.Value, ".")))) > 75 Then
    Me.Field.Value = (VBA.Round(Me.Test1.Value, 0))
End If
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
OK ... here you go:

Let 'y' be your field.  For simplicity ...

y = Format(y, "0.00")

x = "." & Right(y, 2)

z = Int(y) + Switch(x > 0.00 And x <= 0.25, 0.00, x >= 0.26 And x <= 0.75, 0.5, x >= 0.76 And x <= 0.99, 1)

y= Format(z, "0.00")   >> converted value

mx
0
dqmqCommented:
Once again, rounding to nearest half-dollar is this simple:

Round(YourAmt/.5,2)*.5

or, if this makes more sense:
Round(YourAmt * 2, 2) / 2

What am I missing?
0
dqmqCommented:
Oh, #$%#@$, I just figured out what I'm missing. Try this:
Round(YourAmt*2, 0 ) / 2
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
oops:
dqmq

Round(0.75*2, 0 ) / 2 = 1.00,   not 0,50

Weird ... only that one case (0.75) fails !

"0.00 - 0.25 = 0.00
0.26 - 0.75 = 0.50
0.76 - 0.99 = 1.00"

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Seriously ... If you use the Switch() ... you can see that all of the 'boundary conditions' are covered ... there is no guess work ...it has to work.  And, you could adjust it for different scenarios.

mx
0
dqmqCommented:
>Round(0.75*2, 0 ) / 2 = 1.00,   not 0,50
>Weird ... only that one case (0.75) fails

OK, I totally missed that as .75 would normally round "up".  So, there's the revised formula:

Int((AMT + .24) * 2 ) / 2

I changed from Round to Int because I discovered VBA applies "bankers rounding"


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TBayXXXVAuthor Commented:
Thanks all of you for your imput, I really learned a lot.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Glad to be of assistance to dqmq :-)

mx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.