Formula

  • Thread starter Thread starter Simon
  • Start date Start date
S

Simon

I need a formula to use in a query to round UP a currency
value to the nearest .05

Ie. £ 1.01 to £ 1.05
£ 1.04 to £ 1.05
£ 1.06 to £ 1.10

Silly I know but can anyone help

Thanks in advance

Simon
 
I need a formula to use in a query to round UP a currency
value to the nearest .05

Ie. £ 1.01 to £ 1.05
£ 1.04 to £ 1.05
£ 1.06 to £ 1.10

Hi Simon,

There may be simpler method(s)
out there, but here be one way.

I constructed a table ("tblCur")
with one field ("Curr") ranging from
1.00 to 1.99 incrementing by .01.

Expr6 in the following query performs
the rounding as you describe. I included
other "Exprx"'s to show what we get as
we build the expression.


SELECT tblCur.Curr,
CCur((CLng([Curr]*100)+IIf(((CLng([Curr]*100)) Mod 10)
Mod 5=0,0,5-((CLng([Curr]*100)) Mod 10) Mod 5))/100) AS Expr6,
CLng([Curr]*100) AS x1,
(CLng([Curr]*100)) Mod 10 AS x2,
((CLng([Curr]*100)) Mod 10) Mod 5 AS x3,
IIf(((CLng([Curr]*100)) Mod 10)
Mod 5=0,0,5-((CLng([Curr]*100)) Mod 10) Mod 5) AS x4,
CLng([Curr]*100)+IIf(((CLng([Curr]*100)) Mod 10)
Mod 5=0,0,5-((CLng([Curr]*100)) Mod 10) Mod 5) AS x5
FROM tblCur;

Curr Expr6 x1 x2 x3 x4 x5
$1.00 $1.00 100 0 0 0 100
$1.01 $1.05 101 1 1 4 105
$1.02 $1.05 102 2 2 3 105
$1.03 $1.05 103 3 3 2 105
$1.04 $1.05 104 4 4 1 105
$1.05 $1.05 105 5 0 0 105
$1.06 $1.10 106 6 1 4 110
<snip>
$1.89 $1.90 189 9 4 1 190
$1.90 $1.90 190 0 0 0 190
$1.91 $1.95 191 1 1 4 195
$1.92 $1.95 192 2 2 3 195
$1.93 $1.95 193 3 3 2 195
$1.94 $1.95 194 4 4 1 195
$1.95 $1.95 195 5 0 0 195
$1.96 $2.00 196 6 1 4 200
$1.97 $2.00 197 7 2 3 200
$1.98 $2.00 198 8 3 2 200
$1.99 $2.00 199 9 4 1 200

From the "Exprx"'s, you may see that
this could easily be "transferred" to a
user-defined function using Case stmt.

If you would like to go that route, please
respond back.

Good luck,

Gary Walter
 
Hi Simon,

Here be the function just in case.
(Again...there may be more clever
methods out there)


Public Function fRoundCur(Curr As Currency) As Currency
Dim intMod As Integer
intMod = 5 - (((CLng(Curr * 100)) Mod 10) Mod 5)
'intMod will be 1,2,3,4, or 5, and except for "5,"
'represents what we want to add to (Curr*100) to round up
Select Case intMod
Case 5
fRoundCur = Curr
Case 1 To 4
fRoundCur = CCur((CLng(Curr * 100) + intMod) / 100)
Case Else
fRoundCur = 9999999.99
End Select
End Function
 
Actually....if type of your field is Currency,
then function could be simplified:

Public Function fRoundCur(Curr As Currency) As Currency
Dim intMod As Integer
intMod = 5 - (((Curr * 100) Mod 10) Mod 5)
'intMod will be 1,2,3,4, or 5
Select Case intMod
Case 5
fRoundCur = Curr
Case 1 To 4
fRoundCur = ((Curr * 100) + intMod) / 100
Case Else
fRoundCur = 9999999.99
End Select
End Function

Gary Walter said:
Hi Simon,

Here be the function just in case.
(Again...there may be more clever
methods out there)


Public Function fRoundCur(Curr As Currency) As Currency
Dim intMod As Integer
intMod = 5 - (((CLng(Curr * 100)) Mod 10) Mod 5)
'intMod will be 1,2,3,4, or 5, and except for "5,"
'represents what we want to add to (Curr*100) to round up
Select Case intMod
Case 5
fRoundCur = Curr
Case 1 To 4
fRoundCur = CCur((CLng(Curr * 100) + intMod) / 100)
Case Else
fRoundCur = 9999999.99
End Select
End Function

Gary Walter said:
I need a formula to use in a query to round UP a currency
value to the nearest .05

