RoundUp to the next multiple of .5

[Guest]

Unless it already produces a multiple of .5, I need the results of my
expression to round up to the nearest multiple of 1/2 or .5, therefore if I
was dividing 5/2 I would want the result of my roundup function to leave the
result at 2.5 However, if I was dividing 5 by 1.5 I would want the roundup
function to change the 3.33 to 3.5

Thanks for any and all help

 

[Steve Schapel]

Lele,

Round(YourNumber*2)/2

 

[Guest]

Hi,
Thanks for the help. Somehow it is just giving me back the same number I
started with. For Example

 

[Guest]

Hello Steve,
Thanks so much for the help. I can see were the math works great, but for
some reason when I try to use in my expression, it appears to just round.

WESround: Round([WES]*2)/2
WES: IIf([UOM]="Pair(s)",[NumbOfCuts]/[qty]/2,[NumbOfCuts]/[qty])

My WES - 1.1
My WESround - 1

Any suggestions?
Thanks

 

[CompGeek78]

If you truely want it to round up to the nearest .5, not just round to
the nearest .5, Steve's formula won't work. Steve's formula rounds to
the nearest .5. So, 1.1 rounds to 1 not 1.5. Do you always want it to
round up?

Hello Steve,
Thanks so much for the help. I can see were the math works great, but for
some reason when I try to use in my expression, it appears to just round.

WESround: Round([WES]*2)/2
WES: IIf([UOM]="Pair(s)",[NumbOfCuts]/[qty]/2,[NumbOfCuts]/[qty])

My WES - 1.1
My WESround - 1

Any suggestions?
Thanks
--
Lele

Lele,

Round(YourNumber*2)/2

 

[Guest]

Yes, I ALWAYS want to ROUND UP. We are manufacturing draperies, and we can
never do less, but only do equal or more the amount of fabric.
Thanks for any help you can offer.
--
Lele

If you truely want it to round up to the nearest .5, not just round to
the nearest .5, Steve's formula won't work. Steve's formula rounds to
the nearest .5. So, 1.1 rounds to 1 not 1.5. Do you always want it to
round up?

Hello Steve,
Thanks so much for the help. I can see were the math works great, but for
some reason when I try to use in my expression, it appears to just round.

WESround: Round([WES]*2)/2
WES: IIf([UOM]="Pair(s)",[NumbOfCuts]/[qty]/2,[NumbOfCuts]/[qty])

My WES - 1.1
My WESround - 1

Any suggestions?
Thanks
--
Lele

Lele,

Round(YourNumber*2)/2

--
Steve Schapel, Microsoft Access MVP

Lele wrote:
Unless it already produces a multiple of .5, I need the results of my
expression to round up to the nearest multiple of 1/2 or .5, therefore if I
was dividing 5/2 I would want the result of my roundup function to leave the
result at 2.5 However, if I was dividing 5 by 1.5 I would want the roundup
function to change the 3.33 to 3.5

Thanks for any and all help

 

[CompGeek78]

Unfortunately, the function you need to do this is a ceiling function.
The unfortunate part is that Access MDBs don't support the ceiling
function. I'm not sure of a way to implement this without a ceiling
function.

Yes, I ALWAYS want to ROUND UP. We are manufacturing draperies, and we can
never do less, but only do equal or more the amount of fabric.
Thanks for any help you can offer.
--
Lele

If you truely want it to round up to the nearest .5, not just round to
the nearest .5, Steve's formula won't work. Steve's formula rounds to
the nearest .5. So, 1.1 rounds to 1 not 1.5. Do you always want it to
round up?

Hello Steve,
Thanks so much for the help. I can see were the math works great, but for
some reason when I try to use in my expression, it appears to just round.

WESround: Round([WES]*2)/2
WES: IIf([UOM]="Pair(s)",[NumbOfCuts]/[qty]/2,[NumbOfCuts]/[qty])

My WES - 1.1
My WESround - 1

Any suggestions?
Thanks
--
Lele


:

Lele,

Round(YourNumber*2)/2

--
Steve Schapel, Microsoft Access MVP

