# Creating a formula

• ### Question

• hi guys,i am trying to create a formula, basically im trying to do the below but cannot get it to work.

= (Fee*field13) if field13>5 then Fee/50

im am trying to get a field the will give a 50% discount once the number passes 5. so the first 5 will be full price and then 50% after.

<tfoot></tfoot>
Pricing With GST
SetUp Fee Expr1 Expr2 Expr3 Field13
\$175.00 \$175.00 \$350.00 35 \$385.00 1

(this is in Microsoft Access in a query)
• Edited by Friday, December 15, 2017 5:14 AM
Friday, December 15, 2017 5:13 AM

• First, do yourself a favour and don't give objects meaningless names like Field13, but something meaningful like Quantity.

To return the gross discounted price of a quantity of items sold where a 50% discount on the listed unit price applies to the quantity in excess of 5 sold an expression would be:

IIf(Quantity <= 5,UnitPrice*Quantity,(UnitPrice*5)+((Quantity-5)*UnitPrice/2))

Or as a function:

Public Function DiscountedPrice(UnitPrice As Currency, Quantity As Integer) As Currency

If Quantity <= 5 Then
DiscountedPrice = UnitPrice * Quantity
Else
DiscountedPrice = (UnitPrice * 5) + ((Quantity - 5) * UnitPrice / 2)
End If

End Function

Ken Sheridan, Stafford, England

• Marked as answer by Monday, December 18, 2017 12:31 AM
Friday, December 15, 2017 5:23 PM

### All replies

• First, do yourself a favour and don't give objects meaningless names like Field13, but something meaningful like Quantity.

To return the gross discounted price of a quantity of items sold where a 50% discount on the listed unit price applies to the quantity in excess of 5 sold an expression would be:

IIf(Quantity <= 5,UnitPrice*Quantity,(UnitPrice*5)+((Quantity-5)*UnitPrice/2))

Or as a function:

Public Function DiscountedPrice(UnitPrice As Currency, Quantity As Integer) As Currency

If Quantity <= 5 Then
DiscountedPrice = UnitPrice * Quantity
Else
DiscountedPrice = (UnitPrice * 5) + ((Quantity - 5) * UnitPrice / 2)
End If

End Function

Ken Sheridan, Stafford, England

• Marked as answer by Monday, December 18, 2017 12:31 AM
Friday, December 15, 2017 5:23 PM
• Hi Ken,

Thanks so much that works perfectly i took you advise and changed the names to something more meaningful. really appreciate your help this will make my job that much easier.

Monday, December 18, 2017 12:31 AM