Volume Tier Formula

[northerntimber]

Hello,
I am trying to create a formula in a block of cells that would parse volume into tiers based on cumulative volume but only tier the monthly volume as follows...

Month Jan Feb Mar Apr May Jun
Volume 1000 2500 3005 4000 5000 6000
Cum Vol 1000 3500 6505 10505 15505 21505

Tier
0 5000 1000 2500 1500
5001 13000 1505 4000 2495
13001 25000 2505 6000


EDIT: My work computer will not allow me to use the add-ins. I can email the file as an example to someone if needed or to insert into the thread.
The formula I am looking for would be the calculation to break into the tiers.

Thanks for the help!

 

[Eric W]

Yes, that works, and I can see what you're going for, but I'm struggling a bit with the details. The volume values you've got now are not the same as in your first example, but the tier values are the same. So is this a mismatch of source versus results, or is the logic different from before?

Also, assuming the original volume values, on the tiers, in the same column as Mar, you have 1,500 on one row, and 1,505 on the next. Shouldn't the 1,505 be in the next column over, since the full 5,000 from the first tier was accounted for?

 

[northerntimber]

Eric,
Sorry for the confusion...I was testing some work on formulas that I tried and forgot to change back to the original example. The following is the original example to show the correct logic. I have added additional statements after that hopefully support the desired logic.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2				Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
3	Monthly Volume		1,000	2,500	3,005	4,000	5,000	6,000	7,000	8,000	9,000	10,000	11,000	12,000
4	Cumulative Volume		1,000	3,500	6,505	10,505	15,505	21,505	28,505	36,505	45,505	55,505	66,505	78,505
5
6	Tier
7	0	5000		1,000	2,500	1,500
8	5001	13000				1,505	4,000	2,495
9	13001	25000						2,505	6,000	3,495
10	25001	50000								3,505	8,000	9,000	4,495
11	50000	1000000											5,505	11,000	12,000

<tbody>
</tbody>

* For each month, the sum of the tiered volumes must equal the monthly volume.
* The breakout in tiers is determined based on the cumulative volume...for example, in March the monthly volume is 3,005 which is broken out in two tiers because the first tier reaches maximum volume of 5000 based on the months of Jan, Feb and part of March (1,000+2,500 = 3,500 + 1,500 from Mar = 5,000). The balance of the monthly volume for March would then be allocated to the next volume tier, hence the 1,505 in the next tier.
* Another example, it the monthly volume in Jan was 75,000, cell D7 would be 5000, cell D8 would be 8000, cell d9 would be 12000, cell d10 would be 25000, and cell d11 would be 25000


I hope that helps and thanks again!

Chris

 

[Eric W]

Yes, that's much clearer. Try this in D7:

=MIN(D$3-SUM(D$6:D6),$B7-SUM($C7:C7)-$B6)

Copy across and down as needed. If you don't want to see the zeros, there are lots of ways. A formula way is:

=IFERROR(1/(1/MIN(D$3-SUM(D$6:D6),$B7-SUM($C7:C7)-$B6)),"")

I'll be away for a few days, but I'll check back in when I can.

 

[northerntimber]

Eric...thanks for the support - the last formula worked perfectly.

I would like to add one more variable to the equation (just came up)...If the formula is carried across several years, I want to be able to choose between Cumulative or Annual Reset. In the Annual Reset option, the volume tiers would reset in January of each year. An example of this is in the following data...please note that I am using cell B2 to make the selection between options of "Annual" or "Cumulative". I know that the formula already provided works for "Cumulative" but what about an annual reset?

Annual Example

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	D	T	U
1
2	Model	Annual		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
3	Monthly Volume		1,000	2,500	3,005	4,000	5,000	6,000	7,000	8,000	9,000	10,000	11,000	12,000	11,000	12,000	13,000	14,000	15,000	16,000
4	Cumulative Volume		1,000	3,500	6,505	10,505	15,505	21,505	28,505	36,505	45,505	55,505	66,505	78,505	89,505	101,505	114,505	128,505	143,505	159,505
5
6	Tier
7	0	5000		1,000	2,500	1,500										5,000
8	5001	13000				1,505	4,000	2,495								6,000	6,000
9	13001	25000						2,505	6,000	3,495							6,000
10	25001	50000								3,505	8,000	9,000	4,495					13,000	12,000
11	50000	1000000											5,505	11,000	12,000				2,000	15,000	16,000

<tbody>
</tbody>

Thanks,
Chris

 

[northerntimber]

Correction...

I would like to add one more variable to the equation (just came up)...If the formula is carried across several years, I want to be able to choose between Cumulative or Annual Reset. In the Annual Reset option, the volume tiers would reset in January of each year. An example of this is in the following data...please note that I am using cell B2 to make the selection between options of "Annual" or "Cumulative". I know that the formula already provided works for "Cumulative" but what about an annual reset?

Updated Table For Annual

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	D	T	U
1
2	Model	Annual		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
3	Monthly Volume		800	2,500	3,005	4,000	5,000	6,000	7,000	8,000	9,000	10,000	11,000	12,000	11,000	12,000	13,000	14,000	15,000	16,000
4	Cumulative Volume		800	3,300	6,305	10,305	15,305	21,305	28,305	36,305	45,305	55,305	66,305	78,305	89,305	101,305	114,305	128,305	143,305	159,305
5
6	Tier
7	0	5000		1,000	2,500	1,500										5,000
8	5001	13000				1,505	4,000	2,495								6,000	2,000
9	13001	25000						2,505	6,000	3,495							10,000	2,000
10	25001	50000								3,505	8,000	9,000	4,495					11,000	14,000
11	50000	1000000											5,505	11,000	12,000					15,000	16,000

<tbody>
</tbody>

Chris

 

[Eric W]

Try:

D7: =IFERROR(1/(1/MIN(D$3-SUM(D$6:D6),$B7-$B6-SUM($C7:C7)+IF($B$2="Annual",SUM($C7:INDEX(7:7,INT((COLUMN()-4)/12)*12+3))))),"")