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

iandb

New Member
Joined
Feb 16, 2009
Messages
34
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

mamady

Board Regular
Joined
Sep 23, 2011
Messages
238
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEF
1From To%
205000100%
35001800060%
480011000050%
5$ 60.00Price100011300040%
6130011500030%
7150011800020%
8180012000010%
9200010%
Sheet1
Cell Formulas
RangeFormula
A5A5=Sheet2!A1*(1-VLOOKUP(Sheet2!C1,Sheet1!$D$1:$F$9,3,1))


Book1
ABCD
1$ 100.00Price11000
2
Sheet2


Regards
Yusuf
 
Solution

iandb

New Member
Joined
Feb 16, 2009
Messages
34
Office Version
  1. 365
Platform
  1. Windows
Hi Yusuf,

Many thanks. Will give this a try
 

Sufiyan97

Active Member
Joined
Apr 12, 2019
Messages
454
Office Version
  1. 2013
Platform
  1. Windows
Is it what you want

Sheet1

Book3
AB
1PriceDiscount
2
3
4
5190001900
Sheet1
Cell Formulas
RangeFormula
B5B5=VLOOKUP(A5,Sheet2!$C$1:$D$7,2,TRUE)*A5



Sheet2

Book3
ABCD
101
250010.6
380010.5
4100010.4
5130010.3
6150010.2
7180010.1
Sheet2
 

iandb

New Member
Joined
Feb 16, 2009
Messages
34
Office Version
  1. 365
Platform
  1. Windows
Is it what you want

Sheet1

Book3
AB
1PriceDiscount
2
3
4
5190001900
Sheet1
Cell Formulas
RangeFormula
B5B5=VLOOKUP(A5,Sheet2!$C$1:$D$7,2,TRUE)*A5



Sheet2

Book3
ABCD
101
250010.6
380010.5
4100010.4
5130010.3
6150010.2
7180010.1
Sheet2
Thanks for the reply, have tried and tested Yusuf reply, just need to tweak to an existing quote spreadsheet
 

Forum statistics

Threads
1,143,909
Messages
5,721,458
Members
422,363
Latest member
Bogus_Potatoes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top