Round to 2 Decimal Places

jakjo
Lounger
Posts: 25
Joined: 28 May 2022, 00:57

Round to 2 Decimal Places

Post by jakjo »

Hello everyone
The following code works fine but I need your support to get the desired results
To clarify things
I have two sheets in my workbook. One is "Customers data" and second one is "Desired results"
In the Customers data sheet, column A contains the customer number and In the output sheet, this number is manually recorded in cell "C5".
My requirement if you change the customer number, this customer's data must be obtained from horizontal to vertical with his personal data as shown in Desired results sheet ..The problem I have when I run this code the numbers end up having 12 or 13 decimal places In columns A, E and G
I only need two decimal places To get the correct results ... Right now, I am wondering ...
Is there a way to edit this to Rounding Decimal Places Correctly? ... I can provide additional clarification if needed
please take a look ... Thanks in advance for any help you can provide.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Round to 2 Decimal Places

Post by HansV »

Welcome to Eileen's Lounge!

It's a matter of rounding the "S" amounts to whole numbers:

Code: Select all

Sub test()
    Dim x, e, r As Range, i As Long, mySum As Double
    Application.ScreenUpdating = False
    With Sheets("Customers data")
        .Range("a8", .Range("a" & Rows.Count).End(xlUp)).Name = "Cust"
        x = Application.Match(Sheets("Desired results").[c5], .Range("cust"), 0)
    End With
    If IsError(x) Then Exit Sub
    With Sheets("Desired results")
        .[c2] = "=index(offset(cust,,4)," & x & ")"
        .[c4] = "=index(offset(cust,,6)," & x & ")"
        .[c6:c8] = "=index(offset(cust,,row(a19))," & x & ")"
        .[i4:i5] = "=index(offset(cust,,row(a15))," & x & ")"
        .[i6] = "=index(offset(cust,,49)," & x & ")"
        .[i8] = "=index(offset(cust,,47)," & x & ")"
        For Each r In .[c2,c4,c6:c8,i4:i5,i6,i8].Areas
            r.Value = r.Value
        Next r
        With .[a12:b43]
            .Formula = Array("=if(b12<>"""",iferror(round(mod(index(offset(cust,,row(a89))," & x & "),1)*100,0),""""),"""")", _
                    "=iferror(text(rounddown(index(offset(cust,,row(a89))," & x & "),0),""0;-0;""),"""")")
            .Value = .Value
        End With
        mySum = Application.Sum(.[b12:b43]) + Application.Sum(.[a12:a43]) / 100
       .Cells(44, "a").Resize(, 2) = Array((mySum - Fix(mySum)) * 100, Fix(mySum))
        For Each e In Array(Array(12, 2, 122), Array(14, 21, 128), Array(35, 8, 155))
            With .Cells(e(0), "g").Resize(e(1), 2)
                .Formula = Array("=if(h" & e(0) & "<>"""",iferror(round(mod(index(offset(cust,,row(a" & e(2) & "))," & x & "),1)*100,0),""""),"""")", _
                    "=iferror(text(rounddown(index(offset(cust,,row(a" & e(2) & "))," & x & "),0),""0;-0;""),"""")")
                .Value = .Value
            End With
        Next e
        x = Array(12, 14, 23, 41, 43)
        For i = 1 To UBound(x)
            mySum = Application.Sum(.Range("h" & x(i - 1) & ":h" & x(i) - 1)) + Application.Sum(.Range("g" & x(i - 1) & ":g" & x(i) - 1)) / 100
            .Cells(x(i) - 1, "e").Resize(, 2) = Array(Round((mySum - Fix(mySum)) * 100, 0), Fix(mySum))
        Next i
        mySum = [sum(f12:f42)+sum(e12:e42)/100]
        .Cells(43, "e").Resize(, 2) = Array(Round((mySum - Fix(mySum)) * 100#, 0), Fix(mySum))
        mySum = .[(b44+a44/100)-(f43+e43/100)]
        .[e44:f44] = Array(Round((mySum - Fix(mySum)) * 100, 0), Fix(mySum))
    End With
    Application.ScreenUpdating = True
End Sub
Best wishes,
Hans

jakjo
Lounger
Posts: 25
Joined: 28 May 2022, 00:57

Re: Round to 2 Decimal Places

Post by jakjo »

That's great. Thank you very much Mr. Hans for your great help and sorry for disturbing you With more edits
I think we need to edit these two lines to rounding Decimal Places in net total Amount

Code: Select all

     mySum = .[(b44+a44/100)-(f43+e43/100)]
    .[e44:f44] = Array(Round((mySum - Fix(mySum)) * 100, 0), Fix(mySum))
  
  End With
    Application.ScreenUpdating = True
End Sub
I mean that if the result of the total net amount as an integer ... In this case, the result of decimal places should be zeros.
The net amounts= Total sales - total discounts
The net amounts is assumed to be = 3300.00 not 3299.100 ... As per the attached example.
Please take a look at the net amount in the two cells ( F44 & E44 ) To find out what I mean
Is there any additional Conditions should I add to achieve this?
Your time on this is greatly appreciated!

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Round to 2 Decimal Places

Post by HansV »

Change the line

Code: Select all

     mySum = .[(b44+a44/100)-(f43+e43/100)]
to

Code: Select all

    mySum = Round(.[(b44+a44/100)-(f43+e43/100)], 2)
Best wishes,
Hans

jakjo
Lounger
Posts: 25
Joined: 28 May 2022, 00:57

Re: Round to 2 Decimal Places

Post by jakjo »

Now it is perfect and awesome .. Thank you very much Mr. Hans for your great help.
Best Regards