Round Number to the nearest 0.5

JoyC

Board Regular
Joined
Aug 28, 2009
Messages
91
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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

MisterCrash

Active Member
Joined
Jan 27, 2003
Messages
279
Do you need to use a macro for this? A formula like this should work:

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

JoyC

Board Regular
Joined
Aug 28, 2009
Messages
91
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
Joined
Jan 27, 2003
Messages
279
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
Joined
Aug 28, 2009
Messages
91
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,394
Members
414,234
Latest member
grlevesq

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top