Formula help - billing equation

[mfh1287]

I'm trying to create a formula to figure out how much of a rebate someone will get back based on how much they have spent in advertising. Here is the breakdown:

Spending:	Rate
$0<=Spending<$10,000	0%
$10,000<=Spending<$15,000	20%
$15,000<=Spending<$30,000	30%
$30,000<=Spending<$50,000	40%
$50,000<=Spending	50%

<tbody>
</tbody>

So if you were to spend $12,000, you would get 20% of $2,000 ($12,000-$10,000) rebated back to you. If you spend $31,000, you would get 20% of $5,000, 30% of $15,000 and 40% of $1,000 rebated back to you for a total of $5,900 if my math is correct. This was the easy part.

The part I can't figure out is that we rebate the total amount spent in a month based on the running total of advertising for the year. Here is the spreadsheet.

Client	Current Spending	Running total of advertising in 2013 <tbody> </tbody>	Rebated amount <tbody> </tbody>	Explanation <tbody> </tbody>
<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {font-weight:700; border-top:1.0pt solid windowtext; border-right:none; border-bottom:1.0pt solid windowtext; border-left:1.0pt solid windowtext;} --> </style> Client 1 <tbody> </tbody>	$2,376.90 <tbody> </tbody>	$2,376.90 <tbody> </tbody>
<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {font-weight:700; border-top:1.0pt solid windowtext; border-right:none; border-bottom:1.0pt solid windowtext; border-left:1.0pt solid windowtext;} --> </style> Client 1 <tbody> </tbody>	$168.45 <tbody> </tbody>	$2,545.35 <tbody> </tbody>
<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {font-weight:700; border-top:1.0pt solid windowtext; border-right:none; border-bottom:1.0pt solid windowtext; border-left:1.0pt solid windowtext;} --> </style> Client 2 <tbody> </tbody>	$0.00 <tbody> </tbody>	$0.00
<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {font-weight:700; border-top:1.0pt solid windowtext; border-right:none; border-bottom:1.0pt solid windowtext; border-left:1.0pt solid windowtext;} --> </style> Client 3 <tbody> </tbody>	$500.00 <tbody> </tbody>	$500.00
<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {font-weight:700; border-top:1.0pt solid windowtext; border-right:none; border-bottom:1.0pt solid windowtext; border-left:1.0pt solid windowtext;} --> </style> Client 3 <tbody> </tbody>	$1,300.00 <tbody> </tbody>	$1,800.00 <tbody> </tbody>
<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {font-weight:700; border-top:1.0pt solid windowtext; border-right:none; border-bottom:1.0pt solid windowtext; border-left:1.0pt solid windowtext;} --> </style> Client 3 <tbody> </tbody>	$2,295.00 <tbody> </tbody>	$4,095.00 <tbody> </tbody>
<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {font-weight:700; border-top:1.0pt solid windowtext; border-right:none; border-bottom:1.0pt solid windowtext; border-left:1.0pt solid windowtext;} --> </style> <style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {font-weight:700; border-top:1.0pt solid windowtext; border-right:none; border-bottom:1.0pt solid windowtext; border-left:1.0pt solid windowtext;} --> </style> Client 3 <tbody> </tbody> <tbody> </tbody>	$340.00 <tbody> </tbody>	$4,435.00 <tbody> </tbody>
<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {font-weight:700; border-top:1.0pt solid windowtext; border-right:none; border-bottom:1.0pt solid windowtext; border-left:1.0pt solid windowtext;} --> </style> Client 3 <tbody> </tbody>	$1,000.00 <tbody> </tbody>	$5,435.00 <tbody> </tbody>
<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {font-weight:700; border-top:1.0pt solid windowtext; border-right:none; border-bottom:1.0pt solid windowtext; border-left:1.0pt solid windowtext;} --> </style> Client 3 <tbody> </tbody>	$2,000.00 <tbody> </tbody>	$7,435.00 <tbody> </tbody>
<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {font-weight:700; border-top:1.0pt solid windowtext; border-right:none; border-bottom:1.0pt solid windowtext; border-left:1.0pt solid windowtext;} --> </style> Client 3 <tbody> </tbody>	$1,300.00 <tbody> </tbody>	$8,735.00 <tbody> </tbody>
<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {font-weight:700; border-top:1.0pt solid windowtext; border-right:none; border-bottom:1.0pt solid windowtext; border-left:1.0pt solid windowtext;} --> </style> Client 3 <tbody> </tbody>	$2,295.00 <tbody> </tbody>	$11,030.00 <tbody> </tbody>	$206.00 <tbody> </tbody>	20%*1,030 <tbody> </tbody>
<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {font-weight:700; border-top:1.0pt solid windowtext; border-right:none; border-bottom:1.0pt solid windowtext; border-left:1.0pt solid windowtext;} --> </style> Client 3 <tbody> </tbody>	$1,300.00 <tbody> </tbody>	$12,330.00 <tbody> </tbody>	$260.00 <tbody> </tbody>	20%*1,300 <tbody> </tbody>
<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {font-weight:700; border-top:1.0pt solid windowtext; border-right:none; border-bottom:1.0pt solid windowtext; border-left:1.0pt solid windowtext;} --> </style> Client 3 <tbody> </tbody>	$2,295.00 <tbody> </tbody>	$14,625.00 <tbody> </tbody>	$459.00 <tbody> </tbody>	20%*2,295 <tbody> </tbody>
<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {font-weight:700; border-top:1.0pt solid windowtext; border-right:none; border-bottom:1.0pt solid windowtext; border-left:1.0pt solid windowtext;} --> </style> Client 3 <tbody> </tbody>	$425.00 <tbody> </tbody>	$15,050.00 <tbody> </tbody>	$90.00 <tbody> </tbody>	20%*375 + 30%*50 <tbody> </tbody>

