# variable ROUND formula or VBA macro

#### 32CARDS

##### Board Regular
A micrometer may measure sizes and need to be lathed into square rounded settings.

Either 1 or .50, but sometimes it needs to be specific and adjustments vary.

How is the formula constructed or a VBA coded Function.

Here is the 3 "If" "and" "then" scenarios.
x = a cell range

if (x < 0.25) then x = 0 end if

if (x >= 0.25) and (x < 0.75) then x = 50 end if

if (x >= 0.75) then x = 1 end if

Sometimes I need to adjust to a "50" to a "1" in the decimal
So if a measurement is 57.73 it may become 58 if the setting is 1
But if the setting is 50, then it becomes 57.50

The range is 3 cells.
Actual
C4:C6 ( If)

ROUNDED
E4:E6 (Then)

Thanks

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
I think you had better post some sample data and the expected results.

If I could upload a workbook I would.
Not sure how to work this html thing, too vague.

But here goes

ACTUAL .|. ROUNDED
If . x | then x
1 75.38 | 75.50
2 66.77 | 67.00
3 48.91 | 49.00

''if (x < 0.25) then x = 0 end if-----'''if (x >= 0.25) and (x < 0.75) then x = 50 end if-----'''if (x >= 0.75) then x = 1 end if

Last edited:
Hello,

I've used the INT formula if that will help.

A = input
B = x
C = setting
D = output

A2: (enter required figure)
B2: =A2-INT(A2)
C2: =IF(B2<0.25,0,IF(B2<0.75,0.5,1))
D2: =INT(A2)+C2

Hopefully it's a start.

Hi,

I've not tested this but the maths works in my head.

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

=round(a1/0.5,0)*0.5

miss_ell
That works, I can see where to make adjustments if need be. That's the basics of it.

dms37
Yours worked to, I changed the .05 to a 1, and got same answers with both formulas

CodeNinja
That works to.

Thanks

Last edited:
I'm not sure =(ROUND(2*(A1+0.5),0))/2 works... I tried 73.88 on it and got 74.5...

double post

Last edited:
The actual code from some other "user friendly" program goes into the editor of the source code.
It looks something like this...( we don't use it anymore ) I think it's string related.

function Round ()
'*******************************************************************
' this function designed to round to the nearest .50
'*******************************************************************
dim x
x = Frac
if (x < 0.25) then x = 0 end if
if (x >= 0.25) and (x < 0.75) then x = 1 end if
if (x >= 0.75) then x = 1 end if
Round = Int + x
end function

But the formula works

Replies
0
Views
638
Replies
0
Views
197
Replies
3
Views
480
Replies
6
Views
200
Replies
0
Views
241

### Forum statistics

1,196,457
Messages
6,015,366
Members
441,889
Latest member
balolaptopgiaolong ### 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