Price by Volume Formula

[Jon von der Heyden]

Hi there

I am struggling to create a formula to calculate the price per transaction.

Each row in this table is a transaction. The cost is calculated based on the cumulative volume for each customer. Price banding as follows:

Excel Workbook
	A	B
3	Volume >	Price
4	-	3.12
5	1,000	2.85
6	5,000	2.58
7	10,000	2.31
8	20,000	2.04
9	35,000	1.77
10	50,000	1.50
11	65,000	1.23
12	80,000	0.96
13	100,000	0.69
14	125,000	0.41
Prices

So for e.g row7 Cust3, the 1st 999 should be charged at £3.12, and then the remaining 586 should be charged at £2.85. The calculation should be based on the cumulative volume and not the transaction volume.

Any help GREATLY appreciated.

Book4
	A	B	C	D
1	Cust	Volume	Cum	Price
2	Cust1	1654	1654	?
3	Cust1	1106	2760
4	Cust1	1139	3899
5	Cust2	3878	3878
6	Cust2	1539	5417
7	Cust3	1585	1585
8	Cust1	2852	6751
9	Cust1	1249	8000
10	Cust1	1812	9812
11	Cust2	1795	7212
12	Cust2	2995	10207
13	Cust3	3405	4990
14	Cust1	2037	11849
15	Cust1	3744	15593
16	Cust1	3626	19219
17	Cust2	3696	13903
18	Cust2	1576	15479
19	Cust3	1206	6196
20	Cust1	1782	21001
21	Cust1	1162	22163
22	Cust1	686	22849
23	Cust2	2754	18233
24	Cust2	3288	21521
25	Cust3	3816	10012
26	Cust1	1255	24104
27	Cust1	518	24622
28	Cust1	857	25479
29	Cust2	1068	22589
30	Cust2	1885	24474
Sheet1

 

[barry houdini]

Hello Jon,

I'd suggest the method outlined here

 

[Jon von der Heyden]

Thanks Barry

I've been looking at your MEDIAN combo formula suggestion in the challenge of the month "Tricky Commission Formula" trying desperately to figure out if I could adapt it for my needs.

I'll have a read of this and give it a shot.

Thanks muchly!

 

[Richard Schollar]

Well, here's my contribution (I hope it works - I evaluated it for the first couple of examples):

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	3	Volume >	Price	Difference	Cum Difference
2	4	0	3.12	0	0	Cust	Volume	Cum	Price
3	5	999	2.85	-0.27	-0.27	Cust1	1654	1654	4983.63
4	6	4,999	2.58	-0.27	-0.54	Cust1	1106	2760	3152.1
5	7	9,999	2.31	-0.27	-0.81	Cust1	1139	3899	3246.15
6	8	19,999	2.04	-0.27	-1.08	Cust2	3878	3878	11322.03
7	9	34,999	1.77	-0.27	-1.35	Cust2	1539	5417	4160.43
8	10	49,999	1.5	-0.27	-1.62	Cust3	1585	1585	4786.98
9	11	64,999	1.23	-0.27	-1.89	Cust1	2852	6751	7182.12
10	12	79,999	0.96	-0.27	-2.16	Cust1	1249	8000	2885.19
11	13	99,999	0.69	-0.27	-2.43	Cust1	1812	9812	4185.72
12	14	124,999	0.41	-0.28	-2.71	Cust2	1795	7212	4146.45
13						Cust2	2995	10207	6749.97
14						Cust3	3405	4990	9704.25
15						Cust1	2037	11849	3206.97
16						Cust1	3744	15593	5616
17						Cust1	3626	19219	5439
18						Cust2	3696	13903	5544
19						Cust2	1576	15479	2364
20						Cust3	1206	6196	2790.72
21						Cust1	1782	21001	1590.84
22						Cust1	1162	22163	488.04
23						Cust1	686	22849	288.12
24						Cust2	2754	18233	4131
25						Cust2	3288	21521	3288.24
26						Cust3	3816	10012	8804.43
27						Cust1	1255	24104	527.1
28						Cust1	518	24622	217.56
29						Cust1	857	25479	359.94
30						Cust2	1068	22589	448.56
31						Cust2	1885	24474	791.7
Sheet1

Copy I4 down as far as required.

 

[Jon von der Heyden]

Ta Rich! Will also have a look at this...

In the meantime Barry's solution has worked a treat. Well worth reading the article!

 

[GlennUK]

The same kind of logic applies as here:

http://www.tek-tips.com/viewthread.cfm?qid=1282681