Help needed: Custom rounding of numbers in Excel 2010/13 meeting 1 of 6 criteria

[hainer76]

Happy 2014 to you all
Hi
I would really appreciate any help and/or guidance offered to hopefully resolve a custom rounding problem in Excel 2010/2013. The function will be applied to a results column containing numbers with two decimal places.
For general layout please see Table 1

[TABLE="class: grid, width: 340, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]E[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1.15[/TD]
[TD]1.15[/TD]
[TD]15[/TD]
[TD]1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1.14[/TD]
[TD]1.14[/TD]
[TD]14[/TD]
[TD]1[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1.25[/TD]
[TD]1.25[/TD]
[TD]25[/TD]
[TD]2[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1.26[/TD]
[TD]1.26[/TD]
[TD]26[/TD]
[TD]2[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1.95[/TD]
[TD]1.95[/TD]
[TD]95[/TD]
[TD]9[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Table 1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Column E: The numbers to round
Column H: formula”=ROUND(E1,2)”
Column I: extracts the two decimal numbers from the relevant cell in column H.

Formula Column I: “=RIGHT(H1,LEN(H1)-FIND(“.”,H1))”
Formula Column J: ”=LEFT(I1,1)”
Formula Column K: “=RIGHT(I1,1)”






The criteria to match and the final results to be placed in Column E
1.) If the number in Column J is odd and the number in Column K is =>5 add 1 to the number in Column J and change the number in Column K to 0. (H1:1.15 is rounded up to 1.20)
2.) If the number in Column J is odd and the number in Column K is <5 the number in Column J stays unchanged and the number in Column K is changed to 0. (H2:1.14 is rounded down to 1.10)
3.) If the number in Column J is even and the number in Column K is =<5 the number in Column J stays unchanged and the number in Column K is changed to 0. (H3:1.25 is rounded down to 1.20)
4.) If the number in Column J is even and the number in Column K is >5 add 1 to the number in Column J and change the number in Column K to 0. (H3:1.26 is rounded up to 1.30)
5.) If the number in Column J is “9” and the number in Column K is =>5 add 1 to the first whole number in Column H and change the numbers in Column J and Column K to 0. (H5:1.95 is rounded up to 2.00)
6.) If the number in Column J is “9” and the number in Column K is <5 the number in Column J stays unchanged and the number in Column K is changed to 0. (1.94 is rounded down to 1.90)

What I do have
I have code to find the first column with no data (Table 1, Cell H1 in the general layout)
Code (CurrentRegion.Rows.Count) to determine how many rows contain values in the dataset.
Code to go to the next cell in Column H. ("ActiveCell.Offset(1,0).Select")
The above code is used in a For/Next procedure.

What I would need:
Ideally the VBA code to do the criteria 1 to 6 checks would be housed within the For/Next loop. On matching one of the criteria the value in Column E is replaced with the “rounded” value. The process is repeated for all rows up to the rowcount.

· Preferably a VBA solution which would allow me to apply the automated procedure to active sheets with the same numbering.
· Alternatively an Excel/VBA solution to apply the automated procedure to active sheets with the same numbering

As with the intro, any help would be greatly appreciated.
Many Thanks

 

[Rick Rothstein]

The rounding you are describing is known as "Banker's Rounding" and it is the type of rounding that VB does naturally, so your VB code would simply be this...

Sub CustomRound()
  Dim X As Long, LastRow As Long
  LastRow = Cells(Rows.Count, "E").End(xlUp).Row
  For X = 1 To LastRow
    Cells(X, "H").Value = Round(Cells(X, "E").Value, 1)
  Next
End Sub

 

[hainer76]

Many thanks for the speedy reply. I really appreciate it.
All the best for 2014