Google sheets scaling discount question

[Berandon]

I am trying to figure out the best way to give a discount on a sliding scale.

I have a sheet that is giving square footage pricing, but as the price gets higher, we can offer a discount.
I would like it to look like:
$0-$6,000 = no discount
$6,001-$10,000 = 5%
$10,001 - $15,000 = 10%
$15,001 - $20,000+ = 15%

Any help would be greatly appreciated.
Thank you!

 

[DRSteele]

I don't know whether this works outside of Excel, but this is the idea:

MrExcel posts20.xlsx
	D	E	F	G	H	I
2	hurdle	discount
3	0	0
4	6001	5%		customer	price	discount
5	10001	10%		Tom	11068	10%
6	15001	15%		Dyck	5285	0%
7				Harry	7515	5%
8				Zed	75691	15%
9				Fred	6000	0%
10				Ted	10001	10%
11
Sheet11

Cell Formulas
Range	Formula
I5:I10	I5	=XLOOKUP(H5,$D$3:$D$6,$E$3:$E$6,,-1)

 

[Berandon]

I was thinking to use IFS, but i usually write them smallest to biggest. But i read the help section some and it says it goes in order, so i flipped the forumla to start from the largest number and worked backwards and it seems to work.

Let me know if you see any potential issues with this formula

=ifs(Test!B19>15000,Test!B19*0.15,Test!B19>10000,Test!B19*0.1,Test!B19>6000,Test!B19*0.05,Test!B19<6000,0)

I don't know whether this works outside of Excel, but this is the idea:

MrExcel posts20.xlsx
	D	E	F	G	H	I
2	hurdle	discount
3	0	0
4	6001	5%		customer	price	discount
5	10001	10%		Tom	11068	10%
6	15001	15%		Dyck	5285	0%
7				Harry	7515	5%
8				Zed	75691	15%
9				Fred	6000	0%
10				Ted	10001	10%
11
Sheet11

Cell Formulas
Range	Formula
I5:I10	I5	=XLOOKUP(H5,$D$3:$D$6,$E$3:$E$6,,-1)

Your solution worked in sheets!

Thank you for your help!

 

[DRSteele]

You're welcome.

The problems with your IFS formula are that the list of hurdles could be much longer and you have hard-coded the hurdles (and we know hard-coding variables is bad spreadsheet practice).