none
Creating a formula RRS feed

  • Question

  • hi all,

    i need a formula that would be able to do this:

    I'm 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, and once the number reaches 20 25% discount price thereafter.

    First 5 of same sample for same analysis  =  full price

    Next 15 after that = half price

    From 20 samples onwards = quarter price

    thanks in advance

    Thursday, January 11, 2018 11:31 PM

Answers

  • Hello Ramon-123,

    You could try to embed an IIF statement base on the your previous IIF statement.

    Here is the example.

    IIf(
        [Sample Quantity]<=5,
        [Fee]*[Sample Quantity],
        IIF(
    	[Sample Quantity]<=20,
    	[Fee]*5+([Sample Quantity]-5)*[Fee]/2,
            5*[Fee]+15*[Fee]/2+([Sample Quantity]-20)*[Fee]/4
            )
    ) +[Setup]

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Ramon-123 Sunday, January 14, 2018 11:08 PM
    Friday, January 12, 2018 9:23 AM

All replies

  • Hi,

    What is your application? Access, Excel or other? 

    Regards,

    Ashidacchi

    P.S.
    Are you a student and is it a school assignment?
    • Edited by Ashidacchi Friday, January 12, 2018 3:06 AM
    Friday, January 12, 2018 2:57 AM
  • Hi,

    this is in Access no i am not a student. i have gotten this far with the formula

    Discount ex GST: IIf([Sample Quantity]<=5,[Fee]*[Sample Quantity],([Fee]*5)+(([Sample Quantity]-5)*[Fee]/2))+[SetUp]

    which gives me the full price for the first 5 and then the 50% discount for the next 15 but i cannot get the 25% discount once it goes past 20 to work.

    Regards,

    Friday, January 12, 2018 5:27 AM
  • Hello Ramon-123,

    You could try to embed an IIF statement base on the your previous IIF statement.

    Here is the example.

    IIf(
        [Sample Quantity]<=5,
        [Fee]*[Sample Quantity],
        IIF(
    	[Sample Quantity]<=20,
    	[Fee]*5+([Sample Quantity]-5)*[Fee]/2,
            5*[Fee]+15*[Fee]/2+([Sample Quantity]-20)*[Fee]/4
            )
    ) +[Setup]

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Ramon-123 Sunday, January 14, 2018 11:08 PM
    Friday, January 12, 2018 9:23 AM
  • Hi Terry,

    Thanks very much i had thought about embedding an extra IIF statement but i just couldn't get it to work and i didn't have time time to figure it out. Thanks very much this works great.

    Regards,

    Isaac

    Sunday, January 14, 2018 11:10 PM