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

thanks in advance.

(this is in Microsoft Access in a query)

Answer 1

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

Answer 2

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.