ROUNDUP and ROUNDDOWN FUNCTION errors

[32CARDS]

A UDF function seems to interfere sometimes with the rest of the workbooks entire process for some reason.

I could use the CEILNG function to work around the glitch, but it does not seem to allow the flexibility required because it takes everything to 1.
So for example if a Value is 9.24, CEILING will Sum to 10 when when it must be 9, unless I'm missing something.
But if the Value is 9.25 or 9.26 then rounded value must go to 10


The basic math required in the formula bar then is:

x = the value in Cell range.

if (x < 0.25) then x = 0
if (x >= 0.25) and (x < 0.75) then x = 1 ' or 5
if (x >= 0.75) then x = 1

x values appear in cell ranges:
J9,J10 and J11

The rounded Values of those Cell Ranges are in:
R9,R10 and R11

The question is,
How is the correct Syntax of that above ROUNDUP/ROUNDOWN written so it works in the Formula bar instead ?

The other question is then, why would a UDF ROUNDING Function mess with the entire process of the workbook's other macros, even if I place the UDF in a VBA module on it's own ?

Much prefer though to use the Formula bar instead though if possible.


Thanks in advance.

 

[Scott Huish]

if (x >= 0.25) and (x < 0.75) then x = 1 ' or 5

what is 1 ' or 5?

What is your UDF?

 

[32CARDS]

Thanks,
1 ' or 5 is the choice to make adjustments,
so I could change it if Value = 9.52 it will show 9.50 and not 10

The UDF shown to me to try is

Function ROUNDPAY(Pay)
Dim x
If Pay - Int(Pay) < 0.25 Then
x = 0
Else
x = 1
End If

ROUNDPAY = Int(Pay) + x
End Function

 

[Boller]

If you want to round-up if >0.24 else round-down :-

=(A7-INT(A7)>0.24)+ROUNDDOWN(A7,0)

If you want to round to nearest 0.50 :-

=MROUND(A7,0.5)

 

[Scott Huish]

In a formula:
=(A1-INT(A1)>=0.25)+INT(A1)

 

[32CARDS]

Boller, thanks for you reply,
I can see your solution, but there are approximately 150 seperate calculations done per hour and each process is not the same as the previous.
So the Values are not a constant 0.24 etc

 

[Boller]

Boller, thanks for you reply,
I can see your solution, but there are approximately 150 seperate calculations done per hour and each process is not the same as the previous.
So the Values are not a constant 0.24 etc

I don't understand what this means.

 

[32CARDS]

HOTPEPPER

Works perfectly

Solved.

 

[Boller]

HOTPEPPER

Works perfectly

Solved.

The formula I posted does the same thing !

 

[32CARDS]

Boller,
where you have >0.24

=(A7-INT(A7)>0.24)+ROUNDDOWN(A7,0)

I assumed you thought I may have meant the 0.24 is constant ?

But I might be wrong to in understanding your solution, my apologies.

I'll try and test both methods and see how it goes and post back.