Excel Formula as a Access Expression

T

tbowland

New Member
Joined
Jul 13, 2017
Messages
3
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
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 861"]
<tbody>[TR]
[TD="width: 861, bgcolor: transparent, colspan: 8"]=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))[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 91, bgcolor: transparent"]ROUNDING NUMBERS[/TD]
[TD="width: 22, bgcolor: transparent"][/TD]
[TD="width: 76, bgcolor: transparent"]Cost / Unit[/TD]
[TD="width: 97, bgcolor: transparent"]Set Desired MG%[/TD]
[TD="width: 93, bgcolor: transparent"]No Rounding[/TD]
[TD="width: 80, bgcolor: transparent"]Retail (rounded)[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]$1.15[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="align: right"]$1.96[/TD]
[TD="align: right"]38%[/TD]
[TD="align: right"]$3.16[/TD]
[TD="align: right"]$3.25[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="width: 380, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]$0.95[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]$1.94[/TD]
[TD="bgcolor: transparent, align: right"]38%[/TD]
[TD="bgcolor: transparent, align: right"]$3.13[/TD]
[TD="bgcolor: transparent, align: right"]$3.15[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]$0.85[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="align: right"]$1.90[/TD]
[TD="align: right"]38%[/TD]
[TD="align: right"]$3.06[/TD]
[TD="align: right"]$3.15[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]$0.75[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]$1.86[/TD]
[TD="bgcolor: transparent, align: right"]38%[/TD]
[TD="bgcolor: transparent, align: right"]$3.00[/TD]
[TD="bgcolor: transparent, align: right"]$3.15[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]$0.65[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="align: right"]$1.84[/TD]
[TD="align: right"]38%[/TD]
[TD="align: right"]$2.97[/TD]
[TD="align: right"]$3.15[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]$0.50[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]$1.76[/TD]
[TD="bgcolor: transparent, align: right"]38%[/TD]
[TD="bgcolor: transparent, align: right"]$2.84[/TD]
[TD="bgcolor: transparent, align: right"]$2.85[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]$0.35[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="align: right"]$1.74[/TD]
[TD="align: right"]38%[/TD]
[TD="align: right"]$2.81[/TD]
[TD="align: right"]$2.85[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]$0.25[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]$1.58[/TD]
[TD="bgcolor: transparent, align: right"]38%[/TD]
[TD="bgcolor: transparent, align: right"]$2.55[/TD]
[TD="bgcolor: transparent, align: right"]$2.65[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]$0.15[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="align: right"]$1.48[/TD]
[TD="align: right"]38%[/TD]
[TD="align: right"]$2.39[/TD]
[TD="align: right"]$2.50[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]$1.62[/TD]
[TD="bgcolor: transparent, align: right"]38%[/TD]
[TD="bgcolor: transparent, align: right"]$2.61[/TD]
[TD="bgcolor: transparent, align: right"]$2.65[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="align: right"]$1.64[/TD]
[TD="align: right"]38%[/TD]
[TD="align: right"]$2.65[/TD]
[TD="align: right"]$2.65[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]$1.66[/TD]
[TD="bgcolor: transparent, align: right"]38%[/TD]
[TD="bgcolor: transparent, align: right"]$2.68[/TD]
[TD="bgcolor: transparent, align: right"]$2.75[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="align: right"]$1.22[/TD]
[TD="align: right"]38%[/TD]
[TD="align: right"]$1.97[/TD]
[TD="align: right"]$2.15[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]$1.24[/TD]
[TD="bgcolor: transparent, align: right"]38%[/TD]
[TD="bgcolor: transparent, align: right"]$2.00[/TD]
[TD="bgcolor: transparent, align: right"]$2.15[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="align: right"]$1.26[/TD]
[TD="align: right"]38%[/TD]
[TD="align: right"]$2.03[/TD]
[TD="align: right"]$2.15[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]$1.30[/TD]
[TD="bgcolor: transparent, align: right"]38%[/TD]
[TD="bgcolor: transparent, align: right"]$2.10[/TD]
[TD="bgcolor: transparent, align: right"]$2.15[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="align: right"]$1.05[/TD]
[TD="align: right"]38%[/TD]
[TD="align: right"]$1.69[/TD]
[TD="align: right"]$1.75[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]$1.06[/TD]
[TD="bgcolor: transparent, align: right"]38%[/TD]
[TD="bgcolor: transparent, align: right"]$1.71[/TD]
[TD="bgcolor: transparent, align: right"]$1.75[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="align: right"]$1.07[/TD]
[TD="align: right"]38%[/TD]
[TD="align: right"]$1.73[/TD]
[TD="align: right"]$1.75[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]$1.08[/TD]
[TD="bgcolor: transparent, align: right"]38%[/TD]
[TD="bgcolor: transparent, align: right"]$1.74[/TD]
[TD="bgcolor: transparent, align: right"]$1.75[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="align: right"]$1.09[/TD]
[TD="align: right"]38%[/TD]
[TD="align: right"]$1.76[/TD]
[TD="align: right"]$1.85[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]$1.10[/TD]
[TD="bgcolor: transparent, align: right"]38%[/TD]
[TD="bgcolor: transparent, align: right"]$1.77[/TD]
[TD="bgcolor: transparent, align: right"]$1.85[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="align: right"]$1.11[/TD]
[TD="align: right"]38%[/TD]
[TD="align: right"]$1.79[/TD]
[TD="align: right"]$1.85[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]$1.12[/TD]
[TD="bgcolor: transparent, align: right"]38%[/TD]
[TD="bgcolor: transparent, align: right"]$1.81[/TD]
[TD="bgcolor: transparent, align: right"]$1.85[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="align: right"]$1.13[/TD]
[TD="align: right"]38%[/TD]
[TD="align: right"]$1.82[/TD]
[TD="align: right"]$1.85[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
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:



Code: 
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Excel "Strava Best Effort" Calculation
2
Replies
13
Views
804
felixstraube
F
L
  • Solved
help with finding local max and local min (advanced question)
Replies
4
Views
3K
BSALV
BSALV
M
  • Question Question
Price tracking
Replies
0
Views
518
mykeee
M
P
  • Question Question
Carhart 4 factor model run through regression
Replies
0
Views
2K
polly95
P
B
  • Question Question
Need to Return Lowest Price based on Criteria
Replies
3
Views
565
oldbrewer
O

Forum statistics

Threads
1,223,912
Messages
6,175,340
Members
452,638
Latest member
Oluwabukunmi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top