Lele wrote:
Unless it already produces a multiple of .5, I need the results of my
expression to round up to the nearest multiple of 1/2 or .5, therefore if I
was dividing 5/2 I would want the result of my roundup function to leave the
result at 2.5 However, if I was dividing 5 by 1.5 I would want the roundup
function to change the 3.33 to 3.5

Thanks for any and all help

 

[CompGeek78]

Try this:

IIF([Data]-Round([Data],0)<=.5 and
[Data]-Round([Data],0)>=0,Round([Data],0)+.5,Round([Data],0))

Keven

Unfortunately, the function you need to do this is a ceiling function.
The unfortunate part is that Access MDBs don't support the ceiling
function. I'm not sure of a way to implement this without a ceiling
function.

Yes, I ALWAYS want to ROUND UP. We are manufacturing draperies, and we can
never do less, but only do equal or more the amount of fabric.
Thanks for any help you can offer.
--
Lele

If you truely want it to round up to the nearest .5, not just round to
the nearest .5, Steve's formula won't work. Steve's formula rounds to
the nearest .5. So, 1.1 rounds to 1 not 1.5. Do you always want it to
round up?

Lele wrote:
Hello Steve,
Thanks so much for the help. I can see were the math works great, but for
some reason when I try to use in my expression, it appears to just round.

WESround: Round([WES]*2)/2
WES: IIf([UOM]="Pair(s)",[NumbOfCuts]/[qty]/2,[NumbOfCuts]/[qty])

My WES - 1.1
My WESround - 1

Any suggestions?
Thanks
--
Lele


:

Lele,

Round(YourNumber*2)/2

--
Steve Schapel, Microsoft Access MVP

Lele wrote:
Unless it already produces a multiple of .5, I need the results of my
expression to round up to the nearest multiple of 1/2 or .5, therefore if I
was dividing 5/2 I would want the result of my roundup function to leave the
result at 2.5 However, if I was dividing 5 by 1.5 I would want the roundup
function to change the 3.33 to 3.5

Thanks for any and all help

 

[CompGeek78]

Try this:

IIF([Data]-Round([Data],0)<=.5 and
[Data]-Round([Data],0)>0,Round([Data],0)+.5,Round([Data],0))


Keven

Yes, I ALWAYS want to ROUND UP. We are manufacturing draperies, and we can
never do less, but only do equal or more the amount of fabric.
Thanks for any help you can offer.
--
Lele

If you truely want it to round up to the nearest .5, not just round to
the nearest .5, Steve's formula won't work. Steve's formula rounds to
the nearest .5. So, 1.1 rounds to 1 not 1.5. Do you always want it to
round up?

Hello Steve,
Thanks so much for the help. I can see were the math works great, but for
some reason when I try to use in my expression, it appears to just round.

WESround: Round([WES]*2)/2
WES: IIf([UOM]="Pair(s)",[NumbOfCuts]/[qty]/2,[NumbOfCuts]/[qty])

My WES - 1.1
My WESround - 1

Any suggestions?
Thanks
--
Lele


:

Lele,

Round(YourNumber*2)/2

--
Steve Schapel, Microsoft Access MVP

Lele wrote:
Unless it already produces a multiple of .5, I need the results of my
expression to round up to the nearest multiple of 1/2 or .5, therefore if I
was dividing 5/2 I would want the result of my roundup function to leave the
result at 2.5 However, if I was dividing 5 by 1.5 I would want the roundup
function to change the 3.33 to 3.5

Thanks for any and all help

 

[CompGeek78]

The big problem with this is the way Access rounds .5.

Round(2.5)=2
however
Round(3.5)=4

So, the function I've listed above gives you:
31.5 ---> 32 but 30.5 ---> 30.5

This is why Access really needs that ceiling function that Excel uses.

Will continue to work on something for you.

Keven

Try this:

IIF([Data]-Round([Data],0)<=.5 and
[Data]-Round([Data],0)>0,Round([Data],0)+.5,Round([Data],0))


Keven

