variable ROUND formula or VBA macro

[32CARDS]

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

 

[Andrew Poulsom]

I think you had better post some sample data and the expected results.

 

[32CARDS]

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

 

[miss_ell]

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.

 

[dms37]

Hi,

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

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

 

[CodeNinja]

=round(a1/0.5,0)*0.5

 

[32CARDS]

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

 

[CodeNinja]

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

 

[32CARDS]

double post

 

[32CARDS]

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