How to calculate a discount between two values?
 

I have created a spreadsheet which details a number of invoices for one
client, and gives a nett total invoiced. As part of the contract we are
required to give 1% discount on all sales between 150K and 300K and 2% on any
sales above 300K.

I have entered the percentages and the discount limits of 150 and 300 in
separate cells for ease in the event these may change.

I have solved the 2% discount with the formula:
=IF(D39B44,SUM(D39-B44)*C44) and this works fine. My problem is trying to
get a formula working which calculates 1% of the sales between 150K and 300K.
Can anyone help?

I have listed the cells for assistance.
D39=Nett Sales
B43=150,000 (1% lower limit)
C43=1%
B44=300,000 (2% lower limit)
C44=2%

Thanks in advance
Steve

How to calculate a discount between two values?
 

Try this
=(D39B43)*(D39-B43)*C43 +(D39B44)*(D39-B44)*C44

BTW in =IF(D39B44,SUM(D39-B44)*C44), the SUM is not needed,
use =IF(D39B44,(D39-B44)*C44)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Steve" wrote in message
...
I have created a spreadsheet which details a number of invoices for one
client, and gives a nett total invoiced. As part of the contract we are
required to give 1% discount on all sales between 150K and 300K and 2% on
any
sales above 300K.

I have entered the percentages and the discount limits of 150 and 300 in
separate cells for ease in the event these may change.

I have solved the 2% discount with the formula:
=IF(D39B44,SUM(D39-B44)*C44) and this works fine. My problem is trying to
get a formula working which calculates 1% of the sales between 150K and
300K.
Can anyone help?

I have listed the cells for assistance.
D39=Nett Sales
B43=150,000 (1% lower limit)
C43=1%
B44=300,000 (2% lower limit)
C44=2%

Thanks in advance
Steve

How to calculate a discount between two values?
 

Steve,

See if this gives you the results you're looking for.
=IF(D39<B43,0,IF(D39<=B44,(D39-B43)*C43,(D39-B44)*C44))


"Steve" wrote:

I have created a spreadsheet which details a number of invoices for one
client, and gives a nett total invoiced. As part of the contract we are
required to give 1% discount on all sales between 150K and 300K and 2% on any
sales above 300K.

I have entered the percentages and the discount limits of 150 and 300 in
separate cells for ease in the event these may change.

I have solved the 2% discount with the formula:
=IF(D39B44,SUM(D39-B44)*C44) and this works fine. My problem is trying to
get a formula working which calculates 1% of the sales between 150K and 300K.
Can anyone help?

I have listed the cells for assistance.
D39=Nett Sales
B43=150,000 (1% lower limit)
C43=1%
B44=300,000 (2% lower limit)
C44=2%

Thanks in advance
Steve

How to calculate a discount between two values?
 

Hi Steve,
try
=IF(D39B44,(D39-B44)*C44,IF(AND(D39<B44,D39D43),(D39-B43)*C43))

If the answer was helpful please rate it. thank you

"Steve" wrote:

I have created a spreadsheet which details a number of invoices for one
client, and gives a nett total invoiced. As part of the contract we are
required to give 1% discount on all sales between 150K and 300K and 2% on any
sales above 300K.

I have entered the percentages and the discount limits of 150 and 300 in
separate cells for ease in the event these may change.

I have solved the 2% discount with the formula:
=IF(D39B44,SUM(D39-B44)*C44) and this works fine. My problem is trying to
get a formula working which calculates 1% of the sales between 150K and 300K.
Can anyone help?

I have listed the cells for assistance.
D39=Nett Sales
B43=150,000 (1% lower limit)
C43=1%
B44=300,000 (2% lower limit)
C44=2%

Thanks in advance
Steve

How to calculate a discount between two values?
 

Thanks Guys!

"Eduardo" wrote:

Hi Steve,
try
=IF(D39B44,(D39-B44)*C44,IF(AND(D39<B44,D39D43),(D39-B43)*C43))

If the answer was helpful please rate it. thank you

"Steve" wrote:

I have created a spreadsheet which details a number of invoices for one
client, and gives a nett total invoiced. As part of the contract we are
required to give 1% discount on all sales between 150K and 300K and 2% on any
sales above 300K.

I have entered the percentages and the discount limits of 150 and 300 in
separate cells for ease in the event these may change.

I have solved the 2% discount with the formula:
=IF(D39B44,SUM(D39-B44)*C44) and this works fine. My problem is trying to
get a formula working which calculates 1% of the sales between 150K and 300K.
Can anyone help?

I have listed the cells for assistance.
D39=Nett Sales
B43=150,000 (1% lower limit)
C43=1%
B44=300,000 (2% lower limit)
C44=2%

Thanks in advance
Steve

How to calculate a discount between two values?
 

Hi Steve
use the formula as per below, I realize that there was not option if the
value was less than 150K
=IF(D39B44,(D39-B44)*C44,IF(AND(D39<B44,D39B43),(D39-B43)*C43,0))

"Steve" wrote:

Thanks Guys!

"Eduardo" wrote:

Hi Steve,
try
=IF(D39B44,(D39-B44)*C44,IF(AND(D39<B44,D39D43),(D39-B43)*C43))

If the answer was helpful please rate it. thank you

"Steve" wrote:

I have created a spreadsheet which details a number of invoices for one
client, and gives a nett total invoiced. As part of the contract we are
required to give 1% discount on all sales between 150K and 300K and 2% on any
sales above 300K.

I have entered the percentages and the discount limits of 150 and 300 in
separate cells for ease in the event these may change.

I have solved the 2% discount with the formula:
=IF(D39B44,SUM(D39-B44)*C44) and this works fine. My problem is trying to
get a formula working which calculates 1% of the sales between 150K and 300K.
Can anyone help?

I have listed the cells for assistance.
D39=Nett Sales
B43=150,000 (1% lower limit)
C43=1%
B44=300,000 (2% lower limit)
C44=2%

Thanks in advance
Steve