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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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
Back
Top