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]

Quite tricky, it's likely there's an easier formula, but try:

Book1
	A	B	C	D	E	F	G
1	Month	Jan	Feb	Mar	Apr	May	Jun
2	Volume	1000	2500	3005	4000	5000	6000
3	Cum Vol	1000	3500	6505	10505	15505	21505
4
5
6
7	Tier
8	1	5000		1000	2500	1500
9	5001	13000		1505	4000	2495
10	13001	25000		2505	6000
Sheet3

Cell Formulas
Range	Formula
B3	=SUM($B2:B2)
D8	=IFERROR(1/(1/MIN($B8-SUM($C8:C8)-$B7,INDEX($3:$3,AGGREGATE(15,6,COLUMN($B$3:$M$3)/($B$3:$M$3-SUM($D$7:$O7)-SUM($C8:C8)>0),1))-SUM($C8:C8)-$B7)),"")

Put the B3 formula in and drag to the right.

Put the D8 formula in and copy down and to the right as needed. Note that it is required to have an empty row above 8, and an empty column in C. Let us know if this works for you.

 

[northerntimber]

Eric...thanks for the support. Unfortunately, my post didn't probably show the desired result correctly as I can't provide a snapshot of what is expected. sorry.

 

[Eric W]

Other than spacing, my formula generated the same numbers as your original post. Is that not what you're looking for, or do you not need help anymore?

 

[northerntimber]

Eric...l greatly appreciate your help (and still need the assistance). I inserted the formula that you provided but it does not work to what I was expecting to see. As mentioned, I don't think that my original post provided the layout of what formula i was looking for.

 

[Eric W]

I'm a little unsure how else I can help. I need to understand why the formula does not work as you expect. Somehow you need to explain that. Possibly in words, although "it does not work to what I was expecting to see" does not provide me with anything I can use. What were you expecting to see? On the screen print I provided, what looks right, what doesn't? Did you try a different set of monthly values?

If you can't use add-ins, you can possibly use the table tool under the Advanced menu. It can be a bit tedious, but it does work. Another option is to put your spreadsheet on a file sharing service such as DropBox and post a link here. But be aware that many people here can't or won't open files off the internet for the usual reasons. And regrettably, your idea of emailing someone a spreadsheet is against the rules here.

So I'd love to help, but until I understand what you want, I'm sort of stuck.

 

[Eric W]

In order to use the Table tool, click on Reply to Thread, then in the bottom right, click Go Advanced. You'll see a slightly larger box with 3 rows of icons on top. Click on the first icon in the third row. Then tell it how many rows, how many columns, and for Table Style pick Full Grid. Once you do that, you'll see an empty grid and you enter your values. Use Tab to move move cell to cell. Here's what my previous table from post 2 would look like:


[TABLE="class: grid, width: 500"]
<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]
[/TR]
[TR]
[TD]1[/TD]
[TD]Month[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Volume[/TD]
[TD]1000[/TD]
[TD]2500[/TD]
[TD]3005[/TD]
[TD]4000[/TD]
[TD]5000[/TD]
[TD]6000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Cum Vol[/TD]
[TD]1000[/TD]
[TD]3500[/TD]
[TD]6506[/TD]
[TD]10505[/TD]
[TD]15505[/TD]
[TD]21505[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Tier[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]1[/TD]
[TD]5000[/TD]
[TD][/TD]
[TD]1000[/TD]
[TD]2500[/TD]
[TD]1500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]5001[/TD]
[TD]13000[/TD]
[TD][/TD]
[TD]1505[/TD]
[TD]4000[/TD]
[TD]2495[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]13001[/TD]
[TD]25000[/TD]
[TD][/TD]
[TD]2505[/TD]
[TD]6000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

It took me about 4 minutes to do that, not too terrible. But the data can be copied from the screen to a spreadsheet, making it much easier for someone to work on it. Plus we can see exactly where things are.

 

[northerntimber]

Eric,
Thanks for the follow-up post and additional support.

I clicked on "Reply to Thread" and then clicked on "Go Advanced". I do not get the larger box with icons.

I am seeing the message input box followed by a group of post icons and then another box with "Additional Options" for HTML, Subscription and Rate Thread.

Chris

 

[Eric W]

I'm not sure what else to tell you. Maybe your browser shows the icons in a different location. If you hover your mouse over the icons, do any of them have a tool-tip that says "Table"?

 

[northerntimber]

Eric,
I had to change some settings to get the icons to show but let me try a different method first... Does this help?

[TABLE="width: 1189"]
<colgroup><col><col><col><col><col><col span="2"><col span="7"><col span="2"></colgroup><tbody>[TR]
[TD]1[/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]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]5,001[/TD]
[TD]8,500[/TD]
[TD]3,005[/TD]
[TD]50,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]5,001[/TD]
[TD]13,501[/TD]
[TD]16,506[/TD]
[TD]66,506[/TD]
[TD]71,506[/TD]
[TD]77,506[/TD]
[TD]84,506[/TD]
[TD]92,506[/TD]
[TD]101,506[/TD]
[TD]111,506[/TD]
[TD]122,506[/TD]
[TD]134,506[/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]