Calculating sales number brackets in a table

bmiller603

New Member
Joined
Oct 1, 2019
Messages
2
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>
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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))))
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top