Volume Tier Formula

northerntimber

New Member
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!
 
Last edited by a moderator:

Eric W

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

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Month</td><td style=";">Jan</td><td style=";">Feb</td><td style=";">Mar</td><td style=";">Apr</td><td style=";">May</td><td style=";">Jun</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Volume</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">2500</td><td style="text-align: right;;">3005</td><td style="text-align: right;;">4000</td><td style="text-align: right;;">5000</td><td style="text-align: right;;">6000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Cum Vol</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">3500</td><td style="text-align: right;;">6505</td><td style="text-align: right;;">10505</td><td style="text-align: right;;">15505</td><td style="text-align: right;;">21505</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Tier</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">1</td><td style="text-align: right;;">5000</td><td style="text-align: right;;"></td><td style="text-align: right;;">1000</td><td style="text-align: right;;">2500</td><td style="text-align: right;;">1500</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">5001</td><td style="text-align: right;;">13000</td><td style="text-align: right;;"></td><td style="text-align: right;;">1505</td><td style="text-align: right;;">4000</td><td style="text-align: right;;">2495</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">13001</td><td style="text-align: right;;">25000</td><td style="text-align: right;;"></td><td style="text-align: right;;">2505</td><td style="text-align: right;;">6000</td><td style=";"></td><td style=";"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B3</th><td style="text-align:left">=SUM(<font color="Blue">$B2:B2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D8</th><td style="text-align:left">=IFERROR(<font color="Blue">1/(<font color="Red">1/MIN(<font color="Green">$B8-SUM(<font color="Purple">$C8:C8</font>)-$B7,INDEX(<font color="Purple">$3:$3,AGGREGATE(<font color="Teal">15,6,COLUMN(<font color="#FF00FF">$B$3:$M$3</font>)/(<font color="#FF00FF">$B$3:$M$3-SUM(<font color="Navy">$D$7:$O7</font>)-SUM(<font color="Navy">$C8:C8</font>)>0</font>),1</font>)</font>)-SUM(<font color="Purple">$C8:C8</font>)-$B7</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />

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

New Member
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

MrExcel MVP
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

New Member
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

MrExcel MVP
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

MrExcel MVP
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:


ABCDEFG
1MonthJanFebMarAprMayJun
2Volume100025003005400050006000
3Cum Vol100035006506105051550521505
4
5
6
7Tier
815000100025001500
9500113000150540002495
10130012500025056000

<tbody>
</tbody>

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

New Member
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

MrExcel MVP
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

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

1ABCDEFGHIJKLMNO
2JanFebMarAprMayJunJulAugSepOctNovDec
3Monthly Volume5,0018,5003,00550,0005,0006,0007,0008,0009,00010,00011,00012,000
4Cumulative Volume5,00113,50116,50666,50671,50677,50684,50692,506101,506111,506122,506134,506
5
6Tier
7050001,0002,5001,500
8500113000 1,5054,0002,495
91300125000 2,5056,0003,495
102500150000 3,5058,0009,0004,495
11500001000000 5,50511,00012,000

<colgroup><col><col><col><col><col><col span="2"><col span="7"><col span="2"></colgroup><tbody>
</tbody>
 
Last edited:

Some videos you may like

This Week's Hot Topics

Top