Tiered Pricing Formula

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

U.S. federal income tax works the same way. Here is an implementation that I tweaked to match to your situation.

Note that I did not add $1 to go to the next tier. As a general rule you do not want a gap of any amount between tiers. Instead of "from" I used "over".

Values as displayed

	A	B	C	D
1	Sale Amount	Commission
2	$ 65,000.00	$ 5,076.50
3
4	Over	Up to
5	$ -	$ 40,000	9.23%	$ 3,692.00
6	$ 40,000	$ 60,000	6.46%	$ 1,292.00
7	$ 60,000		1.85%	$ 92.50
8			Total Commission	$ 5,076.50
Commission

Underlying formulas

	A	B	C	D
1
2		=D8
3
4
5				=C5*MIN($A$2,B5)
6	=B5			=MAX(0,C6*(MIN($A$2,B6)-A6))
7	=B6			=MAX(0,C7*($A$2-A7))
8				=SUM(D5:D7)