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.

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


* 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

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



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

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


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))))),"")