none
Creating a formula RRS feed

  • 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)
    • Edited by Ramon-123 Friday, December 15, 2017 5:14 AM
    Friday, December 15, 2017 5:13 AM

Answers

  • 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 Ramon-123 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 Ramon-123 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