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.
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.
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
Hi,
The curly brackets in the formula in column D of Data and Solution sheet have to be entered manually.
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
Tks
PM
Hi,
As per your example, the result should be (100*26)+(300*6)=4400. The formula should be:
=SUMPRODUCT((B5>{0;24;50;75})*(B5-{0;24;50;75})*{0;100;200;200})
B5 has the units sold.
Hope this helps.
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
Hi,
Try this
=SUMPRODUCT(–(A11>{0;100;200}), (A11-{0;100;200}), {2;1;1})
A11 has units consumed, say 205.
Hope this helps.
Thank you So Much Ashish ….. it’s working yaayyyy
You are welcome.
Please Mr. Ashish reply soon i only have one day to solve it
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.
Thanks
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.
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
0%
Up to £150,000 – annual rent is over £1,000
1%
Between £150,001 and £250,000
2%
Over £250,000
5%
Thank you for any help.
Moshe
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%
Example
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
Hi,
Try this formula
=SUMPRODUCT(–(E16>{0;150000;250000}), (E16-{0;150000;250000}), {0;0.02;0.03})
E16 has 275000.
Hope this helps.
Amazing! Thank you so much.
You are welcome.
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.
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!
Geoff
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.
Hii sir ,
i have doubt in calculation of slab rate of different Amounts
for e.g
Taxable income
A of – 1020000
B of – 320000
C of – 840000.
so how to use formula in excel sheet one formula in one raw not in one formula covered every thing, 3 formula as per the slab rate .plz inform sir about this query.
Hi,
I am not clear with your question. Please share more data and show your exact expected result.
Hi,
I want to calculate a rate list on the basis of following table in Excel Sheet:
Left side range is Space/Volume and Right side is the Incremental Slab Rate
Space Amount
1 to 6 Fix 6000
7 to 20 1000
21 to 30 800
31 to 40 600
41 to 50 500
51 to 60 400
61 to 70 350
71 to 80 300
81 to 90 250
91 to 100 200
101 to 200 150
201 to 1000 100
1001 to 1250 125
1251 to 1500 150
1501 to 1750 175
1751 to 2000 200
2001 to 2250 225
2251 to 2500 250
2501 to 2750 275
2751 to 4000 300
Here I have to pay Fix $6000 for 1 to 6 Space; $7000 for 7; $20000 for 20; $20800 for 21 and so on…
Is there any formula to calculate the amount as per the incremental slab rate.
Hi,
Share some input values of size and show the expected result.
Thanks for responding,
As asked, I Could have shown in table format, but its not showing here.
For the input 1 to 6, the output is fixed at 6000.
from 7 to 20, the incremental additional is 1000. So for 7 it is 7000, for 14 it is 14,000, for 17 its 17000, …. till… for 20, its 20000.
Then from 21 to 30, the the incremental additional is 800. So for 21 it is 21800, for 24 it is 23,200, for 27 its 25,600, …. till… for 30, its 28,000.
Similarly, there are slabs as given alongwith the incremental addition for each slab.
Can we arrive at any formula to calculate the amount as per the incremental slab rate.
Thanks.
Thanks….
I worked out my solution by putting the formula:
=SUMPRODUCT(–(C25>{0;1;2;3;4;5;6;20;30;40;50;60;70;80;90;100;200;1000;1250;1500;1750;2000;2250;2500;2750;4000}), (C25-{0;1;2;3;4;5;6;20;30;40;50;60;70;80;90;100;200;1000;1250;1500;1750;2000;2250;2500;2750;4000}), {6000;-6000;0;0;0;0;1000;-200;-200;-100;-100;-50;-50;-50;-50;-50;-50;25;25;25;25;25;25;25;25;25})
and its working. Thanks for the help.
Dear Ashish
Kindly help me in this below slab
If UNIT lot UNIT lot Factor
Less than 0 2,500.00 0%
from 2,500.00 5,000.00 4%
from 5,000.00 10,000.00 8%
from 10,000.00 15,000.00 12%
from 15,000.00 20,000.00 20%
Greater than 20,000.00 – 25%
If i have 9100 unites, then my answer would be 428
I could not get the result in my worksheet
regards
Ceeyes
I am sorry, the headings are not appearing normal..
Please consider as
0.00 to 2,500
2,500 to 5,000 etc.,
Hi,
This formula works fine
=SUMPRODUCT(–(A11>{0;2500;5000;10000;15000;20000}), (A11-{0;2500;5000;10000;15000;20000}), {0;0.04;0.04;0.04;0.08;0.05})
A11 has 9100.
Dear Ashish
I want to Calculate Commission Percentage on LDP.
If LDP Price is below AED15.00 _ Percentage 0%
If LDP Price is Within AED16 to AED20.00 _ Percentage 0.75%
If LDP Price is Within AED21 to AED25.00 _ Percentage 1.00%
If LDP Price is Within AED26 to AED30.00 _ Percentage 1.50%
If LDP Price is Greater than AED30.00 _ Percentage 2.00%
Please provide me Formula.
Hi,
I have already shared the formula in my original Blog post. Please also review the various comments where i have shared multiple versions of the original formula.
Hi Ashish!
While the SUMPRODUCT formula works perfectly, can the VLOOKUP function be used alternatively to solve the same type of problems. The utility bill, for example?
Hi,
No, the VLOOKUP() function cannot be used here.
Hi Ashish
Needed help in following
I have different deals where there are slabs which need to be met to qualify for incentive
I needed a formula which takes care of multiple conditions
For example
Slabs may be 0-10, 10-20, 20-30, 30-40, >40
The incentive is applicable on incremental amounts
So for 0-10, 1%, 20-30, 2%, 30-40, 3% and > 40 5%
The slabs and incentive % will be different for different vendors and that is adding to the complexity
If there any way we can do this in excel?
Compute revenue with progressive discounting
{ 38 Comments }