Compute revenue with progressive discounting


Assume a two column database with airline names in column A and number of passenger seats in column B, one may want to know the total revenue.  Given a full rate of US$ 12.0 per seat, discounts are offered on this full rate depending upon the passenger seats booked.  So for example, for the first 500,000 seats, discount would be 1% on the full rate, then for the next 500,000 seats, discount would be 2% on the full rate and so on.

You may refer to my solution in this workbook.

RSS 23 Comments…

 Share your views
  1. Can you please help me with the formula below?

    I need to calculate the tax based on the following figures:
    Low Threshold High Threshold Tax Rate
    1000 0%
    1001 2000 22%
    2001 4000 24%
    4001 8000 27%
    8001 and over 30%

    Does anyone know the single formula that can be used to calculate the tax bearing in mind the differential scale?

    Please let me have your wisdom on this. Thank you!

    • Hi,

      Try this

      1. In range B3:B7, enter 0,1001,2001,4001 and 8001
      2. In range C3:C7, enter 1000,2000,4000,8000 and 100000
      3. In cell B11, enter a person's income
      4. In cell C11, enter this formula

      =SUMPRODUCT(--(B11>{0;1000;2000;4000;8000}), (B11-{0;1000;2000;4000;8000}), {0;0.22;0.02;0.03;0.03})

      Hope this helps.

  2. How to use } in your formula, should it be entered as ctrl shift enter as an array or you can just type and enter the formula. Pl

  3. Dear All

    I need your help, I am trying to calculate an incentive chart (quota calculation) for my dealers, I am establishing a new quota payment for them which must work as follows

    Model Scale 1 from 0 to 24 units From 25 to 50 units From 51 to 75 units Above 76 units
    Accent Scale 1 is ZERO Scale B is $ 100 Scale C is $ 300 and Scale D is $ 500

    How this should work is for example:

    If a dealer sells 56 units, then we will pay him $ 100 x 50 units plus $ 300 x 6 = 6800

    Please help us with this formula



  4. i need a formula to calculate a utility bill. its a bet among my friend to calculate so please help....
    1-100 units ---------- 2/unit
    101-200 units ------- 3/unit
    201-above units-------- 4/unit
    i need to solve this using "IF" option in excel

  5. Please Mr. Ashish reply soon i only have one day to solve it

  6. From To Rates Distance Travelled Rate Per Ton
    1 125 212.7 20
    126 150 259.4 10
    151 175 290.7 200
    176 200 325.1 230
    201 225 356.9 270
    226 250 391.1 175
    251 275 425.3

    There are rates as per slabs, I need to calculate and print against the given distance travelled, also the slabs are till 4000 Kms so I will need a formula that uses cell address instead of writing values in formula. I hope excel can do this.

    • Hi,

      Please be courteous enough to start your conversation with a Hi (atleast). Next, your data is all garbled. Upload your workbook to OneDrive and share the link of the of the workbook here. In the workbook, also clearly show your expected result.

  7. Hello
    I don't know if you can help me, but I am trying to create and Excel formula to calculate the new non-residential stamp duty. The figures are below.
    Does anyone know how I can do this?

    Up to £150,000 - annual rent is under £1,000


    Up to £150,000 - annual rent is over £1,000


    Between £150,001 and £250,000


    Over £250,000


    Thank you for any help.

    • Hi,

      I am not clear with your question. Please take some example and explain your expected solution.

      • Sorry I was not clear. Stamp Duty Land Tax on a commercial property has change to be in brackets:

        Property or lease premium or transfer value SDLT rate
        Up to £150,000 Zero
        The next £100,000 (the portion from £150,001 to £250,000) 2%
        The remaining amount (the portion above £250,000) 5%

        If you buy a freehold commercial property for £275,000, the SDLT you owe is calculated as follows:
        0% on the first £150,000 = £0
        2% on the next £100,000 = £2,000
        5% on the final £25,000 = £1,250
        Total SDLT = £3,250

  8. Ashish please can you help me on this one:

    This is a breakdown of how the total amount of SDLT was calculated based on the rules from 1 April 2016

    The new residential stamp SDLT on £2.5m

    Purchase price bands (£)

    Percentage rate (%)

    SDLT due (£)

    Up to 125,000 3% 3,750
    Above 125,000 and up to 250,000 5% 6,250
    Above 250,000 and up to 925,000 8% 54,000
    Above 925,000 and up to 1,500,000 13% 74,750
    Above 1,500,000+ 15% 105,000

    Total SDLT due 243,750

    This is from the Government Land Tax Calculator.

    • Hi,

      The answer should be 288,750. The last figure should be 150,000 (not 105,000). The formula will be:

      =SUMPRODUCT(--(A10>{0;125000;250000;925000;1500000}), (A10-{0;125000;250000;925000;1500000}), {0.03;0.02;0.03;0.05;0.02})

      A10 has 2500000

      Hope this helps.

  9. Hi Ashish,

    Slightly complicated one for you, but I'm looking to use excel to calculate a commission rate for any given fee. There are different rates on different portions of the fee. The ranges and rates are as follows (in millions):

    £50 ∞ 0.1%
    £30 £50 0.2%
    £20 £30 0.5%
    £10 £20 1%
    £5 £10 2%
    £2 £5 3%
    £1 £2 5%
    £0.5 £1 8%
    £0.2 £0.5 10%
    £0.1 £0.2 15%
    £0 £0.1 20%

    Would you be able to create a formula which generates the % rate for any given fee please?

    Many thanks in advance!

    • Hi,

      This is the exact problem I have shared my solution for in the Blog article. Please either download the workbook from the article and try to apply the formula yourself or refer to my solution in the Comments section. You may specifically refer to my solution (in the Comments section) dated September 12, 2012.

      If you are unable to get the formula to work (after trying), then let me know.

Leave a Comment

Your email address will not be published.