Ie. £ 1.01 to £ 1.05
£ 1.04 to £ 1.05
£ 1.06 to £ 1.10

Hi Simon,

There may be simpler method(s)
out there, but here be one way.

I constructed a table ("tblCur")
with one field ("Curr") ranging from
1.00 to 1.99 incrementing by .01.

Expr6 in the following query performs
the rounding as you describe. I included
other "Exprx"'s to show what we get as
we build the expression.


SELECT tblCur.Curr,
CCur((CLng([Curr]*100)+IIf(((CLng([Curr]*100)) Mod 10)
Mod 5=0,0,5-((CLng([Curr]*100)) Mod 10) Mod 5))/100) AS Expr6,
CLng([Curr]*100) AS x1,
(CLng([Curr]*100)) Mod 10 AS x2,
((CLng([Curr]*100)) Mod 10) Mod 5 AS x3,
IIf(((CLng([Curr]*100)) Mod 10)
Mod 5=0,0,5-((CLng([Curr]*100)) Mod 10) Mod 5) AS x4,
CLng([Curr]*100)+IIf(((CLng([Curr]*100)) Mod 10)
Mod 5=0,0,5-((CLng([Curr]*100)) Mod 10) Mod 5) AS x5
FROM tblCur;

Curr Expr6 x1 x2 x3 x4 x5
$1.00 $1.00 100 0 0 0 100
$1.01 $1.05 101 1 1 4 105
$1.02 $1.05 102 2 2 3 105
$1.03 $1.05 103 3 3 2 105
$1.04 $1.05 104 4 4 1 105
$1.05 $1.05 105 5 0 0 105
$1.06 $1.10 106 6 1 4 110
<snip>
$1.89 $1.90 189 9 4 1 190
$1.90 $1.90 190 0 0 0 190
$1.91 $1.95 191 1 1 4 195
$1.92 $1.95 192 2 2 3 195
$1.93 $1.95 193 3 3 2 195
$1.94 $1.95 194 4 4 1 195
$1.95 $1.95 195 5 0 0 195
$1.96 $2.00 196 6 1 4 200
$1.97 $2.00 197 7 2 3 200
$1.98 $2.00 198 8 3 2 200
$1.99 $2.00 199 9 4 1 200

From the "Exprx"'s, you may see that
this could easily be "transferred" to a
user-defined function using Case stmt.

If you would like to go that route, please
respond back.

Good luck,

Gary Walter
Click to expand...
Click to expand...
 
Hi,

Easy way: multiply by 20, then ceil() the result and divide that value by 20

I don't know for sure whether ceil is called "ceil()" in Access, but it is a
function to round to the first integer that is bigger or equal.

greets, Jeroen
 
You might try the following formula as long as your values are postive.

-Int(-SomeValue * 20)/20

Jeroen said:
Hi,

Easy way: multiply by 20, then ceil() the result and divide that value by 20

I don't know for sure whether ceil is called "ceil()" in Access, but it is a
function to round to the first integer that is bigger or equal.

greets, Jeroen

Gary Walter said:
I need a formula to use in a query to round UP a currency
value to the nearest .05

Ie. £ 1.01 to £ 1.05
£ 1.04 to £ 1.05
£ 1.06 to £ 1.10

Hi Simon,

There may be simpler method(s)
out there, but here be one way.

I constructed a table ("tblCur")
with one field ("Curr") ranging from
1.00 to 1.99 incrementing by .01.

Expr6 in the following query performs
the rounding as you describe. I included
other "Exprx"'s to show what we get as
we build the expression.


SELECT tblCur.Curr,
CCur((CLng([Curr]*100)+IIf(((CLng([Curr]*100)) Mod 10)
Mod 5=0,0,5-((CLng([Curr]*100)) Mod 10) Mod 5))/100) AS Expr6,
CLng([Curr]*100) AS x1,
(CLng([Curr]*100)) Mod 10 AS x2,
((CLng([Curr]*100)) Mod 10) Mod 5 AS x3,
IIf(((CLng([Curr]*100)) Mod 10)
Mod 5=0,0,5-((CLng([Curr]*100)) Mod 10) Mod 5) AS x4,
CLng([Curr]*100)+IIf(((CLng([Curr]*100)) Mod 10)
Mod 5=0,0,5-((CLng([Curr]*100)) Mod 10) Mod 5) AS x5
FROM tblCur;

