Calculating sales number brackets in a table

bmiller603

New Member
Hello,

I'm working on trying to auto-fill a sales table I have by inserting either formulas into certain cells or creating a VBA macro. I'm comfortable working with both so either method to solve this problem would be great. I've recreated the table below since I didn't see a way to attach my workbook.

Each month I enter the number of sales in column B. The total sales are then calculated using the sum function in column C. Columns D through G are what I'm trying to automate. These columns represent the the number of sales out of the total volume in that bracket. For example the month of April has 4,728 sales before the total number of sales is over 100,000. The rest of the sales for that month (50,464) are now placed in the 101-200k category. This happens again in May, 49,536 sales take place in the 101-200k bracket before the rest of the sales for that month move into the 201-300k bracket. These brackets are later used to determine discount rates. The tricky part is that the bracket changes can happen in any month. Theoretically if there were 340,000 sales in January then there would be 100,000 in brackets D, E, & F and 40,000 in bracket G. This is what I'm having a hard time wrapping my head around without writing a gigantic nested if statement.

Any help is appreciated. Thanks for your time.


ABCDEFG
1MonthSales # per monthTotal Sales So Far0-100k101-200k201-300k301-400k
2Jan24,78324,78324,783
3Feb26,84351,62626,843
4Mar43,64695,27243,646
5April55,192150,4644,72850,464
6May79,066229,53049,53629,530
7June60,336289,86660,336
8July64,274354,14010,13454,140

<tbody>
</tbody>
 

DanteAmor

Well-known Member
How about

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:134.02px;" /><col style="width:129.27px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><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 style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Month</td><td >Sales # per month</td><td >Total Sales So Far</td><td >0-100k</td><td >101-200k</td><td >201-300k</td><td >301-400k</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Jan</td><td style="text-align:right; ">24,783</td><td style="text-align:right; ">24,783</td><td style="text-align:right; ">24,783</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Feb</td><td style="text-align:right; ">26,843</td><td style="text-align:right; ">51,626</td><td style="text-align:right; ">26,843</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Mar</td><td style="text-align:right; ">43,646</td><td style="text-align:right; ">95,272</td><td style="text-align:right; ">43,646</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >April</td><td style="text-align:right; ">55,192</td><td style="text-align:right; ">150,464</td><td style="text-align:right; ">4,728</td><td style="text-align:right; ">50,464</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >May</td><td style="text-align:right; ">79,066</td><td style="text-align:right; ">229,530</td><td style="text-align:right; ">0</td><td style="text-align:right; ">49,536</td><td style="text-align:right; ">29,530</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >June</td><td style="text-align:right; ">60,336</td><td style="text-align:right; ">289,866</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">60,336</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >July</td><td style="text-align:right; ">64,274</td><td style="text-align:right; ">354,140</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">10,134</td><td style="text-align:right; ">54,140</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >=IF(AND(SUM($B$2:$B2)>(COLUMNS($D$1:D$1)-1)*100000,SUM($B$2:$B2)<COLUMNS($D$1:D$1)*100000),$B2-IF(COLUMN()>4,C2,0),IF(SUM(D$1:D1)>COLUMNS($D$1:D$1)*10000,100000-SUM(D$1:D1),""))</td></tr></table></td></tr></table>

Drag to the right then down
 

StephenCrump

Well-known Member
Or D2: =IFERROR(1/(1/ROUND(MEDIAN(0,100000,$C2-100000*(COLUMNS($D2:D2)-1))-SUM(D$1:D1),0)),"")

which also appears a little more robust for larger sales numbers, e.g. try testing with same data but B2 = 96,000, or B5 = 100,000.
 

bmiller603

New Member
Thank you DanteAmor & StephenCrump! Both of these formulas work with smaller numbers, but DanteAmor's formula did give a VALUE error with some larger numbers. The IFERROR formula StephenCrump came up with is working great. Thank you both for helping with this problem!
 

DanteAmor

Well-known Member
Thank you DanteAmor & StephenCrump! Both of these formulas work with smaller numbers, but DanteAmor's formula did give a VALUE error with some larger numbers.
I could adjust the formula, but I think it's getting longer.


I vote for @StephenCrump's formula, his point of view is much better.


=IF(AND(SUM($B$2:$B2)>(COLUMNS($D$1:D$1)-1)*100000,SUM($B$2:$B2)<COLUMNS($D$1:D$1)*100000),$B2-IF(COLUMN()>4,C2,0),IF(SUM(D1:D1)=0,0,IF(SUM(D$1:D1)<100000,100000-SUM(D$1:D1),IF(SUM(D$1:D1)>COLUMNS($D$1:D$1)*100000,100000-SUM(D$1:D1),0))))
 

Some videos you may like

This Week's Hot Topics

Top