Yes, I ALWAYS want to ROUND UP. We are manufacturing draperies, and we can
never do less, but only do equal or more the amount of fabric.
Thanks for any help you can offer.
--
Lele

If you truely want it to round up to the nearest .5, not just round to
the nearest .5, Steve's formula won't work. Steve's formula rounds to
the nearest .5. So, 1.1 rounds to 1 not 1.5. Do you always want it to
round up?

Lele wrote:
Hello Steve,
Thanks so much for the help. I can see were the math works great, but for
some reason when I try to use in my expression, it appears to just round.

WESround: Round([WES]*2)/2
WES: IIf([UOM]="Pair(s)",[NumbOfCuts]/[qty]/2,[NumbOfCuts]/[qty])

My WES - 1.1
My WESround - 1

Any suggestions?
Thanks
--
Lele


:

Lele,

Round(YourNumber*2)/2

--
Steve Schapel, Microsoft Access MVP

Lele wrote:
Unless it already produces a multiple of .5, I need the results of my
expression to round up to the nearest multiple of 1/2 or .5, therefore if I
was dividing 5/2 I would want the result of my roundup function to leave the
result at 2.5 However, if I was dividing 5 by 1.5 I would want the roundup
function to change the 3.33 to 3.5

Thanks for any and all help

 

[Steve Schapel]

Lele,

Apologies, I misunderstood the question.

 

[CompGeek78]

This seems to work on my Data:

