Excel Formula as a Access Expression

[tbowland]

Most of the formula functions I have used in this Excel doc. are not available as a Access expression.

So to achieve what I have here > Retail rounding up to specific numbers (as well as calculating the retail)
I have to start over in access
Can anyone help or maybe I have to go to a access forum.

Thanks in advance
Ted

=INT(([@[Cost / Unit]]/(1-[@[Set Desired MG%]])))+INDEX($A$11:$A$19,MATCH(ROUND(([@[Cost / Unit]]/(1-[@[Set Desired MG%]]))-INT(([@[Cost / Unit]]/(1-[@[Set Desired MG%]]))),15),$A$11:$A$19,-1)) ROUNDING NUMBERS Cost / Unit Set Desired MG% No Rounding Retail (rounded) $1.15 $1.96 38% $3.16 $3.25 $0.95 $1.94 38% $3.13 $3.15 $0.85 $1.90 38% $3.06 $3.15 $0.75 $1.86 38% $3.00 $3.15 $0.65 $1.84 38% $2.97 $3.15 $0.50 $1.76 38% $2.84 $2.85 $0.35 $1.74 38% $2.81 $2.85 $0.25 $1.58 38% $2.55 $2.65 $0.15 $1.48 38% $2.39 $2.50 $1.62 38% $2.61 $2.65 $1.64 38% $2.65 $2.65 $1.66 38% $2.68 $2.75 $1.22 38% $1.97 $2.15 $1.24 38% $2.00 $2.15 $1.26 38% $2.03 $2.15 $1.30 38% $2.10 $2.15 $1.05 38% $1.69 $1.75 $1.06 38% $1.71 $1.75 $1.07 38% $1.73 $1.75 $1.08 38% $1.74 $1.75 $1.09 38% $1.76 $1.85 $1.10 38% $1.77 $1.85 $1.11 38% $1.79 $1.85 $1.12 38% $1.81 $1.85 $1.13 38% $1.82 $1.85 <tbody> </tbody> <strike></strike>

<tbody>
</tbody>

 

[stumac]

As with Excel there is a few ways to do this in access...

I would probably build a function (example below) in a module and call that from the query:

Function RetailAmt(Amt As Double) As Double
Dim roundamount As Double
RetailAmt = Fix(Amt)
roundamount = Amt - RetailAmt
Select Case roundamount
    Case Is > 0.95
    RetailAmt = RetailAmt + 1.15
    Case Is > 0.85
    RetailAmt = RetailAmt + 0.95
    Case Is > 0.75
    RetailAmt = RetailAmt + 0.85
    Case Is > 0.65
    RetailAmt = RetailAmt + 0.75
    Case Is > 0.5
    RetailAmt = RetailAmt + 0.65
    Case Is > 0.35
    RetailAmt = RetailAmt + 0.5
    Case Is > 0.25
    RetailAmt = RetailAmt + 0.35
    Case Is > 0.15
    RetailAmt = RetailAmt + 0.25
    Case Is > 0
    RetailAmt = RetailAmt + 0.15
End Select
End Function