Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate discount amt with conditions | Excel Worksheet Functions | |||
calculate values | New Users to Excel | |||
How to calculate values in multiple values with multi conditions | Excel Worksheet Functions | |||
look up same values in coumn1 and calculate values in 2nd column | Excel Worksheet Functions | |||
calculate the value of a mortgage purchased at a discount | Excel Worksheet Functions |