Calculating sales number brackets in a table

[bmiller603]

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.


[TABLE="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]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]
[TD]2[/TD]
[TD]Jan[/TD]
[TD]24,783[/TD]
[TD]24,783[/TD]
[TD]24,783[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Feb[/TD]
[TD]26,843[/TD]
[TD]51,626[/TD]
[TD]26,843[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Mar[/TD]
[TD]43,646[/TD]
[TD]95,272[/TD]
[TD]43,646[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]April[/TD]
[TD]55,192[/TD]
[TD]150,464[/TD]
[TD]4,728[/TD]
[TD]50,464[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]May[/TD]
[TD]79,066[/TD]
[TD]229,530[/TD]
[TD][/TD]
[TD]49,536[/TD]
[TD]29,530[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]June[/TD]
[TD]60,336[/TD]
[TD]289,866[/TD]
[TD][/TD]
[TD][/TD]
[TD]60,336[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]July[/TD]
[TD]64,274[/TD]
[TD]354,140[/TD]
[TD][/TD]
[TD][/TD]
[TD]10,134[/TD]
[TD]54,140[/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

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]

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]

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]

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