Curr Expr6 x1 x2 x3 x4 x5
$1.00 $1.00 100 0 0 0 100
$1.01 $1.05 101 1 1 4 105
$1.02 $1.05 102 2 2 3 105
$1.03 $1.05 103 3 3 2 105
$1.04 $1.05 104 4 4 1 105
$1.05 $1.05 105 5 0 0 105
$1.06 $1.10 106 6 1 4 110
<snip>
$1.89 $1.90 189 9 4 1 190
$1.90 $1.90 190 0 0 0 190
$1.91 $1.95 191 1 1 4 195
$1.92 $1.95 192 2 2 3 195
$1.93 $1.95 193 3 3 2 195
$1.94 $1.95 194 4 4 1 195
$1.95 $1.95 195 5 0 0 195
$1.96 $2.00 196 6 1 4 200
$1.97 $2.00 197 7 2 3 200
$1.98 $2.00 198 8 3 2 200
$1.99 $2.00 199 9 4 1 200

From the "Exprx"'s, you may see that
this could easily be "transferred" to a
user-defined function using Case stmt.

If you would like to go that route, please
respond back.

Good luck,

Gary Walter
Click to expand...
Click to expand...
 
Thanks Jeroen and John
for sharing "the more clever way!"

To get a handle on this and
because I have worked for
a company that required
"rounding up to nearest .25,"
I tested the following.

-to round *only positive numbers*
up to nearest x:

round up to nearest .05
-Int(-[Curr] * 20)/20

round up to nearest .25
-Int(-[Curr] * 4)/4

round up to nearest .50
-Int(-[Curr] * 2)/2


-to round positive numbers up
and negative numbers down
to a nearest x:

-Sgn([Curr])*(Int(-Abs([Curr])*20)/20)
-Sgn([Curr])*(Int(-Abs([Curr])*4)/4)
-Sgn([Curr])*(Int(-Abs([Curr])*2)/2)

-to round positive numbers up
and negative numbers up
to a nearest x:

???????

I can look it up...but do you know offhand
how large [Curr] can be before this method fails?

Thanks again,

Gary Walter

John Spencer (MVP) said:
You might try the following formula as long as your values are postive.

-Int(-SomeValue * 20)/20

Jeroen said:
Hi,

Easy way: multiply by 20, then ceil() the result and divide that value by 20

I don't know for sure whether ceil is called "ceil()" in Access, but it is a
function to round to the first integer that is bigger or equal.

greets, Jeroen

Gary Walter said:
I need a formula to use in a query to round UP a currency
value to the nearest .05

Ie. £ 1.01 to £ 1.05
£ 1.04 to £ 1.05
£ 1.06 to £ 1.10

Hi Simon,

There may be simpler method(s)
out there, but here be one way.

I constructed a table ("tblCur")
with one field ("Curr") ranging from
1.00 to 1.99 incrementing by .01.

Expr6 in the following query performs
the rounding as you describe. I included
other "Exprx"'s to show what we get as
we build the expression.


SELECT tblCur.Curr,
CCur((CLng([Curr]*100)+IIf(((CLng([Curr]*100)) Mod 10)
Mod 5=0,0,5-((CLng([Curr]*100)) Mod 10) Mod 5))/100) AS Expr6,
CLng([Curr]*100) AS x1,
(CLng([Curr]*100)) Mod 10 AS x2,
((CLng([Curr]*100)) Mod 10) Mod 5 AS x3,
IIf(((CLng([Curr]*100)) Mod 10)
Mod 5=0,0,5-((CLng([Curr]*100)) Mod 10) Mod 5) AS x4,
CLng([Curr]*100)+IIf(((CLng([Curr]*100)) Mod 10)
Mod 5=0,0,5-((CLng([Curr]*100)) Mod 10) Mod 5) AS x5
FROM tblCur;

Curr Expr6 x1 x2 x3 x4 x5
$1.00 $1.00 100 0 0 0 100
$1.01 $1.05 101 1 1 4 105
$1.02 $1.05 102 2 2 3 105
$1.03 $1.05 103 3 3 2 105
$1.04 $1.05 104 4 4 1 105
$1.05 $1.05 105 5 0 0 105
$1.06 $1.10 106 6 1 4 110
<snip>
$1.89 $1.90 189 9 4 1 190
$1.90 $1.90 190 0 0 0 190
$1.91 $1.95 191 1 1 4 195
$1.92 $1.95 192 2 2 3 195
$1.93 $1.95 193 3 3 2 195
$1.94 $1.95 194 4 4 1 195
$1.95 $1.95 195 5 0 0 195
$1.96 $2.00 196 6 1 4 200
$1.97 $2.00 197 7 2 3 200
$1.98 $2.00 198 8 3 2 200
$1.99 $2.00 199 9 4 1 200

From the "Exprx"'s, you may see that
this could easily be "transferred" to a
user-defined function using Case stmt.

If you would like to go that route, please
respond back.

Good luck,

Gary Walter
Click to expand...
Click to expand...
Click to expand...
 
Back
Top