<tbody>
</tbody>

I'm just looking for a formula that will be able to figure out the amount rebated no matter what the running total is, or what the current spending is.

*Notice that the rate is not retroactive, so the formula has to account for that.

Anybody have any suggestions?

 

[DHogg]

This would be a good example of when to use a user defined function rather than lots of nested if statements.

 

[konew1]

What do you mean by this note? *Notice that the rate is not retroactive, so the formula has to account for that.

This formula will work out the monthly amount to pay. It assumes there is always an empty/blank line between clients, and the data doesn't run for mote than a year.

=MIN(MAX(0,C26-10000),5000)*0.2+MIN(MAX(0,C26-15000),15000)*0.3+MIN(MAX(0,C26-30000),20000)*0.4+MIN(MAX(0,C26-50000),5000)*0.5-(MIN(MAX(0,C25-10000),5000)*0.2+MIN(MAX(0,C25-15000),15000)*0.3+MIN(MAX(0,C25-30000),20000)*0.4+MIN(MAX(0,C25-50000),5000)*0.5)

 

[xenou]

I'm just looking for a formula that will be able to figure out the amount rebated no matter what the running total is, or what the current spending is.

I'm pretty sure that any formula would *at least* have to take into account the running total or the current spending. Isn't that the case?
ξ

 

[mfh1287]

I'm pretty sure that any formula would *at least* have to take into account the running total or the current spending. Isn't that the case?
ξ

Yeah, I'm looking for an equation to take into account both things.

 

[Marcelo Branco]

Maybe something like this

Create a table in columns G H I like below

G H I (headers in row 1)

Spending	Rate	Marginal Rate
10000	20%	20%
15000	30%	10%
30000	40%	10%
50000	50%	10%

<tbody>
</tbody>

Formula in I2
=H2-N(H1)
copy down


A B C D (headers in row 1)

Client	Current Spending	Running total of advertising in 2013	Rebated amount
Client 1	2376,9	2376,9	0
Client 1	168,45	2545,35	0
Client 2	0	0	0
Client 3	500	500	0
Client 3	1300	1800	0
Client 3	2295	4095	0
Client 3	340	4435	0
Client 3	1000	5435	0
Client 3	2000	7435	0
Client 3	1300	8735	0
Client 3	2295	11030	206
Client 3	1300	12330	260
Client 3	2295	14625	459
Client 3	425	15050	90

<tbody>
</tbody>

Formula in D2
=IF(A2="","",SUMPRODUCT(--(C2>$L$2:$L$5),C2-$L$2:$L$5,$N$2:$N$5)-SUMIF(A$1:A1,A1,D$1:D1))
copy down

M.

 

[Marcelo Branco]

Sorry, sorry i used the wrong ranges (i was making tests with the table in columns L:N)

Try this in D2

=IF(A2="","",SUMPRODUCT(--(C2>$G$2:$G$5),C2-$G$2:$G$5,$I$2:$I$5)-SUMIF(A$1:A1,A1,D$1:D1))

copy down

M.