# Round Number to the nearest 0.5

#### JoyC

##### Board Regular
Bearing in mind that my VBA knowledge is very basic, could I please ask for some help with some number structure.

I am trying to find the vba code for rounding a number to the nearest 0.5. I have found the following:

ActiveCell = WorksheetFunction.MRound(ActiveCell, 0.5)

But what comes before and after it in the vba window? i.e Option Explicit / Private Sub Workbook..............etc. Additionally, I assume I need to define the range that this format should apply to:

Range("C27:G37").Select

Many thanks

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### MisterCrash

##### Active Member
Do you need to use a macro for this? A formula like this should work:

=0.5*(ROUND(A1*2, 0))

#### JoyC

##### Board Regular
I would prefer to avoid using the formula in the cell as the range in question already has formulae in it that's a bit like "War & Peace":

=IF('Chart 3'!\$E\$5="Overall",SUMPRODUCT((Data!\$I\$2:\$I\$65536='Chart 3'!\$B27)*(Data!\$Q\$2:\$Q\$65536='Chart 3'!M\$26)*Data!\$L\$2:\$L\$65536)/7.5,SUMPRODUCT((Data!\$N\$2:\$N\$65536='Chart 3'!\$E\$5)*(Data!\$I\$2:\$I\$65536='Chart 3'!\$B27)*(Data!\$Q\$2:\$Q\$65536='Chart 3'!M\$26)*Data!\$L\$2:\$L\$65536)/7.5)

I've tried using MRound, but am struggling to get in to work with Sumproduct

#### MisterCrash

##### Active Member
With a formula like that in your cell, you may be better off leaving those formulas as they are, and doing the rounding in a separate area of the worksheet. If you did decide to incorporate the rounding logic within the same formula, you would just replace the "A1" in my formula with that giant formula.

If you do decide to stick with VBA, my question would be whether you want the cell to contain just the rounded value at the end, or if you want to maintain some semblance of the formula in the cell. In other words, if your cell contains the formula =6*3.1, would you want the end result in the cell to be simply the number 18.5, or would you want the result to be =0.5*(ROUND(6*3.1*2, 0)), which would display as 18.5?

#### JoyC

##### Board Regular
Thanks for your advice. After a lot of struggling I finally managed to get it working:

=IF('Chart 3'!\$E\$5="Overall",ROUND((SUMPRODUCT((Data!\$I\$2:\$I\$65536='Chart 3'!\$B27)*(Data!\$Q\$2:\$Q\$65536='Chart 3'!C\$26)*Data!\$L\$2:\$L\$65536)/7.5)*2,0)/2,ROUND((SUMPRODUCT((Data!\$N\$2:\$N\$65536='Chart 3'!\$E\$5)*(Data!\$I\$2:\$I\$65536='Chart 3'!\$B27)*(Data!\$Q\$2:\$Q\$65536='Chart 3'!C\$26)*Data!\$L\$2:\$L\$65536)/7.5)*2,0)/2)

Not sure how this will impact on the speed of the calcs, but at least it's currently consolidated.

Thanks again.

Replies
3
Views
323
Replies
11
Views
982
Replies
8
Views
133
Replies
2
Views
111
Replies
3
Views
130

1,171,790
Messages
5,877,518
Members
433,264
Latest member
Donnybrook

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back