×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Re-write Excel formula for Access use

## Re-write Excel formula for Access use

(OP)
Most of these excel functions are not available in access and I don't know how to accomplish the same rounding function in access
What is happening here in excel is, aside from calculating Retail from Cost and margin 'COST/(1-MARGIN)= RETAIL' I'm rounding the result up to one of these finishing numbers (ROUNDING NUMBERS)
Note the difference between No rounding and Retail.

Can anyone give me some ideas. I'd like to do this just as an expression on a form. I'm not storing this result.

Ted

### RE: Re-write Excel formula for Access use

Welcome to Tek-Tips! There are probably many options for solving this. I would create your own function in Access to do the rounding up. Something like the following should work:

#### CODE --> vba

Public Function GetRetail(curCost As Currency, dblPctMg As Double)
Dim curResult As Currency
Dim curDollars As Currency               'for the whole dollar part
Dim dblCents As Double                   'for the cents part
Dim arrUpTo As Variant                   'array to store the values to round up to
Dim intA As Integer                      'to increment when looping through array
'these values could be read from a table if desired
arrUpTo = Array(0.15, 0.25, 0.35, 0.5, 0.65, 0.75, 0.85, 0.95)
curResult = curCost / (1 - dblPctMg)     'get value with margin
curDollars = Int(curResult)
dblCents = curResult - curDollars
For intA = 0 To UBound(arrUpTo)          'loop through the array and exit when condition met
If dblCents <= arrUpTo(intA) Then
curResult = curDollars + arrUpTo(intA)
Exit For
End If
Next
GetRetail = curResult
End Function 

You can use the function in a query or control source or VBA.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

### RE: Re-write Excel formula for Access use

Duane,
Since curResult is declared as Currency, and at the end GetRetail = curResult, shouldn't GetRetail be declared (and returned the value) as Currency (and not a Variant as it is now), too?

Just curious...

Have fun.

---- Andy

There is a great need for a sarcasm font.

### RE: Re-write Excel formula for Access use

Andy,
Good catch. The function should definitely be:

#### CODE --> vba

Public Function GetRetail(curCost As Currency, dblPctMg As Double) as Currency

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

### RE: Re-write Excel formula for Access use

#### CODE -->

Most of these excel functions are not available in access
Wrong. I believe all of them are. You likely have to pass an array and not a range.

https://www.access-programmers.co.uk/forums/showth...

But I would still roll my own in vba.

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!