Formula for tiered pricing

[riaface]

Hello,


I need to calculate spend based on the number of emails I send a month. The emails are priced using the below grid:


Up to 12k emails per month: Free
Next 1m emails per month (per thousand emails): $0.20
Next 5m emails per month (per thousand emails): $0.15
Remaining emails (per thousand): $0.10


So if I was sending 10,000,000 emails, it would work out like this:
TIER SENDS COST
Free 12,000 $0.00
$0.20 per thousand tier (next 1m) 1,000,000 - $200.00
$0.15 per thousand tier (next 5m) 5,000,000 - $750.00
$0.10 per thousand tier (remaining emails) 3,988,000 - $398.80
MONTHLY TOTAL 10,000,000 $1,348.8


Is there a single line formula I could use to calculate this? So number of emails, for example, is in A1, and I want the cost to show in A2.


Thanks!

 

[riaface]

P.S. I should probably point out that I'm not a spammer, it's an app sending notification emails (hence why there's so many). We're using Mandrill: Pricing - Mandrill

 

[t0azt]

This should work:

=IF(AND(A1>12000,A1<=1000000),((A1-12000)/1000)*0.2,IF(AND(A1>1000000,A1<=5000000),200+((A1-1012000)/1000)*0.15,IF(A1>5000000,950+((A1-6012000)/1000)*0.1)))

 

[Dave Patton]

Excel Workbook
	A	B	C	D	E	F	G
1	Number	Cost			Bracket	Rate	Rate_Diff
2	10,000,000.00	1,348.80			0	0.000%	0.000%
3					12,000.00	0.020%	0.020%
4					1,012,000.00	0.015%	-0.005%
5					6,012,000.00	0.010%	-0.005%
6
1b

Excel 2003

Cell Formulas
Range	Formula
B2	=SUMPRODUCT(--(A2>rB),A2-rB,rRate)
G2	=F2-N(F1)

Excel Workbook
Name	Refers To
rB	='1b'!$E$2:$E$5
rRate	='1b'!$G$2:$G$5
Workbook Defined Names

 

[joeu2004]

I need to calculate spend based on the number of emails I send a month. The emails are priced using the below grid:
Up to 12k emails per month: Free
Next 1m emails per month (per thousand emails): $0.20
Next 5m emails per month (per thousand emails): $0.15
Remaining emails (per thousand): $0.10

Try the following paradigm, explained below.

[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD="align: right"]
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD="align: right"]10,000,000
[/TD]
[TD="align: right"]1,348.80[/TD]
[TD]
[/TD]
[TD]=ROUND(SUMPRODUCT((A1>$A$4:$A$7)*(A1-$A$4:$A$7), $C$4:$C$7), 2)[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[TD="align: right"]Over--[/TD]
[TD="align: right"]Per 1000[/TD]
[TD="align: right"]Diff per 1[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.000000[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]5
[/TD]
[TD="align: right"]12,000[/TD]
[TD="align: right"]0.20[/TD]
[TD="align: right"]0.000200[/TD]
[TD]=(B5-B4)/1000[/TD]
[/TR]
[TR]
[TD="align: right"]6
[/TD]
[TD="align: right"]1,012,000[/TD]
[TD="align: right"]0.15[/TD]
[TD="align: right"]-0.000050[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]7
[/TD]
[TD="align: right"]6,012,000[/TD]
[TD="align: right"]0.10[/TD]
[TD="align: right"]-0.000050[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

The formula for B1 is shown in D1.

The formula for C5 is shown in D5. Copy C5 and paste into C6:C7.