Formula(s) to apply sales % discount depending on set values

[iandb]

Hi All

Sorry unable to us the mini sheet.

I am looking to set up a sales quote and have a formula (or what ever would be best , easiest), or more than one if required, that will apply a set discount based on certain values. Hopefully explained clear enough below.

Basically, Sheet1 Cell A5 = the price to be quoted depending on criteria, end result of formula(s)

Sheet 2 will have the criterial, so...

Sheet 2 cell A1 = Full list price manually entered (eg £1234.56)

Sheet 2 Cell C1 = This will be a blank cell and have manually entered a number from 1 - 20000

I am looking for a discount percentage to be calculated based on this number to be applied to the list price and be shown on sheet 1 cell A5

If number in Sheet 2 Cell C1 is between 0 and 5000 then 100% discount applied
If number in Sheet 2 Cell C1 is between 5001 and 8000 then 60% discount applied
If number in Sheet 2 Cell C1 is between 8001 and 10000 then 50% discount applied
If number in Sheet 2 Cell C1 is between 10001 and 13000 then 40% discount applied
If number in Sheet 2 Cell C1 is between 13001 and 15000 then 30% discount applied
If number in Sheet 2 Cell C1 is between 15001 and 18000 then 20% discount applied
If number in Sheet 2 Cell C1 is between 18001 and 20000 then 10% discount applied
If number in Sheet 2 Cell C1 is greater than 20001 then 0% discount applied


Many thanks

 

[mamady]

Hi Dear,

To avoid a lengthy formula, this requires creating a mapping table (which you will have the flexibility to adjust the tiers in the future).

Please check the below:

Book1
	A	B	C	D	E	F
1				From	To	%
2				0	5000	100%
3				5001	8000	60%
4				8001	10000	50%
5	$ 60.00	Price		10001	13000	40%
6				13001	15000	30%
7				15001	18000	20%
8				18001	20000	10%
9				20001		0%
Sheet1

Cell Formulas
Range	Formula
A5	A5	=Sheet2!A1*(1-VLOOKUP(Sheet2!C1,Sheet1!$D$1:$F$9,3,1))

Book1
	A	B	C	D
1	$ 100.00	Price	11000
2
Sheet2

Regards
Yusuf

 

[iandb]

Hi Yusuf,

Many thanks. Will give this a try

 

[Sufiyan97]

Is it what you want

Sheet1

Book3
	A	B
1	Price	Discount
2
3
4
5	19000	1900
Sheet1

Cell Formulas
Range	Formula
B5	B5	=VLOOKUP(A5,Sheet2!$C$1:$D$7,2,TRUE)*A5

Sheet2

Book3
	A	B	C	D
1			0	1
2			5001	0.6
3			8001	0.5
4			10001	0.4
5			13001	0.3
6			15001	0.2
7			18001	0.1
Sheet2

 

[iandb]

Is it what you want

Sheet1

Book3
	A	B
1	Price	Discount
2
3
4
5	19000	1900
Sheet1

Cell Formulas
Range	Formula
B5	B5	=VLOOKUP(A5,Sheet2!$C$1:$D$7,2,TRUE)*A5

Sheet2

Book3
	A	B	C	D
1			0	1
2			5001	0.6
3			8001	0.5
4			10001	0.4
5			13001	0.3
6			15001	0.2
7			18001	0.1
Sheet2

Thanks for the reply, have tried and tested Yusuf reply, just need to tweak to an existing quote spreadsheet

 

[Sufiyan97]

Thanks for the reply, have tried and tested Yusuf reply, just need to tweak to an existing quote spreadsheet

No problem.