IIf([Data]-Round([Data],0)<=0.5 And [Data]-Round([Data],0)>0,IIf([Data]
mod 2 = 0,
round([Data],0)+.5,round([Data],0)),IIf([Data]-Round([Data],0)>=-0.5
And [Data]-Round([Data],0)<0,round([Data],0)-.5,round([Data],0))

Just replace Data with your fieldname.

Keven

 

[Steve Schapel]

Possibly simpler?...

Round(([WES]+0.249)*2)/2

 

[CompGeek78]

I hate you

I spent like half an hour working on that damnable thing.

Keven

Possibly simpler?...

Round(([WES]+0.249)*2)/2

--
Steve Schapel, Microsoft Access MVP

Try this:

IIF([Data]-Round([Data],0)<=.5 and
[Data]-Round([Data],0)>0,Round([Data],0)+.5,Round([Data],0))

 

[CompGeek78]

That works as long as the numbers are not very precise. If the
measurement is something like 2.0001 it would round incorrectly, but it
sounds like in this circumstance, measuring draperies, that shouldn't
be an issue.

Keven

Possibly simpler?...

Round(([WES]+0.249)*2)/2

--
Steve Schapel, Microsoft Access MVP

Try this:

IIF([Data]-Round([Data],0)<=.5 and
[Data]-Round([Data],0)>0,Round([Data],0)+.5,Round([Data],0))

 

[CompGeek78]

Just as an exercise, if you needed it to be exact in all circumstances
no matter what the precision is, the following formula should do it.

IIf([data] Mod 2=0,
IIf([data]-Round([data])>0,
Round([data])+0.5,
IIf([data]-Round([data])=-0.5,
Round([data])-0.5,
Round([data])
)
),
IIf([data]-Round([data])>0,
Round([data])+0.5,
Round([data])
)
)

Keven

That works as long as the numbers are not very precise. If the
measurement is something like 2.0001 it would round incorrectly, but it
sounds like in this circumstance, measuring draperies, that shouldn't
be an issue.

Keven

Possibly simpler?...

Round(([WES]+0.249)*2)/2

--
Steve Schapel, Microsoft Access MVP

Try this:

IIF([Data]-Round([Data],0)<=.5 and
[Data]-Round([Data],0)>0,Round([Data],0)+.5,Round([Data],0))

 

[Steve Schapel]

Keven,

True. Mind you, if you need that level of precision, then you just go
Round(([WES]+0.24999)*2)/2


--
Steve Schapel, Microsoft Access MVP

That works as long as the numbers are not very precise. If the
measurement is something like 2.0001 it would round incorrectly, but it
sounds like in this circumstance, measuring draperies, that shouldn't
be an issue.

Keven

Possibly simpler?...

Round(([WES]+0.249)*2)/2

--
Steve Schapel, Microsoft Access MVP

Try this:

IIF([Data]-Round([Data],0)<=.5 and
[Data]-Round([Data],0)>0,Round([Data],0)+.5,Round([Data],0))

 

[HanSolo]

You could also equijoin a translation table to roundup the results of
your expression.

e.g. create a translation table that looks like:

Low High Result
..5 1 1
1 1.5 1.5
1.5 2 2
2 2.5 2.5
etc... etc... etc...

Here's how to equijoin:

http://library.tebyan.net/books/10142/23.htm

 

[Guest]

A universal rounding function. Arguments are the Number and the number you
want to RoundTo.

Function uRound(ByVal Number, ByVal RoundTo) As Double

' Universal Round

On Error GoTo uRound_Error

Number = CDbl(Number)
RoundTo = CDbl(RoundTo)
uRound = Round(Number / RoundTo) * RoundTo

Exit Function

uRound_Error:

MsgBox Err.Description

End Function

Some examples:

uRound (3.2, .5) = 3
uRound (3.2, 5) = 5
uRound (2.3, .5) = 2.5
uRound (2.3, 5) = 0
uRound (now(),1) = the closest date
uRound (now(),cdate("0:30")) = the closest half hour

--

Btw, you can make a Ceiling function in Access:

Abs(-Int(Number)) * Sgn(Number)

Keven,

True. Mind you, if you need that level of precision, then you just go
Round(([WES]+0.24999)*2)/2


--
Steve Schapel, Microsoft Access MVP

That works as long as the numbers are not very precise. If the
measurement is something like 2.0001 it would round incorrectly, but it
sounds like in this circumstance, measuring draperies, that shouldn't
be an issue.

Keven

Possibly simpler?...

Round(([WES]+0.249)*2)/2

--
Steve Schapel, Microsoft Access MVP

CompGeek78 wrote:
Try this:

IIF([Data]-Round([Data],0)<=.5 and
[Data]-Round([Data],0)>0,Round([Data],0)+.5,Round([Data],0))

 

[Guest]

Universal Round Up...

Function uRoundUp(ByVal Number, ByVal RoundTo) As Double

' Universal RoundUp

On Error GoTo uRoundUp_Error

Number = CDbl(Number)
RoundTo = CDbl(RoundTo)

uRoundUp = Abs(Int(-(Number / RoundTo))) * Sgn(Number) * RoundTo

Exit Function

uRoundUp_Error:

MsgBox Err.Description

End Function

A universal rounding function. Arguments are the Number and the number you
want to RoundTo.

Function uRound(ByVal Number, ByVal RoundTo) As Double

' Universal Round

On Error GoTo uRound_Error

Number = CDbl(Number)
RoundTo = CDbl(RoundTo)
uRound = Round(Number / RoundTo) * RoundTo

Exit Function

uRound_Error:

MsgBox Err.Description

End Function

Some examples:

uRound (3.2, .5) = 3
uRound (3.2, 5) = 5
uRound (2.3, .5) = 2.5
uRound (2.3, 5) = 0
uRound (now(),1) = the closest date
uRound (now(),cdate("0:30")) = the closest half hour

--

Btw, you can make a Ceiling function in Access:

Abs(-Int(Number)) * Sgn(Number)

Keven,

True. Mind you, if you need that level of precision, then you just go
Round(([WES]+0.24999)*2)/2


--
Steve Schapel, Microsoft Access MVP

That works as long as the numbers are not very precise. If the
measurement is something like 2.0001 it would round incorrectly, but it
sounds like in this circumstance, measuring draperies, that shouldn't
be an issue.

Keven
Steve Schapel wrote:
Possibly simpler?...

Round(([WES]+0.249)*2)/2

--
Steve Schapel, Microsoft Access MVP

CompGeek78 wrote:
Try this:

IIF([Data]-Round([Data],0)<=.5 and
[Data]-Round([Data],0)>0,Round([Data],0)+.5,Round([Data],0))